Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Fetchxml - Parent with...
Microsoft Dynamics CRM (Archived)

Fetchxml - Parent without child and parent with specific child only

(0) ShareShare
ReportReport
Posted on by RaviM 675

Hello,

We have 2 entities. Entity1 and Entity2. Entity1 have 1:N relationship with Entity2. I need to filter lookup field of Entity1 on Entity2 form. So i have written fetchxml and added as prefilter on the lookup field.

I want that lookup should show only those records of Entity1 :

1) Which don't have any associated Entity2 records.

2) If any Entity2  record is associated and that record have specific value in one custom field.

I have created outer join with linked entity and used OR condition. That works fine if only one record is associated with Entity1. But let say, if 2 Entity2 records are associated with Entity1. One is with specific value in custom field and other with not. Then my requirement is, that record should not display in lookup fields. Currently it is showing.

*This post is locked for comments

  • Suggested answer
    Datadvancer Profile Picture
    Datadvancer 353 on at
    RE: Fetchxml - Parent without child and parent with specific child only

    Hi Ravi,

    You can use FetchXML Builder to construct and filter your XML and view.

  • RaviM Profile Picture
    RaviM 675 on at
    RE: Fetchxml - Parent without child and parent with specific child only

    If I will use filter type="and", then it will not show Entity1 record if only one record of Entity2 is associated with it and that is having custom field value="Deleted". I need those records also.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Fetchxml - Parent without child and parent with specific child only

    Use filter type='and'

  • RaviM Profile Picture
    RaviM 675 on at
    RE: Fetchxml - Parent without child and parent with specific child only

    Hi Raj,

    Following is my fetchxml. I have used "addCustomView" in javascript. As I have mentioned, this works fine if only one record is associated with Entity1. But let say, if 2 Entity2 records are associated with Entity1. One is with specific value(Deleted) in custom field and other with not. Then my requirement is, that record should not display in lookup fields. Currently it is showing.

    <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true' >

<entity name='entity1' >

<attribute name='entity1_primaryid' />

<link-entity name='entity2' from='entity2_relationshipfield' to='entity1_primaryid' link-type='outer' >

<filter type='or' >

<condition entityname='entity2' attribute='entity2_relationshipfield' operator='null' />

<condition entityname='entity2' attribute='entity2_custom' operator='eq' value='Deleted' />

</filter>

</link-entity>

</entity>

</fetch>
  • Suggested answer
    Nadeeja Bomiriya Profile Picture
    Nadeeja Bomiriya 6,804 on at
    RE: Fetchxml - Parent without child and parent with specific child only

    Hi RaviM,

    Please see below article which explains how to write FetchXML left outer join with "not in".

    https://msdn.microsoft.com/en-us/library/dn531006.aspx

    You could try writing the query in T-SQL and try to convert it using below site.

    http://www.sql2fetchxml.com/

    Note: FetchXML doesn't support all T-SQL queries

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Fetchxml - Parent without child and parent with specific child only

    Share your code here

  • Suggested answer
    Rajkumar Rajaraman Profile Picture
    Rajkumar Rajaraman 18,108 on at
    RE: Fetchxml - Parent without child and parent with specific child only

    Hi Ravi,

    You can use addPreSearch.

    Refer the following link:

    blogs.msdn.microsoft.com/.../addpresearch-and-addcustomfilter-in-crm-2013

    Hope this helps.

    Regards,

    Rajkumar Rajaraman

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans