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

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 support formatted values and use 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.

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.