Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Get all emails, phone calls, appointments for contacts on account custom entity

  • 1.  Get all emails, phone calls, appointments for contacts on account custom entity

    GOLD CONTRIBUTOR
    Posted Mar 15, 2019 01:22 PM
    I am looking for some feedback on how to proceed with this problem.
    Scenario: I have a custom entity with an N:N relationship for accounts. I also have connections to this entity for contacts of different roles. Ex. "John Doe" as "Stakeholder". I would like to run a report or query that pulls all emails, phone calls, and appointments for all contacts who are in the accounts associated with the custom entity record.

    I attempted this using FetchXML, but there are too many levels for this to be effective. I can only pull the data using XrmToolBox, and I am not able to make this available to each user.

    What is a sustainable approach to this need?

    Example of the FetchXML code (works in XrmToolBox only).
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
      <entity name="activitypointer" >
        <attribute name="activitytypecode" />
        <attribute name="subject" />
        <attribute name="statecode" />
        <attribute name="prioritycode" />
        <attribute name="modifiedon" />
        <attribute name="activityid" />
        <attribute name="description" />
        <attribute name="ownerid" />
        <attribute name="senton" />
        <attribute name="allparties" />
        <order attribute="modifiedon" descending="false" />
        <filter type="and" >
          <condition attribute="statecode" operator="eq" value="1" />
          <condition attribute="activitytypecode" operator="in" >
            <value>4202</value>
            <value>4210</value>
            <value>4201</value>
          </condition>
          <filter type="or" >
            <condition attribute="regardingobjectid" operator="eq" uiname="Record ABC" uitype="new_linked" value="{AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA}" />
            <condition entityname="opp" attribute="new_linked" operator="eq" uiname="Record ABC" uitype="new_entity" value="{AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA}" />
            <condition entityname="customentity" attribute="new_linked" operator="eq" uiname="Record ABC" uitype="new_entity" value="{AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA}" />
          </filter>
        </filter>
        <link-entity name="opportunity" from="opportunityid" to="regardingobjectid" link-type="outer" alias="opp" />
        <link-entity name="customentity" link-type="outer" alias="customentity" />
      </entity>
    </fetch>


    ------------------------------
    Adam Posegate
    Fox Valley Technical College
    Appleton WI
    ------------------------------
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Get all emails, phone calls, appointments for contacts on account custom entity

    TOP CONTRIBUTOR
    Posted Mar 16, 2019 12:39 PM
    Adam,
        The word 'sustainable' can be interpreted different ways. Are you asking for a way to do it without custom code, such as JavaScript? How about third party products?
    Frankly, the N:N relation does not give you many options to present the data you demonstrated above. I am not clear on the particular use-case so I cannot offer an implementation suggestion. Based on your specific query above, I think your best option is to use JavaScript to modify a subgrid on an entity form, using the FetchXML you developed. There are a number of blog articles that show you how to do it. Here are a couple:


    CRM 2015 (Update 1) filter a sub grid to show data based on hierarchy using FetchXML
    From <http://mehmet.ozdemir.id.au/2015/03/crm-2015-filter-a-sub-grid-to-show-data-based-on-hierarchy-using-fetchxml/>

    Dynamically Change Sub-Grid fetchXML with a Small Twist (Fix)
    From <http://blog.navantis.com/dynamically-change-sub-grid-fetchxml-with-a-small-twist-fix/>

    ------------------------------
    If this answered your question, please click on the arrow button next to Reply Inline and choose 'Make Best Answer.'
    Thanks.
    Nelson Johnson, Solution Architect
    BroadPoint, Inc., Bethesda MD
    Link with me! https://www.linkedin.com/in/nelsonjohnson/
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Get all emails, phone calls, appointments for contacts on account custom entity

    GOLD CONTRIBUTOR
    Posted Mar 18, 2019 09:37 AM
    Sustainable, in my case, means not having to manually run this query through XrmToolBox each time.

    The cliff I encounter with filtering the subgrid data (link 1) is that the method is no longer supported. I will look further into the second link. It has some potential for this effort.

    ------------------------------
    Adam Posegate
    Fox Valley Technical College
    Appleton WI
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role