What are Choices in Dataverse?
Also, and formerly, known as Option Sets and Picklists, Choices are effectively a key-value pair object type in Dataverse that allow for a drop-down list of static options. The end user sees a label and the database stores its corresponding integer value. This is wonderful for application performance but poses a problem when reporting.
While integers are great for performance in the application, they are not so helpful for reporting. Depending on how the data is extracted from the application, one may encounter only having the value of the integer with no label. Users of reports will not want to see the integer value, but the label that they are familiar with in the application. Thus, it is regularly desired to show the label. But how does one retrieve the label in a repeatable way? To address this problem, a SQL function can be used.
The function below is an in-line table valued function. This allows for a repeatable, straightforward way to retrieve a label. Not only is it repeatable but using an in-line table valued function instead of a scalar function will yield performant queries over any size of data. Some other solutions using scalar functions in SQL do not scale as well over large data sets. The function assumes using a single language is used. If needing to use multiple languages, need to pass in the language into the function as well. Otherwise leave it hardcoded as 1033 (English)
CREATE FUNCTION [dbo].[GetChoiceLabel] ( @EntityName nvarchar(64), @ChoiceName nvarchar(64), @ChoiceValue int ) RETURNS TABLE AS RETURN ( SELECT StringMap.Value AS Label FROM dbo.StringMap AS StringMap WHERE StringMap.AttributeName=@ChoiceName AND StringMap.ObjectTypeCode=( SELECT TOP 1 Entity.ObjectTypeCode FROM dbo.Entity AS Entity WHERE Entity.LogicalName=@EntityName ) AND StringMap.AttributeValue = @ChoiceValue AND StringMap.langid=1033 )
The above function may need to be updated for your exact situation. Using the Dataverse connector in Azure Data Factory does not require the "SELECT TOP 1" to translate the entity name to an objecttypecode.
To implement the above solution, you use the APPLY operator (similar to a join).
APPLY dbo.GetChoiceLabel('Entity', 'Choice Name', Value Field) AS ChoiceLabel
Here is another example using a table called item
SELECT I.ItemType, ItemType.Label AS ItemTypeLabel FROM dbo.item as I APPLY dbo.GetChoiceLabel('item','itemType',Item.ItemType) AS ItemType
There are some alternatives to using an inline function. See a quick overview below.
A function that is used in-line to return a single (scalar) value
Inline. More intuitive
Performance degradation over large record sets especially if invoking functional multiple times
Multi-statement table valued function
Similar to an in-line table valued function but allows for multiple statements
Less performant than in-line table valued function
Manually type out the conversations
No function call.
Not scalable if choices change.