Unanswered

Filter Date using FetchXML

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Power Apps portals

I have the same question (0)

All responses (16)

Answers (0)

Sort by

oliver.rodrigues 9,475 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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}}' />

Was this reply helpful? Yes No
oliver.rodrigues 9,475 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
justinburch Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
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>

Was this reply helpful? Yes No
oliver.rodrigues 9,475 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
oliver.rodrigues 9,475 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
justinburch Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous,
Are you sure the code is breaking at the query?
Only thing I see there is <condition attribute='cr579_target' operator='nq' value='{{pdate}}' />
Your returning results are using mixed values:
"StartDateKey": "{{ item.cr579_startdate}}",
"EndDateKey": "{{ item.cr579_enddatekey}}",

Assuming none of those are the problems because you said you just pass pdatekey into both fields and it breaks after using pstartdatekey and penddatekey, does the same thing happen if if you pass pstartdatekey for both?
Prove pstartdatekey and penddatekey work as expected
Change penddatekey to pstartdatekey
Does it break?
If 3 == true, then I would assume there is an error in the Liquid's server-side code. Else, there's an issue with pdatekey as it's being passed or stored vs. pstartdatekey.

Was this reply helpful? Yes No