70
Is it possible to do this in fetchXML as part of an SSRS report query? This would be part of a query that is searching for firstname, lastname, cfn_companyname from contacts and needs to get the address fields from the customeraddress table based on the value of the cfn_cbrmailingto field on the contact.
select line1 from CustomerAddress where AddressNumber = contact.cfn_CBRMailingTo and ParentId = contact.ContactId as Address1_Line1
*This post is locked for comments
Hi jimfield,
You could use a parameterized report as described in the below article.
https://blogs.msdn.microsoft.com/crm/2011/03/18/parameterizing-fetch-based-reports/
Cheers,
Nadeeja
If the answer solves your problem, please mark as Verified. Thanks.
My Blog: http://dyn365apps.com/ - Follow me on Twitter: https://twitter.com/dyn365apps
LinkedIn: https://www.linkedin.com/in/nadeeja
What I am looking for is more like this:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="contact">
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="cfn_companyname" />
<attribute name="contactid" />
<order attribute="fullname" descending="false" />
<link-entity name="customeraddess" from="parentid" to="contactid" alias="ca">
<attribute name="line1" />
<attribute name="line2" />
<attribute name="city" />
<attribute name="postalcode" />
<attribute name="stateorprovince" />
<filter type="and">
<condition attribute="addressnumber" operator="eq" value=<contact.cfn_mailingto> />
</filter>
</link-entity>
</entity>
</fetch>
Where contact.cfn_cbrmailingto is an option set with the following values:
Primary Address = 1
Mailing Address = 2
Office Address = 3
Home Address = 4
Is there a way I can plug in the cfn_cbrmailingto value in the filter of the link-entity?
Hi Jimfield, thanks for reaching out.
This is possible, but depends on relationships that you have created with the custom fields. I used contact.addresstype instead of contact.cfn_CBRMailingTo.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="customeraddress">
<attribute name="name" />
<attribute name="line1" />
<attribute name="city" />
<attribute name="postalcode" />
<attribute name="telephone1" />
<attribute name="customeraddressid" />
<order attribute="name" descending="false" />
<link-entity name="contact" from="contactid" to="parentid" alias="ab">
<filter type="and">
<condition attribute="address1_addresstypecode" operator="eq" value="3" />
</filter>
</link-entity>
</entity>
</fetch>
If the cfn_CBRMailingTo field is an option set, then in the condition attribute above you would use the value from the field metadata rather than the actual name of the option.
Let me know if this is what you were looking for.
Hi jimfield,
Write the query in SQL and test it to make sure it works. Then go to the below site to convert your SQL statement to FetchXML.
Cheers,
Nadeeja
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (