[PowerAutomate] Odata Filter Queries

[Scenario]

As part of automation of new joiner process, creating user accounts in various systems, user access of factory ERP system should be granted two days before the start date.

 

Joiner list is available and need to fetch the data with conditions below.

- Start date is less or equal to [today - two days]

- Joiner automation status is "Processed"

- System Access field should not be empty

- FinOpsTicketRrv field should be checked(boolean TRUE)

 

[Requirements]

- Microsoft Power Automate license

- Related SharePoint list access

 

[OS] 

N/A

 

[Development Language]

Odata query

 

[IDE]

Web browser

 

[Setting]

- Go to Power Automate site and log in with an account which has Power Automate license

- Create a new flow, scheduled cloud flow

 

- Click edit then add a step, SharePoint --> Get Items

 

- Enable filter query option

 

 

[Code]

(FinOpsTicketRrv eq 1) 
and (StartDate le '@{formatDateTime(addDays(utcNow(), -2) , 'yyyy/MM/dd')}')  
and (Status eq 'Processed') 
and (SystemAccess ne null)

 

 

[Test]

Items under the conditions were fetched properly.

 

 

[Lesson Learned]

- Column name won't be chaged after column creation

- Spaces between column name are eleminated in oData filter query

- Query can be nested with "and" and "or" conditions
- Boolean value can be filtered with 1(true) and 0(false)

- String type value should be covered with single quote(')