Power Automate - Building Flows

Answered

Filter sharepoit list using Get Items by date

Like (0) Share

Report

Posted on 26 Mar 2021 20:36:49 by RoyCoello

Hi All

I have an issue trying to filter a sharepoint list using Get Items.

The scenario is the following:

I have a sharepoint list with a monthly budget. The list defines the date range with a StartDate and EndDate column and a BudgetType Column. There are two budget types, so each date range is repeated two times.
The flow starts when an item is created. Then I need to filter a list trying to get the correct budget using the date it started ('run date' greater or equal than StartDate AND less or equal than EndDate AND UPSType = 'InputItemUPSType')

Example. Dates are defined as Dates, no strings.

ExpYear	ExpMonth	StartDate	EndDate	UPSType	BudgetAvailable
2021	March	3/7/2021	4/3/2021	On Going	25,000
2021	March	3/7/2021	4/3/2021	New	50,000

So when the flow runs, I need to save the date it started and go to the list to try to get the correct budget based in which date range it falls in and which type it is.

See below how I am filtering the list. The expression I am using is

formatDateTime(utcNow(),'yyyy-MM-dd')

But I receive this Error.

The expression "2021-03-26 ge StartDate AND 2021-03-26 le EndDate AND UPSType eq {"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"On Going"}" is not valid. Creating query failed.

I truly do not know how I fail. If I just filter StartDate ge '2021-03-26' it doesnt throw an error but thats not what I need.

How can I fix this?

All responses (5)

Answers (1)

Verified answer

Pstork1 66,424 Most Valuable Professional on 26 Mar 2021 at 22:39:20

Like (0)

Report

Re: Filter sharepoit list using Get Items by date

You don't have single quotes around both dates. I don't remember at the moment whether they are required for date values or not. But I know its one or the other. You have startdate with single quotes and enddate without. If adding single quotes to the enddate target date doesn't work try removing them from both.
RoyCoello 17 on 26 Mar 2021 at 22:16:24

Like (0)

Report

Re: Filter sharepoit list using Get Items by date

Same error. The expression "StartDate ge '2021-03-26' AND EndDate le 2021-03-26" is not valid. Creating query failed.
clientRequestId: a4f0dbc0-22b1-48db-8323-90e0a4bb30be
serviceRequestId: a4f0dbc0-22b1-48db-8323-90e0a4bb30be
Pstork1 66,424 Most Valuable Professional on 26 Mar 2021 at 21:53:35

Like (0)

Report
Re: Filter sharepoit list using Get Items by date

Your data filters should be turned around and I think you need to put the target date inside single quotes.

StartDate ge 'formatDateTime(...)' and EndDate le 'formatDateTime(...)'
RoyCoello 17 on 26 Mar 2021 at 20:58:47

Like (0)

Report

Re: Filter sharepoit list using Get Items by date

UPSType is a string! I deleted the UPSType filter but the date filter is still giving error.
Pstork1 66,424 Most Valuable Professional on 26 Mar 2021 at 20:46:08

Like (0)

Report

Re: Filter sharepoit list using Get Items by date

I assume UPSType is a choice column right? If so you can't use a complex object in a Filter Query. You've got two options.

1) Do the Filter Query for startDate and EndDate and then do a filter Array to get the specific UPSType.

2) Look at the output of the GetItems. You'll find that the Choice column also reports an ID in the Trigger data that is a simple INT and not a complex object. You can filter on that integer in the Odata query. That simplifies the ODATA filter to one action, but complicates it because you have to figure what the appropriate number is.

I recommend #1.

Community site session details

Filter sharepoit list using Get Items by date

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Filter sharepoit list using Get Items by date

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates