Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
FetchXML - get contacts belonging to accounts with similar attributes RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Good afternoon,

    I'm trying to come up with a FetchXML query which will return a list of contacts with similar attributes to a particular parent account.  The following SQL query does exactly that:

    select Contact.FullName, Contact.ContactId, RC.Name
    from Contact
    left join account PC on contact.ParentCustomerId = PC.accountid
    left join Account RC on PC.[GroupingField1] = RC.[GroupingField1] or PC.[GroupingField2] = RC.[GroupingField2]
    where RC.AccountId = 'DE67B528-1EF3-E211-867C-00155D652D03'
    order by contact.FullName

    The GUID 'DE67B528-1EF3-E211-867C-00155D652D03' will be replaced with a parameter.

    Basically I want to list any contact whose parent account shares common attributes (in "Grouping Field 1" or "Grouping Feld 2") with the account specified by the parameter.

    Any FetchXML wizards out there?

    Saturday, November 9, 2013 8:21 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Are you doing a report or a view? It's certainly possibly in a report, not so sure about a view though.

    If you're doing a report, then you can use outer joins between account and contact for each attribute you want to match on. The great thing about link entities is you can link from/to any attributes, even if the 2 entities aren't related.

    I've done a quick example below, where we have a report showing a list of accounts where 1 or more contacts have the same telephone1 or emailaddress1. Then it lists the matching contacts.

    You could very easily add a condition to the account if you want to only show results for 1 account (or use prefiltering).

    FetchXML:

     
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="telephone1" />
    <attribute name="emailaddress1" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="telephone1" to="telephone1" alias="con_ph" link-type="outer">
      <attribute name="contactid" />
      <attribute name="fullname" />
      <attribute name="telephone1" />
    </link-entity>
    <link-entity name="contact" from="emailaddress1" to="emailaddress1" alias="con_em" link-type="outer">
      <attribute name="contactid" />
      <attribute name="fullname" />
      <attribute name="emailaddress1" />
    </link-entity>
  </entity>
</fetch>

    Report:

    The report just groups by accountid, then 2 inline groupings for matching contacts by email, and matching contacts by phone. In my case I also added visibility conditions to each group so that they're only displayed if there are matches.

    You can see Account 1 has 1 match on the email address, and 2 matches on the phone, and Account 2 has 1 match on the email.

    Hope that helps

    Paul

    If my response helped you find your answer please show your thanks by taking the time to "Mark As Answer" and "Vote As Helpful".

    Twitter LinkedIn Facebook Blog Magnetism

    Saturday, November 9, 2013 9:46 PM