Wednesday, November 21, 2012

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.

***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.***

12 comments:

  1. Invalid object name 'MicrosoftDynamicsAXBaseline.dbo.ModelElement'.
    ??

    ReplyDelete
    Replies
    1. As I wrote in the SOLUTION section above, you must edit the function to point to your Baseline database

      Delete
  2. Oh yeah.. Sorry bout that.

    It runs now but there is no data in either ModelElement or ModelElementData in our baseline database and the function returns ''. Is there something that needs to be done to populate these?

    ReplyDelete
    Replies
    1. Yes, I've seen that before, even when you specify the Baseline database to be separate from the Main, it still puts it in the Main. Not sure why, that's gotta be a bug during installation.

      Delete
  3. I've managed to run this from the main database rather than the baseline which has data. I think the last query needs 'AND Module = @module' otherwise you get more than one record returned.

    Apart from that.... schweeeet!!

    ReplyDelete
    Replies
    1. You are correct, thanks! Yes, I guess my tests only had one label module so I missed it.

      Delete
  4. Would it be possible to modify to retrieve ALL Enums?
    Could you please do it, if it is possible? :)

    ReplyDelete
    Replies
    1. It definitely could be done, but I can tell you it would be horribly slow, not to be used in a real-time solution. I won't do this just because it will take a long time, but essentially you can run the query below and loop thru it in a cursor and call into the Enum2Str function above (passing in the @name and @value for each and insert it into a temp table. Some additional work will be needed inside the loop to get each key/value pair, I've documented it's structure above. But another easy but slower (runtime) way would be to just loop thru the 251 (0 - 250), if my memory serves me right, possible values for the enums and use that to call into the Enum2Str function, if nothing gets returned, assume that there is no enum with that value. Sorry, I'd do it myself, but this sounds like a one-time run case versus having this run every time for a report or something.

      SELECT Properties
      FROM MicrosoftDynamicsAXBaseline.dbo.ModelElement me
      JOIN MicrosoftDynamicsAXBaseline.dbo.ModelElementData med
      ON med.ElementHandle = me.ElementHandle
      WHERE me.ElementType = 40

      Heres a psuedo-code version of the easy method:

      declare table @temp
      result = SELECT .... (above query)
      While(result.next())
      {
          for (i=0; i<251; i++)
          {
              value = ENUM2STR(result.name, i)
              if (value != "")
              {
                  insert into @temp (name, i, value)
              }
          }
      }

      Delete
  5. Hi

    I would like to point out that the script does not work for system enum values. I have noticed that system EDTs and Base enums are not available in the modelelement table, so this list is missing important entries like NoYes. I could not come up with a workaround yet unfortunately.

    Cheers
    DaxRunBase

    ReplyDelete
  6. Great work.. I've used a simpler approach in the past, which is basically to define a table that stores labels for all enum values (example key = "LedgerJournalACType.0", label = "Customer". (key = enum name . enum value), which is updated via an Ax process/job. Elegant solution? Nope, but it works and is probably the most efficient once the list has been generated.

    ReplyDelete
  7. I was getting errors for some of our labels that were part of our module and not the SYS module. The first query returned multiple values. I changed it to:

    SET @bin = (SELECT TOP 1 Properties
    FROM AX2012TST1.dbo.ModelElement me
    JOIN AX2012TST1.dbo.ModelElementData med
    ON med.ElementHandle = me.ElementHandle
    WHERE me.Name = @name
    AND me.ElementType = 40
    ORDER BY Properties DESC);

    Adding the TOP 1 to guaranty 1 value returned. That wasn't enough however, because only one of the returned values led to the correct label. By adding the ORDER BY statement I grabbed the one that always led to a label.

    This code is great work btw! Thanks for sharing this.

    ReplyDelete
    Replies
    1. I'm thinking the reason for multiple return values is due to it possibly existing in multiple layers. A ORDER BY LayerId DESCwould probably be a better solution as you are always guaranteeing it picks the outermost layer's version of the enum.

      Delete