Microsoft Dynamics CRM (Archived)

Fetch XML query doesn't fetch the respective records with "Outer Join" condition

(0) Share

Report

Posted on by Amiy

340

Hi ,

I need to fetch the Appointment records as per below condition :

1. Status = Scheduled

2. Scheduled end date is "Next 3 weeks"

Please find below fetch xml :

But this fetch brings all Appointment records irrespective of the fetch condition , and filter criteria mentioned.

I know , i can fetch the records by creating a simple view or if i remove "Outer" join this would bring respective records , but need to know the reason that why above query doesn't fetches the records as per filter condition and why it is not possible though "Outer join" ?

*This post is locked for comments

All responses (5)

Answers (0)

Amiy 340 on at

Like (0)

Report

RE: Fetch XML query doesn't fetch the respective records with "Outer Join" condition

Need to create one chart on Appointment, which would weeks wise (grouping on week) fetch Appointment records(Open/Scheduled/Completed) on basis of status for below condition :

a. Status should only be considered Open when : Fetch all Appointment records where Status=Open/Scheduled for last 3 weeks on scheduledend date

b. Status should only be considered Scheduled when : Fetch all Appointment records where Status=Scheduled for Next 3 weeks on scheduledend date

c. Status should only be considered Completed when : Fetch Appointment Completed for last 3 weeks and Next 3 weeks on scheduledend date

I configured below fetch Xml , but not working . It is bringing all appointment records irrespective of conditions :

<visualization>

<visualizationid>{6EB047C2-3B5B-E711-8106-E0071B650251}</visualizationid>

<name>Appointment by Status</name>

<primaryentitytypecode>appointment</primaryentitytypecode>

<datadescription>

<datadefinition>

<fetchcollection>

<fetch mapping="logical" aggregate="true">

<entity name="appointment">

<attribute groupby="true" alias="Weekgroup" dategrouping="week" name="scheduledend" />

<link-entity name="appointment" from="activityid" to="activityid" alias="aw" link-type="outer">

<attribute alias="Open" name="subject" aggregate="count" />

<filter type="and">

<condition attribute="statecode" operator="in">

<value>0</value>

<value>3</value>

</condition>

<condition attribute="scheduledend" operator="last-x-weeks" value="3" />

</filter>

</link-entity>

<link-entity name="appointment" from="activityid" to="activityid" alias="ar" link-type="outer">

<attribute alias="Scheduled" name="subject" aggregate="count" />

<filter type="and">

<condition attribute="statecode" operator="eq" value="3" />

<condition attribute="scheduledend" operator="next-x-weeks" value="3" />

</filter>

</link-entity>

<link-entity name="appointment" from="activityid" to="activityid" alias="au" link-type="outer">

<attribute alias="Completed" name="subject" aggregate="count" />

<filter type="and">

<condition attribute="statecode" operator="eq" value="1" />

<filter type="or">

<condition attribute="scheduledend" operator="last-x-weeks" value="3" />

<condition attribute="scheduledend" operator="next-x-weeks" value="3" />

</filter>

</filter>

</link-entity>

</entity>

</fetch>

</fetchcollection>

<categorycollection>

<category alias="Weekgroup">

<measurecollection>

<measure alias="Open" />

</measurecollection>

<measurecollection>

<measure alias="Scheduled" />

</measurecollection>

<measurecollection>

<measure alias="Completed" />

</measurecollection>

</category>

</categorycollection>

</datadefinition>

</datadescription>

<presentationdescription>

<Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">

<Series>

<Series ChartType="Stackedcolumn" LegendText="1-Open" Color="LightSteelBlue" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

<Series ChartType="Stackedcolumn" LegendText="2-Scheduled" Color="CornflowerBlue" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

<Series ChartType="Stackedcolumn" LegendText="3-Completed" Color="RoyalBlue" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

</Series>

<ChartAreas>

<ChartArea BorderColor="White" BorderDashStyle="Solid">

<AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

<MajorGrid LineColor="239, 242, 246" />

<MajorTickMark LineColor="165, 172, 181" />

<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

</AxisY>

<AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

<MajorTickMark LineColor="165, 172, 181" />

<MajorGrid LineColor="Transparent" />

<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

</AxisX>

</ChartArea>

</ChartAreas>

<Titles>

<Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />

</Titles>

<Legends>

<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />

</Legends>

</Chart>

</presentationdescription>

<isdefault>false</isdefault>

</visualization>
Amiy 340 on at

Like (0)

Report

RE: Fetch XML query doesn't fetch the respective records with "Outer Join" condition

I knew this question would come up .

Need to create one chart on Appointment, which would weeks wise (grouping on week) fetch Appointment records(Open/Scheduled/Completed) on basis of status for below condition :

a. Status should only be considered Open when : Fetch all Appointment records where Status=Open/Scheduled for last 3 weeks on scheduledend date

b. Status should only be considered Scheduled when : Fetch all Appointment records where Status=Scheduled for Next 3 weeks on scheduledend date

c. Status should only be considered Completed when : Fetch Appointment Completed for last 3 weeks and Next 3 weeks on scheduledend date

I configured below fetch Xml , but not working . It is bringing all appointment records irrespective of conditions :

<visualization>

<visualizationid>{6EB047C2-3B5B-E711-8106-E0071B650251}</visualizationid>

<name>Appointment by Status</name>

<primaryentitytypecode>appointment</primaryentitytypecode>

<datadescription>

<datadefinition>

<fetchcollection>

<fetch mapping="logical" aggregate="true">

<entity name="appointment">

<attribute groupby="true" alias="Weekgroup" dategrouping="week" name="scheduledend" />

<link-entity name="appointment" from="activityid" to="activityid" alias="aw" link-type="outer">

<attribute alias="Open" name="subject" aggregate="count" />

<filter type="and">

<condition attribute="statecode" operator="in">

<value>0</value>

<value>3</value>

</condition>

<condition attribute="scheduledend" operator="last-x-weeks" value="3" />

</filter>

</link-entity>

<link-entity name="appointment" from="activityid" to="activityid" alias="ar" link-type="outer">

<attribute alias="Scheduled" name="subject" aggregate="count" />

<filter type="and">

<condition attribute="statecode" operator="eq" value="3" />

<condition attribute="scheduledend" operator="next-x-weeks" value="3" />

</filter>

</link-entity>

<link-entity name="appointment" from="activityid" to="activityid" alias="au" link-type="outer">

<attribute alias="Completed" name="subject" aggregate="count" />

<filter type="and">

<condition attribute="statecode" operator="eq" value="1" />

<filter type="or">

<condition attribute="scheduledend" operator="last-x-weeks" value="3" />

<condition attribute="scheduledend" operator="next-x-weeks" value="3" />

</filter>

</filter>

</link-entity>

</entity>

</fetch>

</fetchcollection>

<categorycollection>

<category alias="Weekgroup">

<measurecollection>

<measure alias="Open" />

</measurecollection>

<measurecollection>

<measure alias="Scheduled" />

</measurecollection>

<measurecollection>

<measure alias="Completed" />

</measurecollection>

</category>

</categorycollection>

</datadefinition>

</datadescription>

<presentationdescription>

<Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">

<Series>

<Series ChartType="Stackedcolumn" LegendText="1-Open" Color="LightSteelBlue" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

<Series ChartType="Stackedcolumn" LegendText="2-Scheduled" Color="CornflowerBlue" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

<Series ChartType="Stackedcolumn" LegendText="3-Completed" Color="RoyalBlue" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

</Series>

<ChartAreas>

<ChartArea BorderColor="White" BorderDashStyle="Solid">

<AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

<MajorGrid LineColor="239, 242, 246" />

<MajorTickMark LineColor="165, 172, 181" />

<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

</AxisY>

<AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

<MajorTickMark LineColor="165, 172, 181" />

<MajorGrid LineColor="Transparent" />

<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

</AxisX>

</ChartArea>

</ChartAreas>

<Titles>

<Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />

</Titles>

<Legends>

<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />

</Legends>

</Chart>

</presentationdescription>

<isdefault>false</isdefault>

</visualization>
Suggested answer

Community Member on at

Like (0)

Report

RE: Fetch XML query doesn't fetch the respective records with "Outer Join" condition

You can verify the following link and you can test your fetchxml online
http://msxrmtools.com/fetchxml
Verify the following link also http://blog.sonomapartners.com/2016/02/fetchxml-left-outer-joins-with-multiple-on-clauses.html
Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

RE: Fetch XML query doesn't fetch the respective records with "Outer Join" condition

hi

When it can be achieved by querying single entity (appointment), why do you want to do with outer join with the same entity?
Suggested answer

Nithya Gopinath 17,076 on at

Like (0)

Report
RE: Fetch XML query doesn't fetch the respective records with "Outer Join" condition

Hi,

Please try the fetch XML code below.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="appointment"> <attribute name="subject" /> <attribute name="statecode" /> <attribute name="scheduledstart" /> <attribute name="scheduledend" /> <attribute name="createdby" /> <attribute name="regardingobjectid" /> <attribute name="activityid" /> <attribute name="instancetypecode" /> <order attribute="subject" descending="false" /> <filter type="and"> <condition attribute="statecode" operator="eq" value="3" /> <condition attribute="scheduledstart" operator="next-x-weeks" value="3" /> </filter> </entity> </fetch>

Hope this helps.