In which MS SQL Table are PowerApps Choice Values Stored-CodePudding

In Dataverse if I create a table e.g 'foo' MS Dynamics automatically pre-fixes my tables with a few characters to denote that they are my custom tables e.g. abcd_foo.

What's more, if connect to the database with SSMS I can query the table as follows:

Select * from abcd_foo;

When you create a Choice column in foo, the table contains an integer value that references the lookup value - not the lookup value itself.

The picture below shows the choice values returned from a query:

Select 
       abcd_accesslevel -- my choice column
from   abcd_foo;

What is the name of the SQL Table in which MS Dynamics stores these 'choice values' which it then internally joins up to my tables foo choice column, acceslevel?

e.g. if 'abcd_accesslevel' is a Choice column, how would I complete this query:

Select     f.abcd_accesslevel,
           c.label, -- The choice label
           c.value, -- The choice value
           f.*
from       abcd_foo f
left join  some_internal_choice_table c on c.someid = f.abcd_accesslevel
-- There may be a more complex WHERE clause to separate out the label for my table from the other choice values if all choices are stored in a common table.

CodePudding user response：

In Dataverse there are 2 optionsets, local and global.

In your case I believe your abcd_accesslevel is local optionset.

local optionset is stored in metadata Entitydefinitions. I never retrieved this via sql but I user API to fetch such data.

example API call

https://XYZ.crm.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName=’incident’)/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?&$expand=OptionSet

I tried this sql query and worked for me. (I tried on my account entity)

SELECT *
FROM   stringmap
WHERE  objecttypecode = 'abcd_foo'
       AND attributename = 'abcd_accesslevel ';