update servoy sequence programatically

Classic Servoy
1

Hi there,

I have a table called invoice which has a column called invoice_no which uses a servoy sequence. The sequence is running perfect as long as the records are created from within servoy.

But I also have a php script which creates a bulk of records (every few months) which sets the invoice_no to max(invoice_no) + 1 for every new record.

After the script is run the servoy sequence is out of sync. So I have to manually sync the servoy sequence from the Servoy Admin page.

Is there a way to do this programatically?

I cannot use db identity for the invoice_no column since another column uses that.

Thanks.

2

This is a perfect sample of where it is desired to NOT let Servoy take care of the sequence.
Can’t you create a ‘servoy_id’ row if you can not use the one that is already there.

You can run a simple query with a function like ‘max(servoy_id)’ (by heart, syntax could be different) to retrieve the highest value.

3

Thanks for your reply.

I was thinking about this but was curious that there is another better way.

So as you suggested I would use the SQL “SELECT MAX(invoice_no) + 1 FROM invoice” and set that in the new record.

My worry though is what if two users clicking the new button at the exact same time.

I will get a duplicate invoice_no right? Is there a way to handle this issue?

4

You can set a lock to the record and check for the lock.

5

Hi Faheem,

You might want to take a look at this old thread from 2005:
viewtopic.php?f=3&t=3705

Hope this helps.