Lets say that there is a table with a primary key of Entry No. which has the AutoIncrement property set to True. When inserting a record to this table through code you must have Rec.Entry No. := 0; which allows the autoincrement property to do its thing automatically when you insert. I am trying to do this same thing using a RecordRef. I set code similar to the following: FieldRef := RecordRef(“Entry No.”); FieldRef.value := 0 but when I insert it doesn’t increment this field. Anyone know how to fix this?
My recommendation would be not to use AutoIncrement. We had issues with AutoIncrement in the past (specially in SQL), and you will notice that most of the navision standard tables are not using this. If you are designing a table like the comment table, it is recommended to use AutoSplitKeys. If you are writing into a new ledger, it is recommended to do it this way: MyTable.LOCKTABLE; IF MyTable.FIND(’+’) THEN NextEntryNo := MyTable.“Entry No.” + 1 ELSE NextEntryNo := 1; MyTable.INIT; //Create my rc MyTable.INSERT; //or MyTable.INSERT(TRUE)
quote:
not … use AutoIncrement.
Hmm…, but what of Table 405 - Change Log Entry. This table uses the ‘ledger’ pattern (primary key = “Entry No.”, new recs always go at the end…), but change the pattern to use a BigInteger type, with the AutoIncrement property. Is this not ‘shades of things to come…’? Might not all ledgers someday be converted to this pattern? You would seem to get a big multi-user concurrency benefit – the db server gets to determine the Entry No. automatically, rather than several network round-trips to figure it out at the client… Only downside that I can see – the LOCKTABLE/FIND(’+’) pattern is a kind of semaphore – if you can grab the flag, then you are king of the hill, everyone else (who follows the same pattern) must wait 'till you’re done…
I think I agree with you on the long run. However, if we look at Navision today, I think all ledgers with $$$ in Navision are not using this approach… If for example we were willing to change the posting in the Item Ledger to use autoincrement + multi-user posting, we would have to:
- wait for the database to return our entry no. before we can create the detailed item ledger entries and the item value entries
- in the case of multiple users posting to the same ledger, manage the fact that two warehouse transfers combined could bring the inventory negative without popping an error (just an example)
- redefine the way versioning/rollback is handled since 5 people can be writing in the same core table and the first user to access can get an error or loose power on his machine
I’d also be curious to see what we would do in the case of rollback of transaction of 1 user in a multiple user environments in an area like the GL Entry table where all the Entry No. are required to be in sequence. I can guarantee our auditors would ask questions if he saw that entries 17, 24, 27 and 32 are missing… and what about the consistency check after each posting to the GL? Those are a few examples of why Today I would stick with the “Navision way of doing things”. even if your approach could be the way to go for Tomorrow.