Get Last Number in a Field

elToritoelTorito Member Posts: 191

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.



  • Options
    miguel.tiagomiguel.tiago Member Posts: 6
    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.

  • Options
    Timo_LässerTimo_Lässer Member Posts: 481
    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:
    IF MyRec2.FIND('+') THEN
      Rec."Entry No." := MyRec2."Entry No." + 1
      Rec."Entry No." := 1;
    Timo Lässer
    Microsoft Dynamics NAV Developer since 1997
    MSDynamics.de - German Microsoft Dynamics Community - member of [clip]
  • Options
    elToritoelTorito Member Posts: 191
    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 :)

  • Options
    Francis_MalengierFrancis_Malengier Member Posts: 28
    take a look at the properties of the integer field in your table, there is a property 'Autoincrement'.
  • Options
    Christian_BuehlChristian_Buehl Member Posts: 145
    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.
  • Options
    Joe_LittleJoe_Little Member Posts: 45
    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.
  • Options
    PaLPiTaTioNPaLPiTaTioN Member Posts: 55
    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.
  • Options
    Joe_LittleJoe_Little Member Posts: 45
    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.
Sign In or Register to comment.