Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 815

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

  • Suggested answer
    Kokulan Profile Picture
    Kokulan 18,048 on at
    RE: Fetch XML filter

    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);

  • gman0104 Profile Picture
    gman0104 815 on at
    RE: Fetch XML filter

    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,048 on at
    RE: Fetch XML filter

    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 815 on at
    RE: Fetch XML filter

    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
    gdas Profile Picture
    gdas 50,085 on at
    RE: Fetch XML filter

    Hi,

    Take a look below article -

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

  • Suggested answer
    Kokulan Profile Picture
    Kokulan 18,048 on at
    RE: Fetch XML filter

    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

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,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans