Get Dynamics 365 OptionSet labels in Copy Activity

John Aherne 516

Is there any way to get optionset labels from a Dynamics 365 entity using a copy activity?

All I can seem to get is the option number when I query an entity (using Common data services and Dynamics 365 linked services)

Thanks!

Accepted answer

Daniel Holth 81 Reputation points

2020-10-29T15:00:30.717+00:00

Instead of leaving this half answered. I'll share how I added option set text. Instead of having a single Copy Activity you will need atleast two. The first is to get your entity. The 2nd is to get the entity called "String Map". This contains the key number and value text for each option set on every entity. For the 3th step I used a stored procedure to join the StringMap.Key with your entity.fieldNumeric. Both of these are integers. How you combine them is up to you.
Please sign in to rate this answer.

1 person found this answer helpful.
John Aherne 516 Reputation points

2020-10-29T15:14:41.607+00:00

Thanks for that Daniel!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

ChiragMishra-MSFT 956 Reputation points

2020-06-17T06:44:10.317+00:00

Hi @John Aherne ,

Regular OptionSets can be mapped in ADF as integer type, but "MultiSelect Option Sets" can't be mapped to ADF integer type.

As a workaround, you can use a third party workflow tool (msdyncrmWorkflowTools) to write the text values of the MultiSelect Option Set as a comma-delimited string in a separate field within Dynamics 365. From there you should be able to ingest the field as a string type and parse the values.

Ref - https://github.com/MicrosoftDocs/azure-docs/issues/28027#issuecomment-628114669

Hope this helps.
Please sign in to rate this answer.
John Aherne 516 Reputation points

2020-06-17T15:13:50.417+00:00

Thanks for the reply!

I was able to get the integer value, but what I was looking for is the optionset display value. For example, I can get accountcategorycode which will be 0 or 1, but what I actually want is "Standard" or "Preferred Customer".

Even a way to copy the optionset itself would be helpful, as we could then map the values to the codes.

It doesn't look like FetchXML can do that, but don't know a lot about Dynamics or Common Data Services yet.

ChiragMishra-MSFT 956 Reputation points

2020-07-01T10:58:28.063+00:00

Hi there,

If you use a third party workflow tool (msdyncrmWorkflowTools) to write the text values of the MultiSelect Option Set as a comma-delimited string in a separate field within Dynamics 365. From there you should be able to ingest the field as a string type and parse the values.

Julian Sharp 41 Reputation points MVP

2020-07-03T08:33:52.183+00:00

The optionset labels are in a table called stringmap

You need to know the entityname and fieldname and the integer value to get the label

<fetch>
<entity name="stringmap" >
<attribute name="value" />
<filter type="and" >
<condition attribute="objecttypecodename" operator="eq" value="account" />
<condition attribute="attributename" operator="eq" value="customertypecode" />
<condition attribute="attributevalue" operator="eq" value="2" />
</filter>
</entity>
</fetch>

See this post for an example https://stackoverflow.com/questions/55536422/loading-data-from-dynamics-crm-to-sql-server

ChiragMishra-MSFT 956 Reputation points

2020-07-14T06:28:16.977+00:00

Hi @John Aherne ,

Just wanted to check - was the above suggestion helpful to you? If yes, please accept it as answer as it would help other community members going through this thread.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Get Dynamics 365 OptionSet labels in Copy Activity

1 additional answer

Your answer