web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Fetch XML filter
Microsoft Dynamics CRM (Archived)

Fetch XML filter

(0) ShareShare
ReportReport
Posted on by gman0104 821

Hi is it possible in fetchxml to do the following where i filter my link table base on a column from the parent like new_field2 below? 

<fetch>
  <entity name="new_entity1" >
    <attribute name="new_field1" />
    <attribute name="new_field2" />
    <link-entity name="new_entity2" from="new_field2" to="new_field2" alias="ach" >
      <attribute name="new_field2" />
      <attribute name="new_field3" />

        <attribute name="new_field4" />
      <filter>
        <condition attribute="new_field3" operator="eq" value= new_field2 />
      </filter>
    </link-entity>
  </entity>
</fetch>

Thanks G

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Kokulan Profile Picture
    Kokulan 18,054 on at

    Hi

    Unfortunately, you cannot use the field for the value. It will not dynamically pick up values of fields.

    Could you share the details about what is your requirement if you can, might be able to suggest a solution

  • Suggested answer
    gdas Profile Picture
    gdas 50,091 Moderator on at

    Hi,

    Take a look below article -

    community.dynamics.com/.../know-how-or-condition-between-linked-entities-or-multiple-entites-in-mscrm-fetchxml

  • gman0104 Profile Picture
    gman0104 821 on at

    Thanks Kokulan

    My primary course entity has an award date  and my linked Prize entity has multiple records that are linked to the primary entity via the course code. The linked entity has a from and to date that determines the prize amount based on the course award date being between the from and to dates

    Course Prize From Date To Date

    A1 £100, 01/04/2018 31/03/2019

    A1 £150 01/04/2019 31/03/2020

    If the award date is 05/10/2018 the  prize will be £100

    If the award date is 20/02/2020 the  prize will be £150

    How can I achieved this by filtering the prize  entity based on the  course award date

  • Suggested answer
    Kokulan Profile Picture
    Kokulan 18,054 on at

    Can there be multiple prize for a Primary Course for a given award date?

    And could you also share the details on where you will be using this fetch query in code or UI?

  • gman0104 Profile Picture
    gman0104 821 on at

    There can be multiple prizes per course but only one should be returned based on the award date being between the prize from and to dates .  The query is for coded c# function

  • Suggested answer
    Kokulan Profile Picture
    Kokulan 18,054 on at

    Hi

    Please try the following

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="new_courseprize">
    <attribute name="new_courseprizeid" />
    <attribute name="new_name" />
    <attribute name="createdon" />
    <attribute name="ke_to" />
    <attribute name="ke_prize" />
    <attribute name="ke_from" />
    <attribute name="ke_course" />
    <order attribute="new_name" descending="false" />
    <filter type="and">
    <condition attribute="ke_course" operator="eq" uiname="Test Course" uitype="new_primarycourse" value="{29DB08D7-744E-E911-A972-002248019881 - Your Primary Course ID}" />
    <condition attribute="ke_from" operator="on-or-after" value="2019-03-20 - Primary Course Award Date" />
    <condition attribute="ke_to" operator="on-or-before" value="2019-03-20 - Primary Course Award Date" />
    </filter>
    </entity>
    </fetch>

    Query Expression Version

    // Define Condition Values
    var QEnew_courseprize_ke_course = "29db08d7-744e-e911-a972-002248019881";
    var QEnew_courseprize_ke_from = "2019-03-20T00:00:00-00:00";
    var QEnew_courseprize_ke_to = "2019-03-14T00:00:00-00:00";

    // Instantiate QueryExpression QEnew_courseprize
    var QEnew_courseprize = new QueryExpression("new_courseprize");

    // Add columns to QEnew_courseprize.ColumnSet
    QEnew_courseprize.ColumnSet.AddColumns("new_courseprizeid", "new_name", "createdon", "ke_to", "ke_prize", "ke_from", "ke_course");
    QEnew_courseprize.AddOrder("new_name", OrderType.Ascending);

    // Define filter QEnew_courseprize.Criteria
    QEnew_courseprize.Criteria.AddCondition("ke_course", ConditionOperator.Equal, QEnew_courseprize_ke_course);
    QEnew_courseprize.Criteria.AddCondition("ke_from", ConditionOperator.OnOrAfter, QEnew_courseprize_ke_from);
    QEnew_courseprize.Criteria.AddCondition("ke_to", ConditionOperator.OnOrBefore, QEnew_courseprize_ke_to);

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans