cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bfausti
Advocate II
Advocate II

Filter records that don't exist in related table

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
EricRegnier
Super User
Super User

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:

EricRegnier_0-1631504789979.png

Hope this helps!

View solution in original post

bfausti
Advocate II
Advocate II

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 

View solution in original post

3 REPLIES 3
EricRegnier
Super User
Super User

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:

EricRegnier_0-1631504789979.png

Hope this helps!

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
Advocate II
Advocate II

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 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (4,608)