Abra Axapta: enum

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

Forenote: If you are using 2012, read this post. There is a more robust way to access enums in AX 2012.

If your business operates like any would and has the need to report on data from custom functionality then you might find this post useful. In creating custom functionality we often have the need to create new custom Enums and store the values in a custom table. Doing so works just fine inside the Dynamics circle. However, if you use a separate reporting engine outside of Dynamics, like SSRS, to render your reports, you will find that your users will not understand what a ShipStatus of 2 is, and let's be serious, your proposed solution to hard code your enum labels in your report is a poor strategy.

Microsoft was nice enough to create some tables to store enum values and labels for some of these enums. These are located in two tables, namely, SRSAnalysisEnums and SRSModelEntityCache. But, like I mentioned earlier, there are only SOME enums in these tables. For a long time it was mysterious to me why it was this way, but after some digging in and a few "Ah ha!" moments later I figured it out. It also introduced me to another unknown area of Dynamics.

Today, we're going to learn about Perspectives. It's an object in the AOT under Data Dictionary. Perspecitves in Dynamics are a poor attempt to mimic the functionality of a database cube. It's not as flexible as a real cube and not very customizable. If you expand the Perspectives node, you'll see a Perspective object for each Module. Each of these perspectives has a list of tables. Any enums in these tables listed under a perspective are the enums that show up in the SRS tables. To get your new enums to show up you just need to have your table included in one of these perspectives or create your own, but try to put your table in the most relevant module, pertaining to the data that's stored in the table.

Note: If your table is already in these perspectives, read ahead.

Now, putting the table in the perspective alone won't put your enums in the table. You also have to update the model so the SRS tables get populated correctly. The way to do this is Tools->Reporting tools->Update models. You can choose to update all languages or just one, as you feel is necessary for your business. Running this will take awhile, but when it is finished, you will now see your new enums show up in the SRS tables. Here's a script that will get your enum labels from a value:

SELECT ae.EnumItemName
FROM SRSModelEntityCache ec
JOIN SRSAnalysisEnums ae
ON ae.EnumId = ec.EntityId
WHERE ec.PhysicalName = 'BaseEnum_ShipStatus'
AND ae.EnumItemValue = 2

Abra Axapta

Wednesday, November 21, 2012

Accessing Enum Labels from Outside AX 2012