Skip to main content

Notifications

Community site session details

Community site session details

Session Id : qaYTlTNBhiX+Bl9M0xcNLw
Power Automate - Building Flows
Answered

Filter sharepoit list using Get Items by date

Like (0) ShareShare
ReportReport
Posted on 26 Mar 2021 20:36:49 by 17

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.

ExpYearExpMonthStartDateEndDateUPSTypeBudgetAvailable
2021March3/7/20214/3/2021On Going25,000
2021March3/7/20214/3/2021New50,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?
 

Capture.PNG

  • Verified answer
    Pstork1 Profile Picture
    66,091 Most Valuable Professional on 26 Mar 2021 at 22:39:20
    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 Profile Picture
    17 on 26 Mar 2021 at 22:16:24
    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 Profile Picture
    66,091 Most Valuable Professional on 26 Mar 2021 at 21:53:35
    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 Profile Picture
    17 on 26 Mar 2021 at 20:58:47
    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 Profile Picture
    66,091 Most Valuable Professional on 26 Mar 2021 at 20:46:08
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,771 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard