Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Suggested answer

FetchXML Except / Anti Join

Posted on by 5

I'm looking for assistance on how to perform an Except or Anti-Join using FetchXML. For instance: 

ResourceTable: [1, 2, 3, 4, 5]

ResourceBookingTable: [3,4,3,3,5] (Filtered by time)

ResourceTable SubGrid Result: [1,2,4] 

(essentially: Don't show records in Table1 that have a record in Table2)

Intent:

To display resources that aren't booked during a specific time period in a form subgrid. The subgrid is filtered by date values on the form.

The current approach involves using a link-entity filter with FetchXML.

I can filter both Resource Table and the Booking Table, however, I haven't spotted a way to exclude ID's present in the filtered Booking Table from the Resource Table.

Categories:
  • a33ik Profile Picture
    a33ik 84,313 Super User on at
    RE: FetchXML Except / Anti Join

    Can you please provide the FetchXml that demonstrates your joins and didn't work?

  • Suggested answer
    MDev44 Profile Picture
    MDev44 5 on at
    RE: FetchXML Except / Anti Join

    Confirming I'd already read through that prior to my post... it didn't fir my scenario.

    I just solved the problem though - I wrote the SQL statement and reversed it using SQL 4 CDS on XrmToolBox.

    http://sql2fetchxml.com didn't work in this instance.

  • a33ik Profile Picture
    a33ik 84,313 Super User on at
    RE: FetchXML Except / Anti Join

    Hello,

    Check this article - it might point you the right direction - learn.microsoft.com/.../use-fetchxml-left-outer-join-query-records-not-in

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans