How to use fetchXML to retrieve content of a letter and the associated contact details?

(0) Share

Report

Posted on by Community Member

Hi,

I am building an SSRS report to print letters and invoices. Therefore, I need to use a fetchXML to get the required data fields. I have a contact with an associated account and added a letter activity. In the activity feed of the contact I can see all his activities including the newly created letter. In order to create the fetchXML I do an advanced search and download the fetchXML query. I can select the contact details and also the letter contents. However, I am not able to get them both together, i.e. I can select the subject and description OR I can select the name and address. If I try to link the entities it always comes up with an empty data set.

I tried to start with the letter entity and then link the contacts as relation as well as vice versa and even start with the activity entity filtered by letters and then link activities-parties and contacts. The result is always the same: empty data set.

How can I use fetchXML to retrieve a letter and the corresponding contact information in Dynamics CRM Online?

Thanks for your help!

Cheers

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

Hello,

I would suggest to start from building of correct FetchXml query using Advanced Find.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Andrii,

yes, that was my intention as well. However, as I outlined above the resultset is always empty. How would I build a correct fetchXML query for letters and contacts?

Thanks!

Was this reply helpful? Yes No
a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

Please provide a fetch query and screenshot that shows how you've designed your query using Advanced Find.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

OK, I copied two scenarios. The first shows the letters, without the contact details. The find does not link any entities:

That gives the following result:

The fetchXML looks like this:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="letter">
<attribute name="subject" />
<attribute name="statecode" />
<attribute name="prioritycode" />
<attribute name="scheduledend" />
<attribute name="createdby" />
<attribute name="regardingobjectid" />
<attribute name="activityid" />
<order attribute="subject" descending="false" />
</entity>
</fetch>

In the second scenario, I added the relation Contacts, if I add any relation the resultset will be empty:

This results in the empty resultset:

The resulting fetchXML looks like this:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="letter">
<attribute name="subject" />
<attribute name="statecode" />
<attribute name="prioritycode" />
<attribute name="scheduledend" />
<attribute name="regardingobjectid" />
<attribute name="activityid" />
<order attribute="subject" descending="false" />
<link-entity name="contact" from="contactid" to="regardingobjectid" alias="a_4daca947a9b74bd79923fe32f9069364">
<attribute name="address1_composite" />
<attribute name="address1_line1" />
<attribute name="address1_postalcode" />
<attribute name="address1_city" />
<attribute name="address1_name" />
</link-entity>
</entity>
</fetch>

Maybe I am missing a bit in creating the fetchXML. But I spend lots of hours in searching and trying but all I get are the described results.

Any help is much appreciated!

Thanks!

Was this reply helpful? Yes No
Verified answer

a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

I believe that Beguz is regardingobjectid field. To include fields from regarding contact you should add link-type='outer' to link-entity fetch part like:

<link-entity name="contact" from="contactid" to="regardingobjectid" alias="a_4daca947a9b74bd79923fe32f9069364" link-type="outer">

The other thing that first screenshot of advanced find shows that you have no letters with regardingobjectid field that points to a contact. To make that kind of query work properly you should have regardingobjectid field populated with contact.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks a lot. That did it. I didn't realize that the relation can also be a single contact. I just thought that would relate to the account. I added the contact to the TO field in the letter and the account to the related. When I use the contact to the related it even shows me the address details in the second version of above's fetchXML. I then do not need the outer join. The inner join is just what I needed.

You saved my day! Thanks again!

Cheers

Was this reply helpful? Yes No
Suggested answer

Sean K 1,537 on at

Like (0)

Report

Hi Andre,

Your first Advanced Find is saying "Show me all letters", so it shows you the 2 letters.

Your second Advanced Find is saying "Show me all letters where the regarding object is a Contact", so this shows no results as one of the letters doesn't have the regarding field populated, and the other letter is regarding an account.

What are you trying to achieve?

Was this reply helpful? Yes No
Rickard Norström on at

Like (0)

Report

Hi,

That is a bit funny, to begin with, your first advanced find doesn't really match the output with regards to the number of columns, but nevermind that.

What you're doing here is that you're defining the search parameters rather than choosing which fields to present. What I don't understand is why you're getting the address fields in the view when they aren't present in the fetchXML in the first example.

The second fetchXML contains the address fields of the regarding customer (bezug) but you don't get any hits there. How are the fields of the search set up (spalten bearbeiten)?

(my German is mighty rusty and not very technical so bear with me here :))

Regards

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sean,

yes, you're right. If I think about it afterwards it looks very clearly that something was wrong with my understanding. It now works if I add a contact to the related field. I originally thought that would be always an account.

It works, once done right ;-)

Thanks!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Rickard,

actually, the letter do not have any address fields, so I wasn't surprised that the first example did not show the contact details. I just wanna make sure that I find the letters. I usually try to start with a broad search and then narrowing the criteria. Doing so will show me exactly when something goes wrong. Unfortunately, I did not manage to find the issue myself. Maybe I was too deep in trying to get this solved.

I now have a clearer view of how objects are related in Dynamics. Have it working now and the report is just doing what is should.

Thanks!

Was this reply helpful? Yes No