cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter Date using FetchXML

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 

16 REPLIES 16
OliverRodrigues
Super User
Super User

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.




If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Power Apps Portals Super User


Oliver Rodrigues


 

Anonymous
Not applicable

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
Super User
Super User

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?




If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Power Apps Portals Super User


Oliver Rodrigues


 

Anonymous
Not applicable

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

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
Super User
Super User

@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.




If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Power Apps Portals Super User


Oliver Rodrigues


 

Anonymous
Not applicable

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
Super User
Super User

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




If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Power Apps Portals Super User


Oliver Rodrigues


 

Anonymous
Not applicable


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 %}
   ]
}

Helpful resources

Announcements
May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel News & Announcements 768460.png

What's New in the Community?

Check out the latest News & Events in the community!

MPP IDEAS updated 768x460.png

Ideas

Discover ideas and concepts from users like you for how to use Power Pages and take your work to the next level.

Top Solution Authors
Users online (1,203)