Get Lookup Display Name and Choice (Option Set) Value Label in a Single Query Using a Microsoft Dataverse Connector FormattedValue Property

One of the limitations of using Microsoft Dataverse connector in future compared to the classic workflow WAS the inability to get the display name of the lookup value or the label of the Choice (Option Set) value in one simple query. However, this is actually not the limitation of the platform itself. It seems that the Microsoft Dataverse actions did not use the odata.include-annotations preference with the value of OData.Community.Display.V1.FormattedValue which is required by the Web API to include formatted values in the response. If we query data using the Web API with OData.Community.Display.V1.FormattedValue, the system returns the record with properties that support formatted values. Even the Microsoft Dataverse (legacy) connector can return at least the label of the Choice (Option Set) value with "_*choicecolumn*_label" property.

Getting the formatted value is a functionality that we need quite often especially when composing an email with the record details in the flow. Most of the time, we had to do workarounds like adding additional Get a record step for each lookup or using Expand Query to retrieve the lookup display name. Sometimes, we had to rely on deprecated Microsoft Dataverse (legacy) connector to get "_choicecolumn_label" property or use the string map entity to retrieve the label text against the entity name and choice value or even Invoke an HTTP request. But today, that is not the case any more because Microsoft Dataverse connecter can now return properties that support formatted values just like Web API.

Here is the JSON output of the List records action in one of my flows. The left side is one of the previous runs from history. Once I edited the flow and saved it, the flow updates the Microsoft Dataverse steps itself and the right side is the result when I re-submitted the same flow run. The additional properties returned from the updated flow are

• OData.Community.Display.V1.FormattedValue for supported data types
• Whole Number, Duration, Timezone, Language
• Decimal Number, Floating Point Number, Currency
• Date and Time, Date Only
• Yes/No (Two Options), Choice (Option Set), Choices (Multi Select Option Set)
• Lookup, Polymorphic Lookups
• Microsoft.Dynamics.CRM.associatednavigationproperty for lookup columns
• Microsoft.Dynamics.CRM.lookuplogicalname (which was not returned in output JSON previously for certain exceptional cases like partyid of the Activity Party table)
• Microsoft.Dynamics.CRM.totalrecordcount and Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded in the List records output body
• These two looks like the similar properties of EntityCollection object from the SDK and seems to be added for future enhancement. But for now, the value is always -1 for totalrecordcount and false for totalrecordcountlimitexceeded

💡 Tip

The new OData.Community.Display.V1.FormattedValue is only available in the Get a record and List records actions of Microsoft Dataverse connector and not in the output of the trigger. In the trigger output, the Choice value label is available as _choicecolumn_label parameter. But there is no lookup display name available in the output of the trigger and you will have to call another Get a record action to get the necessary values.

Unfortunately, those new properties are not yet available in the Dynamic Content pane and you will have to enter the expression manually in the following syntax.

columnname@OData.Community.Display.V1.FormattedValue

e.g. outputs('Get_an_Account_Record')?['body/statuscode@OData.Community.Display.V1.FormattedValue']

items('Apply_to_each')?['_ownerid_value@OData.Community.Display.V1.FormattedValue']

Use the Expression Builder to populate the expression above as the expression block (instead of entering the expression directly in the textbox).

If you are using other action than "Apply to each", here is how you can find out the expression for the 

Dynamic Value to build the expression.

E.g. Let's say you are using the Select action from the Data Operation. Select the "Peek code" under ellipses (...) menu.

You will see the expression of the existing Dynamic Value in the code view.

Select and copy that expression without @ symbol and curly brackets { } (if there any) and append "@OData.Community.Display.V1.FormattedValue" after the logical name of the column. The final expression will look something like this.

item()?['_ownerid_value@OData.Community.Display.V1.FormattedValue']

Another way to get the expression is to 

• place the cursor beside the dynamic value pill
• Ctrl + A to select all
• Ctrl + C to copy the whole textbox
• paste into Notepad of any kind of text editor
• remove the @ symbol in the beginning and curly braces { }
• add @OData.Community.Display.V1.FormattedValue after the column logical name

Enter the expression in the Expression Builder to get the formatted value of the column.

Here is the flow with Get a record action to retrieve some columns from Account table which supports formatted values and uses the Compose action to see the formatted output values.

When the flow above ran, it generated the formatted value output as in the screenshot below.

Formatted Value for Choices Column 🔗

For Choices (Multi Select Option Set) data type, the formatted value returns the text value of the selected options separated by the semicolon and <space> even though the normal value is a comma-separated integer value.

E.g. Normal Value = "1,1000"

Formatted Value = "Business; Stakeholder"

Formatted Value Using Parse JSON 🔗

If you are not comfortable with writing an expression to get the @OData.Community.Display.V1.FormattedValue of the column and prefer to select from the list of Dynamic Value list, you can use the Parse JSON action. I will briefly show how it can be done but if you want to know more details about using Parse JSON action, you can check out this blog post by Priyesh Wagh.

🛈 Note

Using Parse JSON action instead of writing the formatted output parameter in an expression will be counted as an additional API request for each succeeded/failed step.

1. Run the Flow once and collect the Outputs from the "Get a record" or "List records" step.

2. Create a "Parse JSON" action, populate "Content" parameter with the body from the output of the previous step, click on "Generate from sample" and paste the output from the step 1.

3. You can now select the formatted values from the list of Dynamic Value. Unlike the output of the Microsoft Dataverse connector, it does not show the display name of the columns. Instead, you can see the same format of logicalname@OData.Community.Display.V1.FormattedValue.

🛈 Note

If your existing flow is not returning OData.Community.Display.V1.FormattedValue property, edit the flow and save it to update the Microsoft Dataverse steps.

For those who prefer to follow the video tutorial, you can check out Neil Parkhurst's YouTube video.

If the requirement is to get the Choice labels from the Dataverse Trigger and not from the Get a Row or List Rows actions, the format is as follows.

triggerOutputs()?['body/column_label']

You can refer to this blog post from Priyesh for more details.

Summary

With the latest update of Microsoft Dataverse connector, we can now query the formatted values of supported data types such as lookup display name, choice (option set) value label, formatted date/time by using the OData.Community.Display.V1.FormattedValue property of the column in the expression.