using FetchXML to get the values of Global Choices dropdown fields

Like (0) Share

Report

Posted on 29 Jan 2021 19:19:48 by dvcooper

Hi,

I'm using SSRS for creating reports but missing how you pull the data from Choice lookups.

For example I have this field <attribute name="cr4fe_servicepaymentterms" /> in my fetchxml, but it is just returning the ID, not the text of the lookup.

thanks

All responses (4)

Answers (1)

Hitesh Nagpal 5 on 15 Feb 2023 at 12:35:36

Like (0)

Report

RE: using FetchXML to get the values of Global Choices dropdown fields

Hello, I was also stucked in this common problem, I was using dataverse, for me what worked is below syntax while displaying the values

for choice field - logicalname.label (here using .value will give you id of the choice)

for lookup field - logicalname.name ( in my case I needed name, you can try other column here)

Thanks,

Hitesh
dvcooper 35 on 31 Jan 2021 at 15:11:05

Like (0)

Report

RE: using FetchXML to get the values of Global Choices dropdown fields

Thanks Pablo,

This answered my question in a round about way : )

I'm using XrmToolbox to build my query string, however, what I noticed is that the results in XrmToolbox give me the quid value not the lookup value. so when i do this query in toolbox:

<fetch>
<entity name="opportunity" >
<attribute name="ownerid" />
<attribute name="owneridname" />
<attribute name="cr4fe_koboldservice" />
</entity>
</fetch>

I only get these results

But when I run the same query in visual studio, I get both the guid and the lookup text as expected.

Thanks again for your response.

Verified answer

PabloCRP

1,088 on 31 Jan 2021 at 07:51:10

Like (1)

Report

RE: using FetchXML to get the values of Global Choices dropdown fields

Hi dvcooper,

Indeed when you retrieve a lookup value with FetchXml you'll get a GUID value.

Making a link entity and get the name will work.

But as you are using SSRS notice that 3 field are adding to your DataSet. So pick just cr4fe_servicepaymentterms

cr4fe_servicepaymentterms	LOOKUP's TEXT	e.g: FOO
cr4fe_servicepaymenttermsValue	LOOKUP's GUID	e.g: 319e2387-38b1-4200-91bb-3bb5410a4a3a
cr4fe_servicepaymenttermsEntityName	LOOKUP's EntityName	e.g: cr4fe_servicepaymentterms

please consider marking as an answer if it was helpful

Suggested answer

prt33k 6,907 on 31 Jan 2021 at 00:40:25

Like (1)

Report

RE: using FetchXML to get the values of Global Choices dropdown fields

Hi David,

You will not get the name field directly in fetchxml.

You need to outer join the link entity and select the primary attribute from this entity.

You can go to advanced find and then select the column you want into display column and then download the fetch..

An example to show accountnumber from contact having account lookup as company name:

<link-entity name="account" from="accountid" to="parentcustomerid" visible="false" link-type="outer" alias="a_c">
<attribute name="accountnumber"/>

Thanks,

Prateek