FetchXML Between

(0) Share

Report

Posted on by Community Member

I need to retrieve some records in crm where today is between the records from and to date fields

I have used the following and it works, but it needs to be dynamic so it picks up whatever the value is in to and form.

<filter type="and">

<condition attribute="msdyn_from" value="2018-08-01" operator="on-or-after"/>

<condition attribute="msdyn_to" value="2018-08-31" operator="on-or-before"/>

</filter>

*This post is locked for comments

All responses (6)

Answers (0)

David Jennaway 14,065 on at

Like (0)

Report
RE: FetchXML Between

You can use the LastXDays and NextXDays operators. For example:

<?xml version="1.0"?> <fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0"> <entity name="bookableresource"> <link-entity name="msdyn_projectteam" alias="ah" link-type="inner" to="bookableresourceid" from="msdyn_bookableresourceid"> <filter type="and"> <filter type="and"> <condition attribute="msdyn_from" operator="last-x-days" value="999"/> <condition attribute="msdyn_to" operator="next-x-days" value="999"/> </filter> </filter> <link-entity name="msdyn_project" alias="ai" link-type="inner" to="msdyn_project" from="msdyn_projectid"/> </link-entity> </entity> </fetch>
Community Member on at

Like (0)

Report
RE: FetchXML Between

Thanks, however, I'm writing this in fetch XML so need an all fetch XML solution.

How do I write the above into a fetch XML statement I can use online in a report sorry.

I currently have:

<?xml version="1.0"?> <fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0"> <entity name="bookableresource"> <link-entity name="msdyn_projectteam" alias="ah" link-type="inner" to="bookableresourceid" from="msdyn_bookableresourceid"> <filter type="and"> <filter type="and"> <condition attribute="msdyn_from" operator="on-or-after" value="today"/> <condition attribute="msdyn_to" operator="on-or-before" value="today"/> </filter> </filter> <link-entity name="msdyn_project" alias="ai" link-type="inner" to="msdyn_project" from="msdyn_projectid"/> </link-entity> </entity> </fetch>
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

RE: FetchXML Between

Hi Graeme,

Can you try something like this? Basically passing the current date as paramter but changing the operator to include the fromdate and todate.

==============

var currentDate = DateTime.Now.ToString("yyyy-MM-dd");

Console.WriteLine(currentDate);

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

<entity name='account'>

<filter>

<condition attribute='new_fromdate' operator='on-or-before' value='{0}' />

<condition attribute='new_todate' operator='on-or-after' value='{0}' />

</filter>

</entity>

</fetch>";

fetchXml = string.Format(fetchXml, currentDate);

var results = service.RetrieveMultiple(new FetchExpression(fetchXml));

Console.WriteLine(results.Entities.Count());

====================

Hope this helps.
Suggested answer

Pawar Pravin 5,237 on at

Like (0)

Report

RE: FetchXML Between

Hi Graeme,

I would like to suggest you to use query expression rather than fetch expression. There are some limitations using fetchxml as like you cant add in between for fetch expression.

In place of between you can use OnOrBefore and OnOrAfter.

Please do refer following code.

QueryExpression AppointmentQuery = new QueryExpression

{

EntityName = "appointment",

ColumnSet = new ColumnSet(true)

};

FilterExpression filter = new FilterExpression(LogicalOperator.Or);

FilterExpression filter7 = new FilterExpression(LogicalOperator.And);

filter7.Conditions.Add(new ConditionExpression("scheduledstart", ConditionOperator.OnOrBefore, _startDate));

filter7.Conditions.Add(new ConditionExpression("scheduledend", ConditionOperator.OnOrAfter, _startDate));

filter.AddFilter(filter7);

AppointmentQuery.Criteria = filter;

EntityCollection Appointments = service.RetrieveMultiple(AppointmentQuery);

Hope this help you... !!!

Regards,

Pravin
Community Member on at

Like (0)

Report

RE: FetchXML Between

Thanks Arun, however I will be retriving multiple records where today is between the two dates on them, so don't think i can use a paramenter to achieve this as i have no idea what dates will be in the two date fields.
Suggested answer

Arun Vinoth 11,615 Moderator on at

Like (0)

Report
RE: FetchXML Between

You can parameterize those values & pass it.

Something like this in SSRS reports:

<condition attribute="msdyn_from" operator="on-or-after" value="@date"></condition>

Something like this in C# or JS code:

'<condition attribute="msdyn_from" operator="on-or-after" value="' + paramDate + '"></condition>'