Menu

Retrieving Label from Enum Value in Dynamics AX 2012 SQL DB

If you had a chance to read my other posts, we are working towards enabling reports development in AX using SQL, no X++ thing. On of the issue we faced was getting Base Enum Labels. Dynamics AX transactional DB only keeps Enum values (0,1,2,3…). All labels and names are stored in the Model DB. However, you cannot access it through TSQL query as data is stored in the binary format.

I have found a post, where one smart guy actually did a good reverse engineering to parse that binary field. His script provides a function with name of the Enum + you need to specify a value of Enum for which you would like to get a Label.

I have changed it a bit to get all available Labels for specific Enum. Now, you just need to loop through all available Enums to get your table populated for reporting.

It takes around 2-3 minutes to get into table all Base Enum Labels.

Orinial Script can be found here + explanation on how to parse binary field: Link

You are returning both an [EnumItemValue] and an [EnumValueIndex] column. In my (very limited) testing, they always equal for each row. I want to only have one numeric value to use as an index to get the Label. Which would you recommend that I use?

IF EXISTS(SELECT top 1 * from @MyTempTable where [EnumItemName]=@EnumName and [EnumValueIndex]=@IndexToFind)
BEGIN
UPDATE @MyTempTable SET [EnumItemLabel]=@ret
where [EnumItemName]=@EnumName and [EnumValueIndex]=@IndexToFind
END
ELSE
BEGIN
INSERT INTO @MyTempTable VALUES (@EnumName,@ret,@IndexToFind,@IndexToFind);
END

AccountingEventType is a more complex case that illustrates it better. The procedure returns (in this order) Correction, 13, Period close, 14, Period open, 15, None, 0, Finalize, 16, Cancel, 17, Original, 0. While None should be 0, Original should be 12. If I run the function upon which you based your procedure and pass in the enum name and the value of 12, it returns Original.

For both of these examples, the code works fine through line 87, looping through and inserting the correct data for each Enum it finds. However, it needs to get the last one from the next block of code. Line 93 sets @IndexToFind=0. That value is never changed the rest of the code. Although it correctly finds the @ret (EnumItemLabel) for that last Enum, it inserts a 0 for the Value no matter what the correct value should be.

I don’t know how to calculate the correct value for @IndexToFind in that block of code. Can you help with this?