Fetch rows which do not exists

[nikviz]

Hi

I have a flow as shown below.
I get the emailid in JSON format. I use the emailid to get the contacts (Dataverse table), and then use that to get the Opportunities (dataverse table) with that contact

Is there a way by which I can get the list of emailids (from the JSON) that have the Contacts (Dataverse table) but do not have an Opportunity.  

Thanks

[lbendlin]

You already seem to be doing that

1. get list of email ids

2. for each of the email ids, get the number (count) of associated opportunities

Then filter the result where the count is zero.

[LinnZawWin]

@nikviz 

You can achieve it by querying with the FetchXML. Here's the sample FetchXML and you need to make sure the link-type='outer'. Then, replace the email values (e.g. email1@test.com) from the sample query with the list of email ids (from the JSON) by appending in the string variable.

<fetch>
  <entity name='contact'>
    <attribute name='contactid' />
    <attribute name='fullname' />
    <attribute name='emailaddress1' />
    <filter>
      <condition attribute='emailaddress1' operator='in'>
        <value>email1@test.com</value>
        <value>email2@test.com</value>
      </condition>
      <condition entityname='opportunity' attribute='opportunityid' operator='null' />
    </filter>
    <link-entity name='opportunity' from='parentcontactid' to='contactid' alias='opportunity' link-type='outer'>
      <attribute name='opportunityid' />
    </link-entity>
  </entity>
</fetch>

[nikviz]

Hi @LinnZawWin 
Thanks for this.
i tried with the above sample you gave. Challenge with "outer" is, i am unable to track which of the emailids have contacts and no opportunity associated. (Reason being, I need to fire an email with this list of contacts that do not have an opportunity)
 
in sql terms i want something like

select fullname, emailaddress1,contactid from
dbo.contact
where emailaddress1 in ('nv@nvtest.com','jp@test.com')
and contactid not in
(select parentcontactid from dbo.opportunity) ;

Based on above example you gave, I tried something like this, but not in condition in fetch xml explicitly expects GUID.

<fetch>  
  <entity name="contact">  
    <attribute name="fullname" />  
    <attribute name="emailaddress1" />  
    <attribute name="contactid" />  
    <filter type="and">  
      <condition attribute="emailaddress1" operator="in">  
        <value>nv@nvtest.com</value>  
        <value>jp@test.com</value>  
      </condition>  
      <condition attribute="contactid" operator="not-in">  
        <entity name="opportunity">  
          <attribute name="parentcontactid" />  
            </condition>  
          </filter>  
        </entity>  
      </condition>  
    </filter>  
  </entity>  
</fetch>  

[nikviz]

Hi @lbendlin 

Did you mean to try something like this? I tried this, and it did not exactly work
Filter From has value from contacts
then mapping is Contacts <does not contain> Contact (Parent Contact Id from Opportunity)

[LinnZawWin · Accepted solution]

@nikviz 

You cannot use not-in condition with another entity in FetchXml.

The outer join in my sample would allow the query to return the Contact rows without Opportunity. Then, it is filtered to get only the contacts with no opportunity associated by this condition.

<condition entityname='opportunity' attribute='opportunityid' operator='null' />

Have you tried my sample FetchXml to see if returns the data that you need? If so, can you please elaborate more about the challenge with "outer"? I understand your requirement but I just did not get the challenge of using the following FetchXml.

Btw, you can use the FetchXML Builder for XrmToolBox tool to query instead of testing it in the flow designer.

<fetch>
  <entity name='contact'>
    <attribute name='contactid' />
    <attribute name='fullname' />
    <attribute name='emailaddress1' />
    <filter>
      <condition attribute='emailaddress1' operator='in'>
        <value>nv@nvtest.com</value>
        <value>jp@test.com</value>
      </condition>
      <condition entityname='opportunity' attribute='opportunityid' operator='null' />
    </filter>
    <link-entity name='opportunity' from='parentcontactid' to='contactid' alias='opportunity' link-type='outer'>
      <attribute name='opportunityid' />
    </link-entity>
  </entity>
</fetch>

[nikviz]

Thanks @LinnZawWin 
Fetch XML tool was a real help. I was able to modify the XML with a few more requirements keeping the sample you gave as baseline.