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

ODATA filter query to filter SharePoint list items in MS flow

Hi,

1. Does ODATA filter query works only on default SP list column(Title)? Why it doesnot work or filter items based on custom columns?

2. I have SP list and I am filtering it based on input from previous step. I am trying to update SP record if there is match against input value in SP list and wanted to create new record if there is no existing entry. However, creating new entry is not working.

What will be output of ODATA filter query if record is not available in the SP list? Can query output be used to create new record?

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User II
Dual Super User II

Re: ODATA filter query to filter SharePoint list items in MS flow

Make sure that you are using the Inner name for the column.  You can check that by editing the column in SharePoint.  The inner name will be included in the URL at the end as Field=something.  That something is the inner column name.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Community Support
Community Support

Re: ODATA filter query to filter SharePoint list items in MS flow

Hi @rajp ,

 

Please take a try with @ScottShearer 's and @Pstork1 's solution, also you could consider adding a "Filter array" action under the "Get items" action, and you just need to add the dynamic content of the MessageID.

1.png

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
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
Super User III
Super User III

Re: ODATA filter query to filter SharePoint list items in MS flow

@rajp 

If you want to check to see if no records are retruned from Get Items, you can use a condition with the following expression:

length(body('Get_items')?['value'])
NoItems.jpg
 
 
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

9 REPLIES 9
Highlighted
Dual Super User II
Dual Super User II

Re: ODATA filter query to filter SharePoint list items in MS flow

  1. OData filters will work on custom columns, depending on the data type of the column. OData in general has difficulties with DateTime, choice, or people columns.  Basically anything that isn't a simple text field.  There are still ways to do it, but its more difficult.
  2. If the OData query finds no records then the result should be an empty collection.  You should be able to test for that. It won't provide you anythign that you can use to create a new record, but should be something you can put a condition on so that your create item is in the No Branch


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Helper I
Helper I

Re: ODATA filter query to filter SharePoint list items in MS flow

Hi,

1. I have custom column as shown below, data type is 'Single line of text', I am getting error this error

"message": "Column MessageID' does not exist. It may have been deleted by another user. 

SP list.PNG

I am storing MessageID details in variable named as 'Id' and used filter query syntax as below

SP list filter query1.PNG

SP list filter query2.PNG

But both are throwing same error

Highlighted
Dual Super User II
Dual Super User II

Re: ODATA filter query to filter SharePoint list items in MS flow

Make sure that you are using the Inner name for the column.  You can check that by editing the column in SharePoint.  The inner name will be included in the URL at the end as Field=something.  That something is the inner column name.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Super User III
Super User III

Re: ODATA filter query to filter SharePoint list items in MS flow

@rajp

I agree with @Pstork1 and would like to add that another way to check how you should be referring to the Msaage ID column is to run a Get Items action on the list without your filter and check the output of the action in a run.  Refer to the Message ID column in exactly the same way as it is referred to in the output.  Your example with the single quotes shows the correct syntax.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Community Support
Community Support

Re: ODATA filter query to filter SharePoint list items in MS flow

Hi @rajp ,

 

Please take a try with @ScottShearer 's and @Pstork1 's solution, also you could consider adding a "Filter array" action under the "Get items" action, and you just need to add the dynamic content of the MessageID.

1.png

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
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
Helper I
Helper I

Re: ODATA filter query to filter SharePoint list items in MS flow

@Pstork1@ScottShearer@v-alzhan-msft:

Thanks for your responses, My first part of question is resolved now with your responses(not sure what I was doing wrong but it do work now).

Regarding 2nd part: If ODATA filter returns empty I used contion filter to compare MessageID=NULL but this didn't work so that I can create new record if it's not avaiable in list.

Highlighted
Super User III
Super User III

Re: ODATA filter query to filter SharePoint list items in MS flow

@rajp 

If you want to check to see if no records are retruned from Get Items, you can use a condition with the following expression:

length(body('Get_items')?['value'])
NoItems.jpg
 
 
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

Highlighted
Helper I
Helper I

Re: ODATA filter query to filter SharePoint list items in MS flow

@ScottShearer@Pstork1@v-alzhan-msft :

Thank you all for your responses, @ScottShearer : I will try you suggestion if there is no record returned through ODATA filter query(Hopefully this will be the easy way than what I have done).

I have resoved this using other way around.

Highlighted
Helper I
Helper I

Re: ODATA filter query to filter SharePoint list items in MS flow

@ScottShearer: I tried your suggestion for 'if there is no record returned through ODATA filter query' and it worked.






Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (5,551)