Filter sharepoit list using Get Items by date

(0) Share

Report

Posted on 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?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
RoyCoello 17 on at

Like (0)

Report

UPSType is a string! I deleted the UPSType filter but the date filter is still giving error.

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report
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(...)'

Was this reply helpful? Yes No
RoyCoello 17 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No