cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: GET SQL ROWS With OData Filter And/OR Query

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
Highlighted
Community Support
Community Support

Re: GET SQL ROWS With OData Filter And/OR Query

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

Highlighted
Frequent Visitor

Re: GET SQL ROWS With OData Filter And/OR Query

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
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (8,955)