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


One of the limitations of using Common Data Service (current environment) connecter in Power Automate (which might probably be renamed as Dataflex Pro connector 😜 Dataverse connector in future) comparing 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 CDS (current environment) 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 standard CDS 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 soon-to-be-deprecated old CDS 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 CDS (current environment) 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 CDS (current environment) 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 Common Data Service (current environment) 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']

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 CDS (cur env) 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 CDS (current environment) steps.


Summary

With the latest update of Common Data Service (current environment) 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.

Comments

  1. Thanks a lot.
    Explained this in a proper and crisp way. No extra documentation and also no short cuts.
    Gave an example which could be used directly to create our query.

    ReplyDelete
    Replies
    1. I'm glad you enjoyed the post and it's useful for you 😊

      Delete
  2. This is very clear, thank you for sharing. I found that assigning a variable string (or variable array if multiple values) allowed me to use it as dynamic content. :)

    ReplyDelete
    Replies
    1. That's interesting approach. How do you assign a variable string though? Just OData.Community.Display.V1.FormattedValue in the expression?

      Delete
    2. I tried this approach and received an "invalid expression" message.

      Delete
    3. Can you please show me your expression that you're getting such error message?

      Delete
  3. Is 'fieldname@OData.Community.Display.V1.FormattedValue' supposed to automatically be listed under Dynamic content? I still only see the 'value' object which outputs the value of the option set, not the actual label. Using the CDS Current Environment connector 'Get Record' and have tried saving and reopening multiple times.

    ReplyDelete
    Replies
    1. Unfortunately, those new properties are not yet available in the Dynamic Content pane and you will have to enter in the Expression builder manually with the following syntax.

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

      Delete
    2. Where in 'Get a record' or 'List records' is your expression used? I'm not sure where in the action step the expression is to be entered so the formatted value is displayed instead of the code.

      Delete
    3. The expression needs to be used in the steps after 'Get a record' or 'List records' actions where you are going to reference the output of those action steps.

      E.g. If the expression for Owner field of 'Get a record' action is something like this,
      outputs('Get_a_Record')?['body/_ownerid_value']

      write your expression as below to get the formatted value.
      outputs('Get_a_Record')?['body/_ownerid_value@OData.Community.Display.V1.FormattedValue']

      Check out this screenshot and it would be clearer for you
      https://1.bp.blogspot.com/-s2iggLw5PjQ/XxbbN0bvHgI/AAAAAAABrnE/Y895U74sn8gBWjwGwPp7uR4LNVQ0q4irwCLcBGAsYHQ/d/Get%2BRecord%2BFlow.png

      Let me know if you still cannot work it out and want me to upload the sample flow.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Thank you for the reply. I see the call for the formatted values in the Compose Account Information action step but I am still unclear on where this code gets applied:

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

    Thank you again. I'm keenly interested in this because statuscode on the Order entity is the exact field I'm trying to retrieve formatted values for.

    ReplyDelete
    Replies
    1. Here's an example for your scenario. E.g. You are retrieving the Order entity with List records action and send an email with the Order ID and Status Reason (statuscode) for each record.
      https://i.imgur.com/HAiELzo.png

      You can add the formatted value of the statuscode field using an expression.
      https://i.imgur.com/qNG9LHr.png

      This is how it will be shown in your Send an email action
      https://i.imgur.com/ucOcTk8.png

      This is the output in the flow run history
      https://i.imgur.com/W7I13nw.png

      Delete
  6. I figured it out. I was able to enter the code format you described in the expression field to set a variable:

    items('Apply_to_each_3')?['statuscode@OData.Community.Display.V1.FormattedValue']

    I was getting an error because I was referencing the wrong Apply to each repeat step.

    Thank you for clarifying!

    ReplyDelete
    Replies
    1. I'm glad to know that it is finally working fine.

      Delete
  7. Thank you for this post, it’s extremely helpful. Will this also work for Multi-Select Option Sets?

    ReplyDelete
    Replies
    1. Yes, it also works for Choices (Multi Select Option Set) columns. The formatted value returns the text value of the selected options separated by the semicolon and (E.g. "Business; Stakeholder")
      I have updated the post to include that info. Thanks for your question.

      http://linnzawwin.blogspot.com/2020/07/get-lookup-display-name-and-option-set.html#choices

      Delete
  8. You are the man Linn! This is the only article on the internet that has the right answer to this question. Thank you so much for blogging weekly.

    ReplyDelete
    Replies
    1. Thanks for the encouragement, Matthew. I will try my best to keep it up.

      Btw, I updated this post and included the steps for using Parse JSON and select from the Dynamic Value list in citizen developer-friendly approach.
      https://linnzawwin.blogspot.com/2020/07/get-lookup-display-name-and-option-set.html#parsejson

      Delete
  9. Have you managed to get annotations to work with large datasets?

    I have an OData query that returns over 9000 records. Using the CDS "List Records (current environment)" will get me the records, but after the first 5000 records the annotations are dropped. I am guessing that it is not including the annotations setting in the header when it is requesting the next page of records, but I cannot see how to add this in myself.

    ReplyDelete

Post a comment