Using MAX to increase serial numbers

Classic Servoy
1

Hi,

I found some problems assigning the max value + 1 to an invoice counter.
My idea was to launch a query like
SELECT MAX(numric) FROM ricevute where yearnr = 2004
and then assign the retrieved value to a variable. After that, I create a new record and set the invoice number to the variable + 1.

But I can’t retrieve this value using getDataSetByQuery, because it returns me a null value.
After several attempts, I managed to make it work using a loop.
It works, but it doesn’t sound me right… :wink:
Anyway: here is my method. I would be happy to understand why it doesn’t work simply assigning to nummax the value of the only column present in the dataset

var oggi = new Date();
var annooggi = oggi.getFullYear();
var query_anno = ""
var query_anno = "SELECT MAX(numric) FROM ricevute where yearnr = " + annooggi
var dataSet = databaseManager.getDataSetByQuery(controller.getServerName(), query_anno, null, 100000);
for(var i=0 ; i<= dataSet.getMaxRowIndex() ; i++)
{
	dataSet.rowIndex= i
	
	//replace null with blank
	var nummax = dataSet[1]
}

globals.gform = application.getMethodTriggerElementName();  
controller.newRecord()
numric = nummax + 1
forms.Intesta_piani.controller.show();
2

The rowindex of your dataset Object always starts with 1.
Select MAX() always returns 1 value, so
you can set your rowlimit to 1 as well instead of 100000

Notes:
Select (max) can be very expensive. Has to check all rows for max value.
You might also end up creating double values.
What database are you using? Can’t you use the sequencing from the database?

3

maarten:
The rowindex of your dataset Object always starts with 1.
Select MAX() always returns 1 value, so
you can set your rowlimit to 1 as well instead of 100000

Right :oops:

maarten:
Notes:
Select (max) can be very expensive. Has to check all rows for max value.
You might also end up creating double values.
What database are you using? Can’t you use the sequencing from the database?

I’m using MySQL. I don’t know if I can use sequencing, because I need an increasing order without “holes”, even if a record is deleted. So, for instance, if the last invoice I created is number 45 and I decide to delete it, the next invoice I create must be number 45, not 46.
Then again: this series of numbers must restart from 1 every year.
That’s why I thought about MAX function, but if there’s a better way, I’ll be glad to use it… :)

4

Just make sure that there is an index on that column. the Max function will use the index and then it won’t be slow.