2015 m. sausio 16 d., penktadienis

Upgrading Number Sequences to AX 2012 That Does Not Mach The Format

Hello AX world,

This time I would like to share some of my upgrade experience. Specifically, how to deal with the issue when a number sequence does not mach the format.
This applies to upgrades from AX 2009 (and earlier) to AX 2012.

Scenario: A number sequence's format has been changed over time. Let's say you had a format XXX-###### (6#) and you had ran out of numbers reaching XXX-999999 number. After that you changed the format to XXX-######## (8#).

That is all fine until you need to upgrade it. During the upgrade the script called 'Transfer number sequence framework tables. ' will fail saying that the number N does not mach format X
Script class: ReleaseUpdateDB60_Basic; method: updateNumberSequenceFrameworkPostSync.

The problem occurs when it tries to upgrade records from DEL_NumberSequenceList to NumberSequenceList table. It tries to calculate NextRec from the number sequence number. To get the number it calls NumberSeq::numRemoveFormat() method which throws an error then the format does not match the number.
Interesting to note that two different numbers XXX-123456 and ZZZ-123456 would mach the format XXX-###### but the table NumberSequenceList does not allow duplicated entries for fields: NumberSequenceIdStatus and NextRec due to unique index NumIdx.
So we have two problems, one when the format does not mach and another when duplicated numbers exist.

I would not say that the workaround I am going to explain here it's the best solution to the problem but it had helped me a lot in several upgrades and it did not result in any problems (at least I was not informed of any).

My approach to the problem was making sure that NextRec does not duplicate.

Firstly, I needed to bypass the error of the method that tries to generate me a number, NumberSeq::numRemoveFormat(). I did it by creating my own method numRemoveFormatNoError(). Instead of throwing an error it returned 0, meaning that the number was not generated.

Secondly, I needed to change the logic of upgrading DEL_NumberSequenceList records to NumberSequenceList.

I managed to do it by splitting the process into tree steps:
  1. Create records for numbers that mach the format and collect duplicated number sequence numbers.
  2. Create records for numbers that are duplicated. NextRec is assigned to be lastNextRec + 1.
  3. Created records for numbers that do not match the format. NextRec is assigned to be lastNextRec + 1.
Using this technique I have solved the problem for both invalid format  and duplicated numbers. And the records were, I would call semi-consistent.

I hope this article helps you to save some time during the upgrade.

P.S. If you would like to receive the code just drop me a comment with you email. I have not found if I can upload an xpo file here.

Evaldas Landauskas