web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / CRM Fetch XML for Late...
Microsoft Dynamics CRM (Archived)
Suggested Answer

CRM Fetch XML for Latest Created Record.

(0) ShareShare
ReportReport
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)
  • suresh maurya Profile Picture
    suresh maurya 630 on at

    For testing of FetchXML, you can test here online http://msxrmtools.com

  • Community Member Profile Picture
    Community Member on at

    Gaurav Thakur, try to create another Dataset for the phonecalls containing the AccountID   and order the records descending.

    Then use a lookup function in your  table to match the Accountid of the two datasets:

    =lookup(AccountId,AccountId,Phonecall,"Phonecall).

  • Royal King Profile Picture
    Royal King 27,686 on at

    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.

  • Suggested answer
    Hamzeh Zawahreh Profile Picture
    Hamzeh Zawahreh 3,674 on at

    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.

  • Gaurav Thakur Profile Picture
    Gaurav Thakur 100 on at

    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

  • Suggested answer
    Hamzeh Zawahreh Profile Picture
    Hamzeh Zawahreh 3,674 on at

    Hello.

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

  • Gaurav Thakur Profile Picture
    Gaurav Thakur 100 on at

    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

  • Royal King Profile Picture
    Royal King 27,686 on at

    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.

  • Gaurav Thakur Profile Picture
    Gaurav Thakur 100 on at

    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 ?

  • Royal King Profile Picture
    Royal King 27,686 on at

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Shidin Haridas Profile Picture

Shidin Haridas 2

#2
Abdullah13 Profile Picture

Abdullah13 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans