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


One of the limitations in using Common Data Service (current environment) connecter in Power Automate (which might probably be renamed as Dataflex Pro 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 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 option set value with "_*optionsetfield*_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 "_optionsetfield_label" property or use the string map entity to retrieve the label text against the entity name and option set 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 field types
    • Whole Number, Duration, Timezone, Language
    • Decimal Number, Floating Point Number, Currency
    • Date and Time, Date Only
    • Two Options, Option Set, Multi Select Option Set
    • Lookup, Polymorphic Lookups
  • Microsoft.Dynamics.CRM.associatednavigationproperty for lookup fields
  • Microsoft.Dynamics.CRM.lookuplogicalname (which was not returned in output JSON previously for certain exceptional cases like partyid of the Activity Party entity)
  • 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 option set value label is available as _optionsetfield_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 Dynamics Content pane and you will have to enter the expression manually in the following syntax.
fieldname@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']

Here is the flow with Get a record action to retrieve some fields from Account entity 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.


🛈 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 field types such as lookup display name, option set value label, formatted date/time by using the OData.Community.Display.V1.FormattedValue property of the field in the expression.

Comments

Popular Posts

[Power Automate] How to Set Lookup Field Value in Common Data Service (current environment) Flow Connector

How to Set Lookup Fields with Null Value from Dynamic Content in CDS (current environment) Connector

[Power Automate] List Records - Use Expand Query to Retrieve Related Data in flow

[Power Apps] Using Common Data Service's Lookup Data Type Field in Canvas App

[Bug] Using the ClearCollect() Function in PowerApps does not Pull any Lookup Columns from Common Data Service (CDS) Data Source

Getting the Lookup Attribute of the Parent Record in Power Apps Canvas App Development with Common Data Service (CDS)

Pass Parameters to Quick Create Form in Dynamics 365 using formContext.data.attributes

Send Email from Dynamics 365/CDS with Attachment from Notes Using Flow

Access Flow Run History within a Record in Dynamics 365/CDS