ODATA filter query to filter SharePoint list items in MS flow

[rajp]

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?

[Pstork1]

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

[rajp]

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. 

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

But both are throwing same error

[Pstork1 · Accepted solution]

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.

[ScottShearer]

@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.

[v-alzhan-msft · Accepted solution]

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.

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.

[rajp]

@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.

[ScottShearer · Accepted solution]

@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'])

[rajp]

@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.

[rajp]

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

---

---

---

---

---