Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / FetchXML to fetch rela...
Microsoft Dynamics CRM (Archived)

FetchXML to fetch related phone call to case to account

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi experts,

In the phone call activity entity, I have a field called new_time where I can enter the duration of the phone call. 

Now, 

For instance, account A. Datum has 3 cases and each case has 1 phone call activity related to it. So I would my FetchXml to show total 3 phone call activities for the 3 cases for the account A.Datum. Another condition I want to add is to only show the phone call activities that are created on in the last 90 days. 

I have created the following fetchxml to get me started but I'm having problems with it:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="activitypointer">
    <attribute name="new_time" alias="time"/>
    <order attribute="modifiedon" descending="false" />
    <filter type="and">
      <condition attribute="createdon" operator="last-x-days" value="90" />
          <link-entity name="activitypointer" from="activitypointer" to="incidentid" link-type="inner" alias="aa">
          <link-entity name="customerid" from="incidentid" to="customerid" link-type="inner" alias="ab">
          <link-entity name="accountid" from="accountid" to="customerid" link-type="inner" alias="ac">
            <filter type="and">
              <condition attribute="accountid" operator="eq" value="A. Datum" />
</link-entity>
    </filter>
  </entity>
</fetch>


Please help me fix this.

Thanks,

Jon

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: FetchXML to fetch related phone call to case to account

    Hi Mark,

    Thanks for your reply. 

    After some trial and error, I got my solution.

    This is the FetchXML below for anyone who may find it useful.

    <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>  <entity name='activitypointer'>     <link-entity name='activityparty' from='activityid' to='activityid' link-type='inner' alias='af'>      <link-entity name='phonecall' from='activityid' to='activityid' link-type='inner' alias='ag'>   <attribute name='new_time' alias='time' />       <filter type='and'>          <condition attribute='createdon' operator='last-x-days' value='90' />        </filter>        <link-entity name='incident' from='incidentid' to='regardingobjectid' link-type='inner' alias='ah'>          <link-entity name='account' from='accountid' to='customerid' link-type='inner' alias='ai'>            <filter type='and'>              <condition attribute='accountid' operator='eq' value='GUIDValue' />            </filter>          </link-entity>        </link-entity>      </link-entity>    </link-entity>  </entity></fetch>


    Thanks,

    Jon

  • Suggested answer
    Mark Carrington Profile Picture
    Mark Carrington 334 on at
    RE: FetchXML to fetch related phone call to case to account

    I'd suggest trying to build your query in Advanced Find and exporting the FetchXml from there to see how it builds it as a good way to learn. There's a few things I'd look at in this example:

    1. The <link-entity> needs to be outside the <filter>. Because you're using inner joins this is implicitly a filter anyway.
    2. The link from activitypointer to incident needs to specify "incident" as the name of the entity you're linking to, and "regardingobjectid" as the attribute on the phone call you're linking from
    3. The filter on accountid is using the account name as the value. You either need to use the accountid (Guid) as the value, or change the condition to filter on the name attribute instead
    4. You're retrieving all activity types, not just phone calls.

    I've rewritten your Fetch Xml below which I think does what you need:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="phonecall">
    <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" />
    <filter type="and">
      <condition attribute="createdon" operator="last-x-days" value="90" />
    </filter>
    <link-entity name="incident" from="incidentid" to="regardingobjectid" alias="ac">
      <link-entity name="account" from="accountid" to="customerid" alias="ad">
        <filter type="and">
          <condition attribute="name" operator="eq" value="A. Datum" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>


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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans