Microsoft Dynamics CRM (Archived)

CRM Fetch XML for Latest Created Record.

(0) Share

Report

Posted on by Gaurav Thakur

100

Hi All,

I have a requirement of creating a SSRS report joining two Entities.

I have two entities A and B and which are related to each other.

A can have multiple B records. In report I need A with Latest B record.

For ex. Accounts with latest Phone call record.

How to achieve this ? Pls help.

Regards,

Gaurav Thakur

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (0)

Suggested answer

purwar purwar 2,836 on at

Like (0)

Report
Copy link

Link copied!

You can use below for retriving latest record:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" count="1">

<entity name="account">

<attribute name="name" />

<attribute name="primarycontactid" />

<attribute name="telephone1" />

<attribute name="accountid" />

<order attribute="createdon" descending="false" />

</entity>

</fetch>

Was this reply helpful? Yes No
Gaurav Thakur 100 on at

Like (0)

Report
Copy link

Link copied!

I need to create a report for all the records of entity A and with the data of entity B.

For Ex. Account 1 has 3 phone calls. so my report should show

Account Number, Account Name from Account and out of 3 phone call get the latest phone call record and get comments, last contacted date etc.

And report will run on all the Account records in the system.

Account 1 - Phone calls made on May 1, May 7 and May 13.

Account 2 Phone calls made on May 5, May 8.

So report should show the data from

Account 1 and Phone Call of May 13

Account 2 and Phone Call of May 8

And their respective fields.

How to do this on fetch XML ?

Regards,

Gaurav Thakur

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report
Copy link

Link copied!

I dont think there is a way using fetchxml to do this. One option would be creating new lookup field on record A to B and updating this field whenever new record B gets associated with A. this way A record will have always latest record B as lookup value and you can use this value to generate report. This way even you dont have to create SSRS report you can use report wizard in crm to get this report.

To update record A you can use workflow on create of record B

Was this reply helpful? Yes No
Gaurav Thakur 100 on at

Like (0)

Report
Copy link

Link copied!

I had done the same thing, however there will be ambiguity of same fields present in both the records and will also be available in Advance find, users will get confused so that solution will not work for me.

Is there any other solution is there for this situation ?

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report
Copy link

Link copied!

I don't think its going to create any ambiguity of data. As record A holds just reference to record B that's it. If you want you can make the lookup non searchable in the advanced find using customization. As well dont add this lookup field on the form so user wont get access to this record at all.

Was this reply helpful? Yes No
Gaurav Thakur 100 on at

Like (0)

Report
Copy link

Link copied!

User does not want a new field to be available in Entity A, as even being made that lookup field unsearchable that is available to add in any system and custom view. So they are not ok to have a new field in Entity A.

I had though of one more solution, putting a Yes/No field in Entity B and set to Yes if it is a latest record, and pull this information in reports. Will that approach be fine ?

Regards,

Gaurav Thakur

Was this reply helpful? Yes No
Suggested answer

Hamzeh Zawahreh 3,674 on at

Like (0)

Report
Copy link

Link copied!

Hello.

what's the problem in using group by account and order by created on activities date using ssrs query designer

Was this reply helpful? Yes No
Gaurav Thakur 100 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I need only latest activity at the time of running the report for all the accounts in the system, using group by and order by it will even give all the activities and not the latest activity. (Account and Activity entities is the example entities I have taken to explain the problem, I have custom entities in which I have to implement this concept. I even need to join 3 custom entities and get the results. For Ex. Entities A, B, C. A is related to B and C, so get the latest record to B and C which are associated and show in report as one row, and run the report for all the A records in the system

I am working on CRM online, so only can use Fetch XML SSRS reports. And through fetch XML getting the linked entities latest created record is not possible.

Even after creating the Yes/No option it solves my issue for two Entities relationship Entity A and B., however when I join A, B, C it fails if entity B is not having any data(and c is having), no records comes in the report

Regards,

Gaurav Thakur

Was this reply helpful? Yes No
Suggested answer

Hamzeh Zawahreh 3,674 on at

Like (0)

Report
Copy link

Link copied!

Dude.

then you will create a custom entity for each activity type for entity A, called like this A_lastPhoneCall.(contain phone id and date as fields).

then when you associated a new phone call to entity A (use association plugin Msg) then update that record with new phone id and date.

So you will have at any time the report run the last phone call.

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report
Copy link

Link copied!

had though of one more solution, putting a Yes/No field in Entity B and set to Yes if it is

a latest record, and pull this information in reports. Will that approach be fine ?

This will work but whenever you set yes to latest record you have to set all other B records to No. To do this you may need to write custom workflow/plugin.

Was this reply helpful? Yes No