Get Last Number in a Field

elTorito

Hi,

i have trouble with a table :twisted:

I Have an Integer Field in that i would save Line Numbers, for make it a Primary Key. I Would not work with a no serie.

I Would that OnInsert it checks what Integer ist the biggest an add them him 1 so that my new Row have LineNo "BiggestInt+1".

If im stay at last one and insert one new row is not problem, but if i goes in the middle of the table and would insert one, it checks the LineNo bevore(xRec), and add 1 to this, i have a Int that already exists.

I Hope you understand what i mean.

thanks

miguel.tiago

If the integer field is part of the key of the table why don't you use the property AutosplitKey := true in the subform or form that you are using.

It's very simple and there's no need for code.

Try to look at de sales lines form (F47), or try to do this:

a. create a new table with two fields:
5. Nº, Integer
10.Name, Text 30
the primary key is field 5

b. create a form over this table and put the property AutosplitKey := yes

If you insert rows in this form you'll see that navision manages de nº automatically, even in the midle of already created lines. Navision makes one simple thing, if you try to create a line between lines 10.000 and 20.000 navision sums 10.000+20.000=30.000 and divides 30.000/2, so the next line is 15.000.

MRT

Timo_Lässer

AutoSplitKey will be the easiest way but if you alway want Highest Int + 1 then you have to code in the OnInsert-Trigger of the corresponding table:

MyRec2.RESET;
IF MyRec2.FIND('+') THEN
  Rec."Entry No." := MyRec2."Entry No." + 1
ELSE
  Rec."Entry No." := 1;

elTorito

Yes the Auto SPlit Key is a nice Function, but with it i have trouble in some Tables for example the Comment Line.

For our Customers we put Comments in database, because we will not scroll to last, we put every time with F3 a new line on top, if there are too lines it cames the Error :

The AUtosplitkey facility cannot find a key between the current and the previous record.

Thanks for YOu tipps, now i have solucioned my problem with insert new rows in a new table. It have utilized now the auto splitkey function, it will works nice, if not are putting some rows with F3 in the middle of table

Thanks.

Francis_Malengier

take a look at the properties of the integer field in your table, there is a property 'Autoincrement'.

Christian_Buehl

The autosplitkey cannot find when there is no free number between the numbers you want to insert.
Fo example if you insert a line between line number 10 and 20 you will get number 15 for the new line.
This is the reason why usual tables have an autoincrement of 10000.

If you insert above the first line, you will encounter problems, as there is no free number before 0.

Joe_Little

Autosplit is the way to go when you can use it. Eventually it can run out of line numbers with autoincrement set to 10,000 but the user can usually select all, cut/paste which resets all of the numbers as an easy workaround to this rare problem.

If you want to manually implement autosplit functionality, check out code unit 51 BOM-Explode BOM. This has the code necessary replicate the autospit functionality.

PaLPiTaTioN

If the Autosplit is the property one should use, then why does the function apply to forms, and not tables? In my opinion, functions that maintain data integrity should be located where the data is located, and not elsewhere. But maybe I don't fully understand the functionality of the Autosplit property.

Joe_Little

Autosplit really has to do with the behavior of a form, not a table. Its purpose is a formatting tool to simplify the insertion of records into a journal type form.

Your comment about data integrity suggests that you may not understand this property fully. The goal and singular function of this property is to automatically suggest a unique integer which is the free-variable on an otherwise specified primary key.

This is done at time of insert to make it easy to add records where the user wants them in a journal type form. There is really no data integrity issue here at all as any unique value would be sufficient for this field and any repeated value would fail the insert.