Get enum label values outside ax from sql.

(0) Share

Report

Posted on by Community Member

Hello,

I want to get enum value from sql server (that works also for system enum and user enum, of course), by sending its integer value.

How can I do that?

Thanks :)

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Community Member on at

Like (0)

Report

Create a batch process which dumps all your enums with their values to a custom table will do the job.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I found a blog on: abraaxapta.blogspot.co.uk/.../accessing-enum-labels-from-outside-ax.html

I am looking for that solution, but better (without any bugs as following - or what may be wrong, that I can fix that).

I have tried the code it that blog: dax.codemann8.com/.../Enum2Str.sql

but, that code doesn't work for all enums, and seems to work only for enum that created in user level.

i.e enum : SalesStatus doesn't work.

Thanks :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I would not directly read from modelelement table, if Microsoft changes the way they store properties for enums you are back to start.

I also thought that Microsoft used to dump enums with their translations to a table when they use the information in cubes, but this could be ax 2009 logic.

You could also use the meta data service, calling it from a .NET assembly in your database

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

May found some better solution - call an AX function from sql-server (that's I have done already). Because all the enum may repeated, I can read always from cache, without much time consuming (about 50,000 records per minutes overhead), but don't know whether that better. I didn't check solutions in sql-function much.

If there is sql-function that may cause better performance, so it is of course a preferred solution (better than 50,000 records per minutes overhead).

Thanks :)

Was this reply helpful? Yes No
Guy Terry 28,924 Moderator on at

Like (1)

Report

If you have a table called SRSANALYSISENUMS, you may find it has the enums you want in it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

This is an empty table (i.e for salesStatus enum)

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

lebels are saved in table ModelElementLabel

Select * from MicrosoftDynamicsAX_model.dbo.ModelElementLabel

Select * from MicrosoftDynamicsAX_model.dbo.ModelElement

where ElementType = 40

--ElementType 40 mean BaseEnums

Was this reply helpful? Yes No
Jakob Bjerg-Heise on at

Like (0)

Report

We have researched all ISV tools in the market, and can recommend you one. Let us connect on LinkedIN. Not appropriate in this forum.

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

If this is not to be considered spam (which it seems to be, taking into account that all your posts are just about promoting your product and that Eitan asked 1.5 year ago), please explain how your product can be used to convert enum value from integer in SQL Server.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm the owner of the AbraAxapta blog listed above and I know this topic well. Unfortunately I developed this solution years ago and many changes have happened to AX since then so further analysis and research may be needed to tweak the query in SQL.

Querying SrsAnalysisEnums can work too, but as someone pointed out, not everything is there, which is why my goal in my blog was to find the complete source of enums. However, it's possible to force the enums you want in there. SrsAnalysisEnums will only have enums that exist on tables that are included in a Perspective object, and only after that Perspective's model has updated. Only after then will you be able to find the Enums in that table.

Was this reply helpful? Yes No