Hello All,
Good to be back with another know how post. Lets go through a simple way to retrieve a MSCRM optionset label using fetchxml that can be used with C#/JS/SSRS.
Usual way to retrieve an optionset value in c# or JS is by using RetrieveAttributeRequest and refer this blog for the same.
RetrieveAttributeRequest - Option set label retrieval
This time, wanted to stick with old school SQL approach by using stringmap table, so replicated the same logic into fetchxml query and below is the simple fetchxml. This will be handy in SSRS report development to retrieve optionset details.
What is stringmap?
Stringmap is a table in crm which contains all the optionset options and its related details. Will write a quick article soon around stringmap and its fields 😉
Coming back to the topic, below is the fetchxml to get optionset label, hope the fields are self explanatory.
FetchXML
<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>
Result
<result>
<value>Consultant</value>
</result>
------ Not working
ReplyDeletePlease provide more details around the issue you are facing?
Deleteobjecttypecodename should be objecttypecode. It changed with the 9.0 API I believe.
DeleteHi,
ReplyDeleteThe idea is good in principle, but I have 10 optionset type columns in a table and need to show the actual labels to the users.
When I run the FetchXML and look at the returned XML file both the ID and Label are there but for some reason only the ID gets back to the report.
I would expect that this basic functionality of showing both the ID and the label for optionset type fields be available out of the box.
Tnx,
Michael
Hey Michael, If you are writing an SSRS report for most part the optionset label should be available as a part of regular fetch and you do not need string map at all. String map comes into picture only when you have value of the optionset and the formatted_value(Label) is empty!
DeleteHello Ramanathan Rajendran
ReplyDeleteIt's a very good tutorial to get the optionset from a specific entity.But the problem i am facing in the output is that it's returning the value of the option selected instead of show me the name of the selected value.
What i should change to get the label text of the selected value ?
Looking to hear from you.
Thank you!
There are two attributes in the stringmap entity(table) that points to optionset value and label.
DeleteLabel will be stored in the attribute "value"
Value will be stored in the attribute "attributevalue"
Ideally the above query should return the label text! Feel free to email(ramanathen@hotmail) me if you have any queries.
Hello Ramanathan,
DeleteI've sent you an email about my issue. Please let me know if you have received my email.
Thank you!
This comment has been removed by the author.
ReplyDeleteThis may be a result of a newer version of Dynamics 365, but objecttypecodename is no longer valid for fecthxml or odata queries. objecttypecode is, however, a valid attribute name and can be used with the text of the entity you are filtering on in place of objecttypecodename.
ReplyDelete