Unanswered

How can I filter a list extracted from the Common Data Service to only show records with a date in one of two columns that is coming up in the next 3 months

(0) Share

Report

Posted on by Community Power Pla...

Hi everyone,

I am trying to filter my list of records so that I only retrieve records that either have an EndDate or a BreakDate that falls within the next 3 months.

Please can someone help.

Thanks!!!

@Paulie78

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Paulie78 8,422 Moderator on at

Like (0)

Report

Can you post a sample of the json output from list records (just to get the right field names etc) from list records? Does not have to be the whole thing, but 1 record would be good.

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

Like (0)

Report

Is this what you're after?

Was this reply helpful? Yes No
MJain 2,450 on at

Like (0)

Report

Hi @Anonymous ,

You can do advanced find in D365 and download the fetch xml and use the same in List Records action. There is Fetch XML query parameter in the list records action .

Thanks

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

Like (0)

Report

Unfortunately I can't use the CDS (Current Environment) in the environment it's interacting with as I don't have the right permissions. So I have been trying to find a work around, I was thinking there must be a way to filter the data with an ODATA query.

Was this reply helpful? Yes No
MJain 2,450 on at

Like (0)

Report

@Anonymous ,

Unfortunately if you do not have permissions on entity no matter what whichever route you use you will get unauthorized error or not allowed error while retrieving data.

Thanks

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
So are you able to get the data without any filters? (suggests that you should be able to access it with a filter, but I don't know much about CDS). Just try and odata query and see what happens, for example:

EndDate ge '2020-11-18' and EndDate le '2021-02-18'

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report
I managed to fix this by using the following OData filter query:

wt_enddate le addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),91) and wt_enddate ge addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),0)

Was this reply helpful? Yes No