0

I Have a fetch and I want to link an entity where 2 lookups contain to other linked entity ID's

Code:

  <fetch mapping='logical'>
      <entity name='serviceappointment'>
      <attribute name='activityid' />
        <filter type='and'>
          <condition attribute='statecode' operator='eq' value='3' />
        </filter>
        <link-entity name='lead' from='leadid' to='regardingobjectid' alias='af' >
          <attribute name='leadid' />
          <link-entity name='contact' from='contactid' to='new_customer' alias='ai' >
            <attribute name='contactid' /> 
          </link-entity>
          <link-entity name='new_record' from='new_recordid' to='new_record' alias='ag' >
          <attribute name='new_recordid' /> 
          </link-entity>
        </link-entity>
      </entity>
    </fetch>

This works fine however I have another entity "new_linkRecordContact" that links the contact with the new_record via 2 look up fields "new_record" & "new_contact"

I can add the link-entity to either contact OR new_record but I want it to have a condition that it contains both the linked entity

Example: 

<link-entity name='new_linkrecordrontact' from='new_record' to='new_recordid' alias='ag' >

OR

<link-entity name='new_linkrecordcontact' from='new_contact' to='contactid' alias='ag' >

but I want to use an AND instead of OR

The Logic:

'new_linkrecordcontactid' from 'new_linkrecordcontact' where 'new_linkrecordcontact.new_record' = 'new_record.new_recordid' AND 'new_linkrecordcontact.new_contact' = 'contact.contactid'

Any Ideas how to write that into a fetch?

Thank you

Richard Dewhirst
  • 229
  • 7
  • 20
  • I'm a little confused on your joins. Can you write the equivalent SQL? – Daryl Jan 16 '14 at 14:09
  • SELECT 'new_linkrecordcontactid' FROM 'new_linkrecordcontact' WHERE 'new_linkrecordcontact.new_contact' = 'contact.contactid' AND 'new_linkrecordcontact.new_record' = 'new_record.new_recordid' – Richard Dewhirst Jan 16 '14 at 15:22
  • Where does new_record come from? – Daryl Jan 16 '14 at 15:28
  • Its one of the linked entities in the fetch, so is contact – Richard Dewhirst Jan 16 '14 at 15:32
  • confusion could be that the lookup field name on new_linkrecordcontact entity is the same as the new_record entity – Richard Dewhirst Jan 16 '14 at 15:33
  • 1
    So really you're attempting to do this:SELECT 'new_linkrecordcontactid' FROM 'new_linkrecordcontact' INNER JOIN new_linkrecordcontact.new_contact = contact.contactid INNER JOIN new_linkrecordcontact.new_record = new_record.new_recordid – Daryl Jan 16 '14 at 15:58
  • Yes I think that's about right. This could be an limitation with FetchXML? I am using ssis data flow designer so have used the Merge Join Function as a work around. however it has to pull in all the new_linkrecordcontact records then apply filter/join which seem massively inefficient ... but it works. if you know of a better way please let me know :) thanks fo you time – Richard Dewhirst Jan 16 '14 at 16:09

1 Answers1

1

Advanced Find allows to build any FetchMXL query you want, through these steps:

  • Open Advanced Find form
  • Build the query as you want it to be
  • You'll find "Download FetchXML" button on the ribbon in the query screen: click it
  • Let CRM build and hand the query for you

If Advanced Find can't give you the results you want, then you're facing a FetchXML limitation and have to turn to some other data retrieval method (OData, SDK). For example, you cannot apply a filter on fields which reside in different entities.

Alex
  • 23,004
  • 4
  • 39
  • 73
  • Im Building it for ssis so need to use FetchXML, Is it possible to reference a field value used in the Fetch as a condition to an attribute? – Richard Dewhirst Jan 16 '14 at 15:41
  • I fear not. The right side of a comparison in a FetchXML query must be a constant value, so you can't have an equivalent of `WHERE field1 = field2` – Alex Jan 16 '14 at 16:19
  • yeah I think your right, unfortunately one downside to using ssis crm integration is that im not able to pass a variable. usually it could be declared "+ var +" etc, but possibly not with a value from within the statement. hmmm well thanks anyway I have used a merge with inner joins outside of the fetch so in theory running two XMLfetch then doing a join. inefficient! – Richard Dewhirst Jan 16 '14 at 17:01
  • Running separate queries and post-processing the results is more or less the only way to accomplish many fetch-related tasks (to take another example, you can't do "WHERE ... OR ..." on different entities) – Alex Jan 17 '14 at 08:16