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 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.

e.g. outputs('Get_an_Account_Record')?['body/statuscode@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.

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.


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.


  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.

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

  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. :)

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

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

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

  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.

    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.


    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.

    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,

      write your expression as below to get the formatted value.

      Check out this screenshot and it would be clearer for you

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

  4. This comment has been removed by the author.

  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:


    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.

    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.

      You can add the formatted value of the statuscode field using an expression.

      This is how it will be shown in your Send an email action

      This is the output in the flow run history

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


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

    Thank you for clarifying!

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

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

    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.

  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.

    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.

  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.

    1. Sorry, it took a while to reply this comment with my vacation and stuff.
      I'm afraid you will have to disable the Pagination in the Settings and handle the paging by yourself with Skip Token. Check out my last blog post about it.

  10. hi Linn, good read, thansk for sharing.
    question, how do you filter rows using the same formatted column?
    i have been getting syntax error when trying to filter using formatted column for a choice a field.
    any ideas?

    1. Hi Khurram

      Formatted columns are just virtual columns and you cannot filter based on the formatted values. You will need to use the backend value (e.g. GUID for lookup, integer for choice column, etc.)

      If you need to filter based on the text value of the lookup column, you can inner join and filter it. Use FetchXML Builder tool for such filter expressions.

    2. hazer kirbasoglu6 April 2021 at 22:35

      hi Linn,

      I see your comments 'virtual columns' can not filter based on formatted values.
      I have choice type field on table.
      Is there any possibility to filter based on that fields label ?
      For example ,
      field name -> Gender (Choice)
      first value -> Label : Man , Value : 100xxx
      second Label : Women , Value : 1000xx1

      is there any possibility filter by label for that table on power automate ? Thanks

    3. @hazer, No, it's not possible to filter with the label of the options (Man, Woman, etc.).
      The only way is to filter by the integer value 100xxx, 100xx1, etc.

      You may get the integer value of the option from the StringMap table using the label.

  11. I can't figure out how to use this when I am creating a new record. I am converting a spreadsheet via Flow where I just have the label, but I need to set the underlying number for that label.

    The drop down on that entity field in Add a New Row shows the labels and code numbers but setting the field to the label throws an error, and I can't see (except String Maps) how to get the code.

    1. This is only for the getting the data out of Dataverse and not for setting the data (create/update) into Dataverse. If the choice options are not changed a lot, I would normally use "Switch" condition or If() expression to set the hardcoded integer value based on the string value.
      Otherwise, getting the string maps is the only way I'm afraid.

  12. Hi Linn, This was very useful link.
    But I have an issue while using @OData.Community.Display.V1.FormattedValue as this says expression invalid.
    My Parse JSON only returns "@Microsoft.Dynamics.CRM.associatednavigationproperty" and "@Microsoft.Dynamics.CRM.lookuplogicalname". @OData.Community.Display.V1.FormattedValue is not present. Can you help me with this.

    1. What connector are you using for that particular step? Is it Microsoft Dataverse connector or Microsoft Dataverse (legacy) connector?

    2. Microsoft Dataverse connector

    3. I've never experienced though. Please contact me in LinkedIn message or Twitter DM to share the screenshot of the output of your "List rows" step

  13. Hi Linn,

    This is amazing link to answer my question recently. But I still have a problem. as I can't load the picture in the comment, I created a post in the MSFT Forum. Here is the link:

    Can you check in and see. Thanks

    1. Sorry for the late reply. I just checked out that forum thread and I am glad it was sorted.

  14. HI Linn,
    I have one query am using below flows which should get the lists of cases which not modified in last 48 hrs & send emial to owninguser for this fetchxml using older than 48 hr cases but here my problem is it should exclude weekend which is Friday & Saturday so that i shouldnot count how can i achieve this & found like DayofWeek we can use but not getting where to use before sending email ?
    Any clue ?

    1. I guess you will have to check DayofWeek for today and if it is Sunday or Monday, set the parameter to 96 hours instead of 48 hours (to cover the weekend Fri/Sat).

      Here's the sample expression and you will have to replace your own time zone with 'New Zealand Standard Time'

      equals(dayOfWeek(convertFromUtc(utcNow(), 'New Zealand Standard Time')), 0),
      equals(dayOfWeek(convertFromUtc(utcNow(), 'New Zealand Standard Time')), 1)

  15. Something new I learnt today and it helped me with my flow. Thank you for taking time to blog this.. Appreciate the effort.

    1. Glad to know that it helped. Thanks for your encouraging comment.

  16. Please help in this flow.
    1. Need to copy item from dataverse table to SharePoint list on creation of item.

    Issue: once data is not in dataverse field it gives me an error saying "cannot be evaluated because property doesn't exist".

    When data is in the dataverse field it runs successfully and copied in SharePoint list.

    1. Hi Anurag
      Is it similar to the issue in this link? I am not sure if it applies to your issue but can you try to re-create your flow and see if it solves the problem?


Post a Comment

Popular Posts