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
You can use the LastXDays and NextXDays operators. For example:
Share
Report
All responses (
Answers (
<?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>
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>
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.
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
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.
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>'
#1
André Arnaud de Cal...
292,489
Super User 2025 Season 1
#2
Martin Dráb
231,305
Most Valuable Professional
#3
nmaenpaa
101,156
