Filter Date using FetchXML

[Anonymous]

Hi All,

I am passing one parameter which is datekey into JSON. Using that datekey I want to check whether it falls in between startdatekey and enddate key. 

How can I achieve that?

For instance-

I have below record in an entity
Startdatekey EndDateKey  Target
20210112       20210114      34
So I pass datekey as 20210113 then it should the above row as the passed datekey falls in the above date range.

Please help
@OliverRodrigues 
@OOlashyn 

[OliverRodrigues]

Hi, how is your datekey field represented in your Dataverse? is it a string?

If it is, you can't really compare using between/before/after. You are just comparing texts.

Can you convert that to a datetime field? 

If you can't, I think the only way would be retrieving your data via Liquid and loop through the data, in your loop you need to convert via Liquid and then compare with the current date. Based on that you can ignore and go to the next record.

This might have performance impact as you would have to retrieve all the data before actually filtering, but I can't think of any other solution.

[Anonymous]

Hi,
I the datekey is a text field. If I use it as a date field then it is also not working. I used below condition-

<condition attribute='cr579_startdate' operator='ge' value='{{pdatekey}}' />

<condition attribute='cr579_enddate' operator='le' value='{{pdatekey}}' />

And it throws an error saying-

Liquid error: Sequence contains no matching element

Though my all key fields are text fields, below condition worked fine-

<condition attribute='cr579_date' operator='ge' value='{{pstartkey}}' />

<condition attribute='cr579_date' operator='le' value='{{pendkey}}' />

[OliverRodrigues]

Hi, yes it won't work because the field is a string.

Does the field have to be a string? Is there any reason it is not a Date field?

[Anonymous]

I checked that field using the data type as date field but it is not working.

[justinburch]

Hi @Anonymous,

So cr579_startdate, cr579_enddate, and cr579_date are all Text fields, and when you use just cr579_date the query is fine but as soon as you use start&end it breaks? Is the value for pdatekey the same as the ones for start&end? And does this happen for both valid & invalid inputs?

It seems the Liquid is throwing a .NET sequence error, so it doesn't like something about the results.

FYI, @OliverRodrigues: I tested this query after making content snippets as 1001 and 1005 and it works 😅

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="adx_contentsnippet">
    <attribute name="adx_contentsnippetid" />
    <attribute name="adx_name" />
    <attribute name="createdon" />
    <order attribute="adx_name" descending="false" />
    <filter type="and">
      <condition attribute="adx_name" operator="ge" value="1000" />
      <condition attribute="adx_name" operator="le" value="1004" />
    </filter>
  </entity>
</fetch>

[OliverRodrigues]

@justinburch OMG.. I am scared to know greater then/less then works with string. D365 is probably trying to parse as numeric or something. I might play around later via SDK.

[Anonymous]

Hi @justinburch and @OliverRodrigues,

All fields here are text field -

The below syntax is working perfectly fine and giving me desired result (if you observe I am passing two parameter i.e. pstartdatekey and  penddatekey, and checking it into a single column i.e. cr579_startdatekey -

<condition attribute='cr579_startdatekey' operator='ge' value='{{pstartdatekey}}' />

<condition attribute='cr579_startdatekey' operator='le' value='{{penddatekey}}' />

But, if I use below syntax. it is not working (if you observe I am passing a single parameter i.e. pdatekey and checking it into two different column i.e. cr579_startdatekey and  cr579_enddatekey )

<condition attribute='cr579_startdatekey' operator='ge' value='{{pdatekey}}' />

<condition attribute='cr579_enddatekey' operator='le' value='{{pdatekey}}' />

What I want to achieve is I am passing the date and want to check if that falls in the date ranges.
Startdatekey EndDateKey  Target
20210112       20210114      34
So I pass datekey as 20210113 then it should give me the above row as the passed datekey falls in the above date range.

Any other solution you suggest?

[OliverRodrigues]

Hi, can you share your code where you assign the value into pdatekey variable?

[Anonymous]

Hi @OliverRodrigues,

please find the below code which is not working-
{% assign pdate = request.params.date %}

{% assign pdatekey = request.params.datekey %}

{% assign pstartdate = request.params.startdate %}

{% assign penddate = request.params.enddate %}

{% fetchxml my_query %}

    <fetch version="1.0" output-format='xml-platform' mapping='logical' distinct='false'>

        <entity name="cr579_tmf_planview">

            <attribute name="cr579_startdatekey" />

            <attribute name="cr579_target" />

              <attribute name="cr579_enddatekey" />

                <attribute name="cr579_startdate" />

                     <attribute name="cr579_enddate" />

            <order attribute="cr579_startdatekey" descending="false" />

            <filter type='and'>               

            <condition attribute='cr579_target' operator='nq' value='{{pdate}}' />

            <condition attribute='cr579_startdatekey' operator='ge' value='{{pdatekey}}' />

            <condition attribute='cr579_enddatekey' operator='le' value='{{pdatekey}}' />

            </filter>

      </entity>

    </fetch>

   {% endfetchxml %}

{

   "TMF_FilterCrushingExecutionTargetJSON" : [

    {% for item in my_query.results.entities %}

      {       

        "StartDateKey": "{{ item.cr579_startdate}}",

          "EndDateKey": "{{ item.cr579_enddatekey}}",

        "Target": "{{ item.cr579_target}}"                  

      }{% unless forloop.last %},{% endunless %}

     {% endfor %}

   ]

}