cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GaganMehrok
Helper I
Helper I

GET SQL ROWS With OData Filter And/OR Query

Hi All, 

 

I am trying to build up a flow using And/OR within Odata filter query to narrow down data coming in from source. I am trying below combination but it going into bad gateway response from server. I know i not writing it in correct format but it would be great if someone can give me some guidance on this. Thanks. 

 

Capture.PNG

 

Expression read as.

formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:sszzz')
1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

HI @GaganMehrok,

 

Could you please show a bit more about the Task_due_date column in your SQL table?

Could you please show a full screenshot of your flow's configuration?

 

I have created a SQL table on my side with following code:

Create table dbo.TaskLists1(
  TaskId int not null identity(1,1),
  Task_due_date datetime not null,
  Task_current_state varchar(20) not null,
  Creator varchar(25) not null
)

The Task_due_date column is a datetime type column, the data structure of my SQL table as below:19.JPG

 

 

I think there is something wrong with the OData query that you provided within Filter Query field of "Get rows" action. When working with date value using OData query within Filter Query field of "Get rows" action, we should get year, month and day separately, please take a try with the following article as a reference:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

I have made a test on my side and please take a try with the following workaround:20.JPG

Within Filter Query field, type the following formula:

year(Task_due_date) le utcNow(...)​ and month(Task_due_date) le utcNow(...) and day(Task_due_date) lt utcNow(...)​ and Task_current_state eq 'Build' or Task_current_state eq 'Peer Review'

The first utcNow() expression read as:

utcNow('yyyy')

The second utcNow() expression read as:

utcNow('MM')

The third utcNow() expression read as:

utcNow('dd')

 

The flow works successfully as below:21.JPG

 

 

Please check and see if the following article would help you in understanding the above solution that I provided:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Filter-SQL-Date-field/m-p/59778/highligh...

 

 

Best regards,

Kris

 

 

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xida-msft
Community Support
Community Support

HI @GaganMehrok,

 

Could you please show a bit more about the Task_due_date column in your SQL table?

Could you please show a full screenshot of your flow's configuration?

 

I have created a SQL table on my side with following code:

Create table dbo.TaskLists1(
  TaskId int not null identity(1,1),
  Task_due_date datetime not null,
  Task_current_state varchar(20) not null,
  Creator varchar(25) not null
)

The Task_due_date column is a datetime type column, the data structure of my SQL table as below:19.JPG

 

 

I think there is something wrong with the OData query that you provided within Filter Query field of "Get rows" action. When working with date value using OData query within Filter Query field of "Get rows" action, we should get year, month and day separately, please take a try with the following article as a reference:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

I have made a test on my side and please take a try with the following workaround:20.JPG

Within Filter Query field, type the following formula:

year(Task_due_date) le utcNow(...)​ and month(Task_due_date) le utcNow(...) and day(Task_due_date) lt utcNow(...)​ and Task_current_state eq 'Build' or Task_current_state eq 'Peer Review'

The first utcNow() expression read as:

utcNow('yyyy')

The second utcNow() expression read as:

utcNow('MM')

The third utcNow() expression read as:

utcNow('dd')

 

The flow works successfully as below:21.JPG

 

 

Please check and see if the following article would help you in understanding the above solution that I provided:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Filter-SQL-Date-field/m-p/59778/highligh...

 

 

Best regards,

Kris

 

 

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi, are you sure the query is ok ? I am not getting correct results this way.

In example, I am trying to get records with older dates than a month ago = 2018-09-24:

- I get the record when the date is 2018-04-15 --> all numbers are lt

- I do not get correct record when one of the numbers (month or day) is bigger (2017-10-15 or 2018-07-25)

 

Maybe I did something wrong

 

Thanks for your response.

 

/D

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,630)