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 bytesValue 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.***
Invalid object name 'MicrosoftDynamicsAXBaseline.dbo.ModelElement'.
ReplyDelete??
As I wrote in the SOLUTION section above, you must edit the function to point to your Baseline database
DeleteOh yeah.. Sorry bout that.
ReplyDeleteIt 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?
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.
DeleteI'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.
ReplyDeleteApart from that.... schweeeet!!
You are correct, thanks! Yes, I guess my tests only had one label module so I missed it.
DeleteWould it be possible to modify to retrieve ALL Enums?
ReplyDeleteCould you please do it, if it is possible? :)
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.
DeleteSELECT 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)
}
}
}
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.
DeleteI used this, runs in a couple of minutes.
Delete--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;
Hi
ReplyDeleteI 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
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.
ReplyDeleteI 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:
ReplyDeleteSET @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.
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.
DeleteIf the property value for UseEnumvalue = Yes for your ENUM, it does not seem to return the value, but rather returns <NOT FOUND.
ReplyDeleteWhat 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.
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.
DeleteI'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.
DeleteHey man.
ReplyDeleteYour 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.
Good find, the lone ELSE should have been one line up. I've fixed it.
DeleteThis comment has been removed by the author.
ReplyDeleteHi Guys
ReplyDeleteThis 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.
Good find, I'll change the code
DeleteGreat function!!! thanks
ReplyDeleteAwesome Function! Thanks heaps for all your work.
ReplyDeleteA quick question... Would it be difficult to modify the function to return the Name instead of the Label?
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
DeleteThank 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.
ReplyDeleteTry changing this line: IF @flags & 0x000200 = 0x000200 --UseEnumValue property
Deleteto: IF @flags & 0x000200 <> 0x000200 --UseEnumValue property
Does that make it work? Also, does that make some of the other previously working enums not work?
Thank you for your function. It's amazing!!. :)
DeleteBut this bug is not resolved. Show enum number in SalesPurch, for example.
Thank you.
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.
ReplyDeleteI'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.
DeleteEnum2Str.sql (Must read SOLUTION section above to implement) - download link does not work anymore, are you able to fix it please?
ReplyDeleteMy bad, filenames are case sensitive on my web host. The link should work now.
DeleteChanged script a bit to get all Labels for specific Enum: http://ioi.solutions/retrieving-label-from-enum-value-in-dynamics-ax-sql-db/
ReplyDeleteMy 2 cents is that I found this very useful on many occasions. Thank you for this addition to the community.
ReplyDeleteif it is possible to get enum values from field name and table name also
ReplyDeleteWorks perfectly, exactly what I needed. Thanks so much!
ReplyDeleteHello everyone,
ReplyDeleteI 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
I would be very interested in a function that returns the int value of an enum name/value name
ReplyDeleteHi, 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