How to find out a maximum value of a field?

Business Central/NAV Developers Forum
1

How to find out a maximum value of a field in table? I need it becourse I want insert new record in table and must know max value of primary key to increment it!

2

Assumed that “MyTable.Field1” is the primary key of the table “MyTable” - then you get the max. value of Field1 doing that: MyTable.RESET; if MyTable.FIND(’+’) then message('This is the max. value of Field1: ',MyTable.Field1);

3

Thanks! It’s right!!!

4

Even though the question was already answered I will continue this a bit further. In case you’d need the maximum value of a field that is not the primary key, you can use a FlowField in some other table, for example the table “General Ledger Setup”. Create a flowfield in this table (“General Ledger Setup”) which uses the method “max” for the selected field in the selected table (where you want the max value from). Just remember to use calcfields for that field before you read the value. I use this quite a lot and I have found this technique quite handy. The best part: you can also set filters for how to calc the max value. /Pauli

5

One word of warning using Max or Min types of flowfields…in my experience they are much slower than other types of flowfields. In small databases (less than 3GB) this probably wouldn’t be a problem. I have a large database (28GB) and the Min & Max are nearly unusable. Keep in mind that I do have an excellent key for this situation but, for whatever reason, it is slow. Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117

6

quote:

Originally posted by paurola: Even though the question was already answered I will continue this a bit further. In case you’d need the maximum value of a field that is not the primary key, you can use a FlowField in some other table, for example the table “General Ledger Setup”. Create a flowfield in this table (“General Ledger Setup”) which uses the method “max” for the selected field in the selected table (where you want the max value from). Just remember to use calcfields for that field before you read the value. I use this quite a lot and I have found this technique quite handy. The best part: you can also set filters for how to calc the max value. /Pauli

The other way is to use a secondary key find last example with field3 being the counter MyTable.RESET; MyTable.SETCURRENTKEY(Field1,Field2,Field3); // If Required MyTable.SETRANGE(Field1,Field1); MyTable.SETRANGE(Field2,Field2); IF MyTable.FIND(‘+’) then message('This is the max. value of Field3: ',MyTable.Field3); David Cox email: david@mindsource.co.uk Edited by - David Cox on 2002 Jan 11 18:03:14