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

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
Dual Super User
Dual Super User

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

Community Support Team
Community Support Team

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

Super User
Super User

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
Dual Super User
Dual Super User

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

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

Dual Super User
Dual Super User

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

Super User
Super User

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

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

rajp
Level: Powered On

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.

Super User
Super User

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

rajp
Level: Powered On

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

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

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 Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,123)