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

All responses (7)

Answers (1)

Verified answer

Community Power Pla... on at

Like (0)

Report
Re: 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

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)
Paulie78 8,416 Super User 2025 Season 1 on at

Like (0)

Report
Re: 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

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'
MJain 2,450 on at

Like (0)

Report

Re: 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

@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
Community Power Pla... on at

Like (0)

Report

Re: 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

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.
MJain 2,450 on at

Like (0)

Report

Re: 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

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
Community Power Pla... on at

Like (0)

Report

Re: 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

Is this what you're after?
Paulie78 8,416 Super User 2025 Season 1 on at

Like (0)

Report

Re: 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

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.