Accessing Enum Labels from Outside AX 2012

Oh, I've done it again.  I've cracked some binary sequence of metadata goodness.  All of you switching over to AX have probably noticed that Enum labels are not accessible as they were in 4.0 and 2009.  In another blog post of mine, I described a solution to accessing Enums in 4.0 and 2009.  I think that method may still work in 2012, but I don't like the idea of always updating your model (not to be confused with the new kind of Models in 2012...oh, the ambiguity) every time you make a change to an Enum.

As you know, AOD files no longer exist in 2012, which stored all the metadata for all AOT objects.  Instead, the metadata is now stored in a model database.  The beauty of it is that we can now simply run queries to view this metadata from outside AX.  However, you will run into a brick wall when trying to view Enum data.  You will only see records for the BaseEnum object, not the Enums beneath it.

Upon further research, I found a field called Properties in the ModelEntityData table.  It is a binary field in which I would later on find that the Enums are stored within this binary structure.

If you want to hear about the technical structure of this Properties field, please read on.  Otherwise, skip to the solution below.

So, I began from the beginning.  I created a new BaseEnum in the AOT and made small tweaks to it to see how the Properties field changes throughout.  Below, you will find a chart that shows both the binary data and the action I took that got it there.  The below chart is a condensed version (so it could fit on this blog) that I made up that disregards changes in ConfigurationKey and CountryRegionCode properties, as those took up a lot of space.  I also kept string sizes small, as this impacted the length of the data as well.  The real chart I used can be found here.

  1    2  Label    H CC FLAG   CK 3  4  5  EnumLabel(SAS)---| EnumName(SAS)--------| 6    ECK CC   ECC(SAS)-----| ChangeDetails
---------------------------------------------------------------------------------------------------------------------------------------------------
0x0600 01               100204       00 00 0000               0000                            0000                --new enum, no props
0x0600 01               100004       00 00 0000               0000                            0000                --useenumvalue=yes
0x0600 02 5A000000      100204       00 00 0000               0000                            0000                --useenumvalue=no label=Z
0x0600 02 5A000000      000204       05 00 0000               0000                            0000                --displaylength=5
0x0600 02 5A000000      000204       00 00 0000               0000                            0000                --displaylength=0
0x0600 02 5A000000      200204       00 00 0000               0000                            0000                --style=radio
0x0600 02 5A000000      300204       00 00 0000               0000                            0000                --displaylength=auto
0x0600 02 5A000000      300206    02 00 00 0000               0000                            0000                --analysisusage=attribute
0x0600 02 5A000000      200206    02 05 00 0000               0000                            0000                --displaylength5
0x0600 02 5A000000      200006    02 05 00 0000               0000                            0000                --useenumvalue=yes
0x0600 02 5A000000      20000E    02 05 01 0200 41000000      0200 61000000          00       0000 0100 0000      --new enum Name=a Label=A Value=0
0x0600 02 5A000000      20000E    02 05 01 0200 41000000      0200 61000000          07       0000 0100 0000      --enum value=7
0x0600 02 5A000000      20000E    02 05 02 0300 41000000 0000 0400 61000000 62000000 0708     0000 0200 0000 0000 --new enum Name=b Label= Value=8
0x0600 02 5A000000      20000C       05 02 0300 41000000 0000 0400 61000000 62000000 0708     0000 0200 0000 0000 --analysisusage=none
0x0600 02 5A000000      20020C       05 02 0300 41000000 0000 0400 61000000 62000000          0000 0200 0000 0000 --useenumvalues=no

String columns (char until 0x0000)

• H - Help
• CC - CountryRegionCode (on BaseEnum)
• CK - ConfigurationKey (on BaseEnum)
• ECK - ConfigurationKey (on Enum)
• ECC - CountryRegionCode (on Enum)

String Array Sections (SAS)

The first 2 bytes (LE short) indicates the number of chars in the section, each char is 2 bytes

Value columns

• 1 - Always 0x0600?
• 2 - Seems to always be the number of strings following this column minus one (ie. if 0x03, then there are 2 strings following)
• First string is always the BaseEnum Label, second is Help, third is CountryRegionCode
• 3 - 0x02 if AnalysisUsage flag is set in FLAG section, otherwise null
• 4 - DisplayLength value, if set to Auto, this value is 0x00
• 5 - Number of enums
• 6 - Value (on Enum), is null if UseEnumValue flag is set in FLAG section, then enum values start at 0 and increment per enum

FLAG Column

There are 6 hexadecimal digits, we only care about the first, third, fourth, and sixth digit of each (A, B, C, & D, respectively), as the rest are always 0s.  So, 0xA0BC0D shows the variables in place of the values.
Sorry: I didn't intentionally use valid hexadecimal values as variables.

Each hexadecimal digit can be expressed as a 4-digit binary number (each binary digit is represented as W, X, Y, and Z, [W is 8 spot, Z is 1 spot]):

• For A:
• W - Always 0
• X - Always 0
• Y - Style property (0-Combo box 1-Radio button)
• Z - DisplayLength property (0-<some value> 1-Auto) if 0, the value specified in the property is stored in column #4 above
• For B;
• W - ConfigurationKey property on BaseEnum (0-<blank> 1-<some value>) - if 1, the string value of the config key is stored in the CK column, else the CK column is null
• X - ConfigurationKey property on Enum (0-<no configs> 1-<at least one config>) if 1, there is at least one enum that has a config key specified, they show up in the ECK column, else the ECK column is null
• Y - Always 0
• Z - Always 0
• For C:
• W - Always 0
• X - Always 0
• Y - UseEnumValue property (0-Yes 1-No) if 0, the enum values will be stored in column #6, else column #6 is null
• Z - Always 0
• For D:
• W - if 0, no enums exist, else they do
• X - Always 1
• Y - AnalysisUsage property (0-None 1-Attribute) if 1, a 0x02 shows up in column #3, else null
• Z - Same as BX in FLAG section

SOLUTION

After reverse engineering the Properties field, I developed a UDF for SQL that you may use to grab the Enum label for displaying on report or whatever you may need it for.  It will require you to provide a string value of the name of the BaseEnum along with the value you are looking up.  NOTE: You will be required to edit the query near the beginning and end of the function.  You must specify which server and database your model data resides (2012 R2 splits this to a separate database, if you upgraded to R2 from a prior version, DO NOT use the model tables that exist in your regular AX database, those tables hold information from your prior version of AX).  Also, you must change the LanguageId in the query at the end if you do not use 'en_us'.

Usage:

DECLARE @status AS int;
SET @status = 1;
SELECT ENUM2STR('SalesStatus', @status); --returns 'Open order'

Downloads:

• Enum2Str.sql (Must read SOLUTION section above to implement)

Changelog:

• 2013-02-08 - Bug - 'Module' column criteria was added to label query.
• 2013-11-27 - Bug - Duplicate entries are eliminated by only pulling the outermost layer's version.
• 2014-12-10 - Bug - When UseEnumValue is set to Yes, return the Value instead of <NOT FOUND>.
• 2015-04-16 - Bug - Binary values larger than 8000 character are no longer truncated

***Also, if there is anything else in AX like this (or not like this) you would like me to look at to figure out, please let me know.  I know there is a lot out there that has yet to be tapped into.  I seem to have a natural talent for analyzing complex structures of data and would love to put it to good use.***