Filter records that don't exist in related table

[bfausti]

I am trying to filter records in one table that don't exist in the other ie only show new records. How is this best achieved?

Note this is to be later used with a Flow.

[EricRegnier · Accepted solution]

Hi @bfausti,

The easiest is to do a left join like in SQL with FetchXml which you can run in Power Automate. Here's an example of the FetchXml between account and contacts, where it will retrieve all accounts where there's no account (aka customer) set:

<fetch>
  <entity name="account" >
    <attribute name="accountid" />
    <link-entity name="contact" from="parentcustomerid" to="accountid" link-type="outer" >
      <filter>
        <condition attribute="contactid" operator="null" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Tip: you can use FetchXml Builder tool to build your FetchXml if you're not familiar with it: https://www.xrmtoolbox.com/plugins/Cinteros.Xrm.FetchXmlBuilder/ 

In Power Automate you can set your FetchXml here in the List Rows action:

Hope this helps!

[bfausti]

Thank you very much for your help Eric,

I am trying to get the xml right, please help, here is what I have so far:

<fetch name="gbca_project" from="gbca_legacyid" to="gbca_projectid" link-type="outer" >
  <entity name="gbca_project" >
    <attribute name="gbca_legacyid" />
    <link-entity name="bapps_ratingsadamm" from="gbca_projectid" to="gbca_legacyid" link-type="outer" >
      <filter>
        <condition attribute="gbca_projectid" operator="null" />
      </filter>
    </link-entity>
  </entity>
</fetch>

gbca_project contains the new records ie. I need to insert records from gbca_project into bapps_ratingsadamm based on the fields gbca_project.gbca_legacyid and bapps_ratingsadamm.gbca_projectid

However, when I run this, all records are returned and it should only return one. Can you see the error in my XML?

[bfausti · Accepted solution]

I was able to work it out, here was my final fetchXML

<fetch mapping="logical" >
  <entity name="gbca_project" >
    <attribute name="gbca_legacyid" />
    <link-entity name="bapps_ratingsadamm" from="gbca_projectid" to="gbca_legacyid" link-type="outer" />
    <filter type="and" >
      <condition entityname="bapps_ratingsadamm" attribute="gbca_projectid" operator="null" />
    </filter>
  </entity>
</fetch>

Thanks again for your help @EricRegnier