Freitag, August 19, 2005

Tip: How to renumber your RecId's

Every record in Axapta will have one unique identifier: the RecId.
The RecId will be unique per company and the system gets the next RecId number from the table SYSTEMSEQUENCES, field "NEXTVAL".
Sometimes Axapta also does not only take one number but will take more (I remember to have read that it can be up to 24 numbers)
The field is a signed integer in the database, so it can happen that you will swap from positive RecId's to negative RecId's. The range in which recid's are allocated is between 2.147.nnn.nnn and -2.147.nnn.nnn.
If you have "holes" in your RecId, it may then be interesting to renumber all RecId's, starting with the number 1 again. You can do this by running the script "Check Record Ids" in the menu Administration/Periodic/SQL Administration.
What will the script do?
--> It will create some new tables on the SQL database, collect all RecId numbers, assign new RecID number starting from one.
It will then check all Axapta tables if there are fields that are a reference to a RecId. If yes, the old RecId Reference will be replaced by the new one.
And here is also a very important trap: you must be sure that all your database fields that are a referenced RecID are extended from any type of RecId datatype.

The script itself will run quite fast. I was once using it on a 2.5 Axapta database with about 2GB of data and it was finished after about 1 hour.


muppel hat gesagt…

Invariably, when people start assigning a meaning to record numbers, things go horribly wrong down the road. An excellent example is Axapta itself (as the OP in "How to understand the +/- record Id generation mystery." in this newsgroup demonstrates, where Axapta is ordering by record id. I have seen people trying to count records, based on the highest record id, trying to insert record id, otherwise their reports wouldn't sort right etc.

markble hat gesagt…

Check Your Database first! If you are using Axapta version 3.0 & higher, the check fix recordid function has changed. The problem will arrise if you have a table with several million records or higher. The way the new function works, it does a mass update of the table. We attemted to use this feature on with an inventsettlement table that had 12 milion records. Long story short, we were down for 3 days & it took a day to repair the records back to their correct number.

If you have to do it in version 3.0, run in a test environment first.