Microsoft Dynamics 365 | Integration, Dataverse...

Power Automate - CDS current env. - Optionset labels

(0) Share

Report

Posted on by tpeschat

4,930

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 ;))

I have the same question (0)

All responses (13)

Answers (0)

cloflyMao 25,210 on at

Like (1)

Report
Copy link

Link copied!

Hi Thomas,

Do you mean that optionset label is not available in Dynamic Content items?

If so, I find that option set label will still be retrieved from raw JSON result.

For example, there is a optionset field called Priority in my custom activity entity,

and both value and text are available.

Then I create a HTML table to show records,

other available dynamic content items are formatted as "@item()?['xxx']" in peek code view,

so I also changed it to retrieved label field name: "_prioritycode_label" manually

Then formatted value(label) will also display in final output.

So try to copy an existing item and replace it with formatted value field name.

Regards,

Clofly

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report
Copy link

Link copied!

Hi Clofly,

thx a lot for all the effort you put into your answer.

But my issue is, that the _<fieldname>_label is not part of the JSON output of the list records action. So your whole approach does not work for me.

It is part of the JSON output, if I do a "normal" list records. But as soon I define a query to limit the returned attributes, only the optionset values, but not the labels are returned in the JSON. I tried to add the _<fieldname>_label to the select query, but that resulted in a invalid request.

br Thomas

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (1)

Report
Copy link

Link copied!

Hi Thomas,

Could you share me which action you're using?

Select Query, filter query and many other fields are not available in my environment.

Did you create it from a solution?

Regards,

Clofly

Was this reply helpful? Yes No
Suggested answer

tpeschat 4,930 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I already tried the fetchXML path, but same result

thx

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

tpeschat 4,930 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No