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.

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
    Replies
    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.
      https://linnzawwin.blogspot.com/2021/01/retrieve-more-than-100000-dataverse.html

      Delete
  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?

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

      Delete
    2. hazer kirbasoglu06 April, 2021 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

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

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

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

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

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

      Delete
    2. Microsoft Dataverse connector

      Delete
    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

      Delete
  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:

    https://community.dynamics.com/365/f/dynamics-365-general-forum/427836/get-the-option-set-value-label-in-a-single-query-using-a-cds-current-environment-and-populate-in-a-subject-through-power-automate

    Can you check in and see. Thanks

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

      Delete
  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 ?

    ReplyDelete
    Replies
    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' https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones

      if
      (
      or
      (
      equals(dayOfWeek(convertFromUtc(utcNow(), 'New Zealand Standard Time')), 0),
      equals(dayOfWeek(convertFromUtc(utcNow(), 'New Zealand Standard Time')), 1)
      ),
      96,
      48
      )

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

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

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

    ReplyDelete
    Replies
    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?

      https://knowledge-junction.com/2020/03/16/power-platform-power-automate-issue-invalidtemplate-unable-to-process-template-language-expressions-in-action-create_item-the-template-language-expression-cannot-be-evaluated-becaus/

      Delete
  17. Thank you for this, it is really well explained and very helpful

    I have a question regarding a specific requirement I have... My application supports English and French, and I need to produce an email that is bilingual. I am getting various field values, some of which include Lookups and Choice fields. For the lookups, I can use ExpandQuery to get the English and French fields from the related tables, but for Choice fields, there are no separate fields for each language, the "FormattedValue" is automatically retrieved based on the current user's preferred language.

    Is there a way to retrieve a specific language (localization) for a Choice field?
    English = 1033, French = 1036

    ReplyDelete
    Replies
    1. Yes, you can
      Retrieve from the String Map table as similar to the query in the following post but by adding additional filter
      e.g. attributename eq 'xyz_choicecolumn' and objecttypecode eq 'xyz_tablename' and langid eq 1036 and attributevalue eq <>

      https://himbap.com/blog/?p=3210

      Delete
    2. Hi! Thanks for a great post.I'm actually looking for an answer for a similar question. If dataverse environment has several language packs installed, how can I get a localized choice label for a specific language in Power Automate?

      Delete

Post a Comment

Popular Posts