Sequences

Classic Servoy
1

Hi,

I have a table that has a database sequence to generate an ordernumber.
(Auto-enter type = db_sequence)

When i duplicate the order a new sequence number is generated automaticaly for the ordernumber, works fine.

But : the purchase order also has a version number.

When i create a new version i want the purchase order number to stay the same and i increment the versionnumber myself.

I see two ways to do this :

  1. not use sequence in auto-enter
    But how do i put next sequence into new record.ordernumber

  2. set ordernumber to original number after duplicate
    But then i loose sequencenumbers and i want them to be a
    “closed range”

Any ideas / comments ?

2

Hi Hans,

Hans Nieuwenhuis:

  1. not use sequence in auto-enter
    But how do i put next sequence into new record.ordernumber

With your own code.
You can take a look at this old thread.
Since you are using Oracle you can, just as with PostgreSQL, call the next value of a sequence. So the described procedure in that thread can be much simpler for you.

Hope this helps.

3

Solved this by using table event on-Insert :

var _record = arguments[0];

//only get sequence number when inkpordkrt_nr is null
if (_record.inkpordkrt_nr == null)
{ 
  var maxReturnedRows = 1;
  var query = 'select SEQ_INKPORDKRT_NR.nextval from dual';
  var args = new Array();
  var dataset = databaseManager.getDataSetByQuery(forms.bvko_navInkorderkaartenPrim_dtl.controller.getServerName(), query, args, maxReturnedRows);
  _record.inkpordkrt_nr = dataset.getValue(1,1)

  if (_record.inkpordkrt_nr != null)
    { return true; }
  else
    {
    var thePressedButton = plugins.dialogs.showErrorDialog('Error', 'Inkooporderkaart nr niet gevuld, inkooporder wordt niet aangemaakt','OK');
    databaseManager.rollbackEditedRecords()
    databaseManager.rollbackTransaction()
    return false; 
    }
}

Regards,

Hans