How to use MS FLow and ODATA to filter a document library on file name?

[Erik Wettergren]

Hi, I'm trying to filter a SharePoint document library using ODATA in the Get Items action in Flow. I can for example use the ODATA expression startswith(Title,'abc') to make it filter on the Title column, but if I do the same with the Name column. i.e. startswith(Name,'abc') stating the Name column doesn't exist.

How come? And is there a way around this?

[#2 · Accepted solution]

@Erik Wettergren 

Erik,

Instead of using name as the filter, try FileLeafRef -

I was able to return all files from this library whose names start with the letter H.

Hope this helps.

Terry McCullagh

[#3]

It certainly helps!

Just out of curiosity - how did you figure this one out? From the SharePoint Search Schema?

Thank you!

[#4]

By the way, tried to do a similar operation using substringof instead of startswith. Oddly though this didn't work unless I switched places of the FileLeafRef variable and the substring to filter on.

[#5]

@Erik Wettergren 

Erik,

I've found the Odata Query Builder from Authur Graus to be extremely helpful in constructing the correct queries.

https://appsource.microsoft.com/en-us/product/office/WA104051159

This article is also helpful:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

Regards,

Terry

[#6]

Hi and thanks again! Great tip about the Query Builder and a useful article.

/Erik

[#7]

Erik - Glad to have been of assistance.

[#8]

@Terry McCullagh 

First of all, *thank you* for this solution, it's great ... and has saved me *hours*.

Secondly, I should say that I haven't come here to doubt anything. However, I noted that this stack notes that FileLeafRef is a bit ... flighty?

https://sharepoint.stackexchange.com/questions/241067/how-to-read-the-name-column-in-a-sharepoint-do...

Is there a chance that this isn't the most reliable method, and we need to build a little logic scope to filter, as per Alice's ( @v-yuazh-msft ) answer in this question to be totally sure?:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Get-File-Properties-from-File-Name/td-p/...

[#9]

@eliotcole-projectfive 

Eliot,

Thank you for reaching out and I'm glad this solution has saved you time. 

I reviewed the posts you referenced and cannot comment on those since I haven't taken the time to try to reproduce any errors that may arise.  

What I can say with certainty is using the solution as accepted by @Erik Wettergren has worked for me in many a SharePoint tenant. 

Perhaps @v-yuazh-msft can elaborate on the benefits of their answer. 

Regards,

Terry

[#10]

@Terry McCullagh ... Nice one, and thanks for the response, I respect that you didn't have to say anything! :)

I think that the approach that I will take is belt and braces. I'll ensure that I've got a fully annotated (commented) 'Pure Flow Logic' available to the user in what I call 'The Worst Timeline' * so that if it fails, then whomever is managing it can switch to that.

Thanks again, mate! :)

* A community reference used to describe a testing branch that I put in most Flows these days via a condition that's always set to run the main line. It'll contain notes, working (how did we get this JSON sample?), and alternates or future parts of the flow, plus some nonsense. Since it's not exactly the easiest place to organise separate Flows (at least, for free users) this feels like the easiest way to ensure that knowledge is available.

[#11]

Hello I have a problem with filtering. I used condition attachmentname ends with .pdf but one of three folders still accepts and .png format files. How I can resolve this?