Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics 365 | Integration, Dataverse... / Power Automate - CDS c...
Microsoft Dynamics 365 | Integration, Dataverse...
Suggested answer

Power Automate - CDS current env. - Optionset labels

(0) ShareShare
ReportReport
Posted on by tpeschat 4,926

Hi,

I hope it is OK that I ask a Power Automate question in this board, but it is Dynamics specific. 

I try to query contacts using the Common Data Service (current environment) connector using the list records action. I defined a select, filter and expand query, to limit the results and attributes and get attributes from linked entities.

BUT unfortunately for optionset fields the labels are not part of the returned JSON, while when no select query is defined, they are returned. 

Did you already experience this situation and how did you solve it?

Thx a lot in advance and br

Thomas

(btw sorry for the outdated signature below ;))

  • Stephen Pryke Profile Picture
    Stephen Pryke 407 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Wow, Linn Zaw Win - you just saved me so many headaches. Been battling this for weeks!!

    Now have the ability to use a nice clean FetchXML query with the current env connector AND get labels easily! Works for lookup labels, too!

    Made my day :)

  • Suggested answer
    Linn Zaw Win Profile Picture
    Linn Zaw Win 3,405 on at
    RE: Power Automate - CDS current env. - Optionset labels

    tpeschat

    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.

    linnzawwin.blogspot.com/.../get-lookup-display-name-and-option-set.html

  • cloflyMao Profile Picture
    cloflyMao 25,198 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi Thomas,

    Thanks for sharing the link and more information about the topic.

    Regards,

    Clofly

  • Suggested answer
    tpeschat Profile Picture
    tpeschat 4,926 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi Clofly, 

    I experimented a bit more and found out how to retrieve optionset lables from the stringmap table and how to map them. 

    Documented it in a blogpost:

    http://crmaddicted.blogspot.com/2020/01/working-with-optionset-lables-in-power.html?m=1

    Br Thomas 

  • tpeschat Profile Picture
    tpeschat 4,926 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi Clofy,

    I went for the regular Common Data Service connector now.

    Pare JSON did not work out, so I went for an expression items('<Apply to Each function name>')['_<attributename>_label'] and that works.

    thx a lot for your efforts

    Thomas

  • cloflyMao Profile Picture
    cloflyMao 25,198 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi Thomas,

    I've searched many articles about the topic, I tired to do sub query in fetchXml, 

    (Because we can find optionset value by stringmap entity: $select=value,attributevalue&$filter=objecttypecode eq 'entity' and attributename eq 'optionset fieldname')

    however, such feature is not supported.

    Ideally if we could run Sql directly with sub query then everyhing would go well, such as 

    select a, b, (select value from stringmaps where attributename = optionset fieldname) formattedvalue from entity

    So I think that currently there is no easy way to add formatted value in a single query for the connector, while it would be cumbersome to build extra query for optionset label.

    Anyway, thanks for found this little issue, and hope it would be fixed in future release.

    Regards,

    Clofly

  • tpeschat Profile Picture
    tpeschat 4,926 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi,

    I already tried the fetchXML path, but same result

    thx

  • tpeschat Profile Picture
    tpeschat 4,926 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi Clofy,

    they are. There are two different connectors for the Common Data Service. One is called "Common Data Service" and the other is called "Common Data Service (current environment)". The second (current environment) is only offered, if your Flow is part of a solution.

    The current environment one offers more actions (e.g. AI stuff, triggering custom actions a.s.o.) and it allows in the list record action to define a select or Fetch XML query.

    But unfortunately for optionsets it does not return the labels.

    br T

  • cloflyMao Profile Picture
    cloflyMao 25,198 on at
    RE: Power Automate - CDS current env. - Optionset labels

    Hi Thomas,

    As you mentioned, there would be no formattdValue in the connector's request header compared with normal query.  

    I'm trying to find whether it could be done by fetchXml.

    Regards,

    Clofly

  • Suggested answer
    tpeschat Profile Picture
    tpeschat 4,926 on at
    RE: Power Automate - CDS current env. - Optionset labels

    OK, I experimtend a bit and found out that it is not related to the select query, but to the connector itself.

    The Common Data Service (current environment) connector does not return the option set labels at all. No matter if you define a select query, or not.

    The normal Common Data Service connector though returns the option set labels in the JSON ...

    Which is a pitty, because that way you can not limit the returned attributes, to keep the JSON small and prevent buffer overrun.

    Also the depricated Dynamics 365 connector would return the labels ...

    For now I have to stick to the normal CDS connector, but I hope they will fix this in the other connector

    br T

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Get batch job history log from D365 FO to Power Automate

Product updates

Dynamics 365 release plans