cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GaganMehrok
Level: Powered On

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

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.
2 REPLIES 2
Community Support Team
Community Support Team

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.
DenisStark
Level: Powered On

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

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 41 members 4,530 guests
Recent signins:
Please welcome our newest community members: