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

39 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
    2. One use case for having a table-valued function version that returns all of the Value and Label pairs for an Enum would be for use in a CROSS APPLY rather than looking up the Label for a Value separately for every row in the output of a select statement.

      Delete
    3. I used this, runs in a couple of minutes.


      --DROP TABLE #TEMP;

      CREATE TABLE #TEMP (
      ENUMNAME VARCHAR(MAX),
      ENUMKEY INT,
      ENUMVALUE VARCHAR(MAX)
      );

      DECLARE @CNT INT = 0;
      WHILE @CNT <= 251
      BEGIN

      BEGIN TRY
      INSERT INTO #TEMP
      SELECT ME.NAME, @CNT, DBO.ENUM2STR(ME.NAME, @CNT)
      FROM MICROSOFTDYNAMICSAX_MODEL.DBO.MODELELEMENT ME
      JOIN MICROSOFTDYNAMICSAX_MODEL.DBO.MODELELEMENTDATA MED
      ON MED.ELEMENTHANDLE = ME.ELEMENTHANDLE
      WHERE ME.ELEMENTTYPE = 40;
      END TRY
      BEGIN CATCH
      END CATCH;

      SET @CNT = @CNT + 1;
      END;

      DELETE FROM #TEMP WHERE ENUMVALUE IS NULL;
      SELECT * FROM #TEMP;

      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
  8. If the property value for UseEnumvalue = Yes for your ENUM, it does not seem to return the value, but rather returns <NOT FOUND.
    What are the implications of changing the property to NO? or can this function be modified to include those ENUMs that have this property set to YES.
    It is a great function.

    ReplyDelete
    Replies
    1. Ah, I see. It appears that if UseEnumValue is set to yes, it will use the numeric value as the label, as opposed to using a string value or label. This is a bug I'll need to address but I can't promise I'll get to this soon.

      Delete
    2. I've fixed the above problem of UseEnumValue = Yes, although I couldn't run any kind of test on it. If this causes issues, please let me know.

      Delete
  9. Hey man.
    Your scripts are awesome and has already helped me A LOT!
    Your latest update is not working correctly - I suppose:
    You have an "ELSE" in line 74, which seems to have no leading IF.
    You do have an IF in line 44, but for that you have an ELSE in line 46, so the ELSE in line 74 is all alone.

    All the best - and merry xmas.

    ReplyDelete
    Replies
    1. Good find, the lone ELSE should have been one line up. I've fixed it.

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Hi Guys

    This script is great and provides correct answers regardless the value is stored in Properties column or ModelElementLabel table.

    I found an exceptional case, Enum Name = 'LedgerPostingType'. The script (function) does not return correct answer for this case, always 'NOT FOUND'.

    The Enum 'LedgerPostingType' has over 200 values and stored values in Properties column in our AX system. So, the Properties column is very long.

    After some try and error, I found an easy solution.

    Replace
    DECLARE @bin AS varbinary(8000);
    with
    DECLARE @bin AS varbinary(MAX);

    After this change, I got no error.

    Just wanted to share with all.

    Happy New Year.

    ReplyDelete
  12. Awesome Function! Thanks heaps for all your work.
    A quick question... Would it be difficult to modify the function to return the Name instead of the Label?

    ReplyDelete
    Replies
    1. It probably wouldn't be too difficult, I'd put a couple of SELECT SUBSTRING(@bin, @pos, 100); statements throughout the code and keep an eye out for the ASCII binary string representing your enum name, strings show up in the binary as this format Abc = 410062006300

      Delete
  13. Thank you for a nice function, it works for some of the enums however it doesn't work for some of the enums. The function doesn't work for SalesStatus, PurchStatus and BOMType etc. For those its not working it just gives the enum number again instead of the label. could you please check and help me. Thank you.

    ReplyDelete
    Replies
    1. Try changing this line: IF @flags & 0x000200 = 0x000200 --UseEnumValue property
      to: IF @flags & 0x000200 <> 0x000200 --UseEnumValue property

      Does that make it work? Also, does that make some of the other previously working enums not work?

      Delete
    2. Thank you for your function. It's amazing!!. :)

      But this bug is not resolved. Show enum number in SalesPurch, for example.
      Thank you.

      Delete
  14. Thank you for trying to help me, I have made the change and the salesstatus worked but salestype which was working before has been stopped working and it is giving just the enum value this time. so its kind of reversed the functionality. Thank you.

    ReplyDelete
    Replies
    1. I've backed out the change to a prior version. It seems that some enums different in properties and I can't tell what it is and what indicates that it is different. Unfortunately, this will probably sit as unresolved for some time.

      Delete
  15. Enum2Str.sql (Must read SOLUTION section above to implement) - download link does not work anymore, are you able to fix it please?

    ReplyDelete
    Replies
    1. My bad, filenames are case sensitive on my web host. The link should work now.

      Delete
  16. Changed script a bit to get all Labels for specific Enum: http://ioi.solutions/retrieving-label-from-enum-value-in-dynamics-ax-sql-db/

    ReplyDelete
  17. My 2 cents is that I found this very useful on many occasions. Thank you for this addition to the community.

    ReplyDelete
  18. if it is possible to get enum values from field name and table name also

    ReplyDelete
  19. Works perfectly, exactly what I needed. Thanks so much!

    ReplyDelete
  20. Hello everyone,

    I have used the sql-script to extract the base-enum values in three languages as I need this voor reporting purpose. So many thanks.

    I'm also looking for this purpose to have read out all table/field combinations to have the label information in more languages for use in reporting instead of the field names from the database.

    How can I make the connection/link between the modelelement and modelelementlabel table for the tables/field information.

    hope someone reads my post, as the last reply is from more than one year ago.

    Regard Ramon

    ReplyDelete
  21. I would be very interested in a function that returns the int value of an enum name/value name

    ReplyDelete
  22. Hi, this script has been very helpful. I've used it to provide enum xref for reporting outside of AX. Can it be adapted to pull a property on any object within the AOT? For example, to pull the MaintainUserLicense property on a menuitem? Thanks for any help you can offer!

    ReplyDelete