cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Hi, All. I am trying to get the dreadful Get Files to work.

My flow will be triggering on "When a file is modified" a library potentially having 1000s of files, with 10-100 files modified at once.

I am copying files, and then need to get their IDs, because the "wonderful" Copy File doesn't care about metadata.

I'm fine to do it myself, but the "wonderful" Copy File does not return the ID of the new file, which is required by the Update File (requires numeric ID).

 

So I'm using Get Files in order to get the ID.

But since the library is BIG, I don't want to dump all the files into flow, and then loop through 1000s of files, looking for the right name and path.

Instead, I want to build a filter!

 

The boards are littered with messages like "Name/Path/FileRef/etc do not work!"

So I looked into the output of Get Files w/o a filter. Here's a (redacted) sample for one file out of many

>>>

{
      "@odata.etag": "\"1\"",
      "ItemInternalId": "55",
      "ID": 55,
      "Modified": "2017-11-17T00:09:13Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Department": null,
        "JobTitle": null
      },
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Department": null,
        "JobTitle": null
      },
      "Created": "2017-11-17T00:09:13Z",
      "OData__dlc_DocId": "HIWSAS2VENCA-908746327-55",
      "OData__dlc_DocIdUrl": "https://###.sharepoint.com/sites/###/_layouts/15/DocIdRedir.aspx?ID=HIWSAS2VENCA-908746327-55",
      "{Identifier}": "StagingExternal%252fHER%252fTest%2b02%252fTransparent%2bKeyboard%2b01.png",
      "{Link}": "https://###.sharepoint.com/sites/###/StagingExternal/HER/Test%2002/Transparent%20Keyboard%2001.png",
      "{Name}": "Transparent Keyboard 01",
      "{Path}": "StagingExternal/HER/Test 02/",
      "{ContentType}": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
        "Id": "0x0101001AE9008232653842A7D277CC16324094",
        "Name": "Document"
      },
      "{ContentType}#Id": "0x0101001AE9008232653842A7D277CC16324094"
    }

>>>

 

As we can see, the Name, the Path and even the Link are present, albeit in a funky format with curly brackets: {Name}

So I'm building a filter

>>>

{Name} eq 'variables['TargetName']'

>>>

While trying to query for it in the $filter, I get a 400 (Bad Request) error:

>>>

The $filter expression \"{Name} eq 'TC8000_TransparentKB_Public.zip'\" is not valid 

>>>

I tried all combinations of URLescaping, single and double quotes, escaping quotes etc, but could not get it to work.

Was anyone able to get it to function? How would one address fields such as {Name} in oData filters?

 

Thanks!

9 REPLIES 9
Highlighted
Community Support
Community Support

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Hi AnotherMSprofil,

 

When using the action “Get files(properties only)”, it seems that Filter query only works for the custom columns.


Per my test, I tried with different functions using different custom columns, they can return the expected value, while when working with the standard columns, the error message you mentioned will be returned.

 

Status is a custom column I have created in the library, when working with it, expected value will be returned.
3.PNG


I will help confirm this issue on my side and back to you later.


As a temporary workaround, you may try to add the custom column to save the file name then filter based on the custom column, I assume it should work.


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

@v-yamao-msft: is there some news to report about this problem? I am struggeling with exact the same issue: copy a file but unable to update the metadata.

I am curious if the "Copy files"-action will at some point give the file ID as output and if the filter query on the "Get files"-action is going to be able to also filter on default columns. 

Highlighted
Kudo Collector
Kudo Collector

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Same issue with "Get Files" here.

@v-yamao-msft any update on that? Thanks!

Highlighted
Frequent Visitor

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Finally found someone else discussing this issue.  Anyone get this to work ever?

Highlighted

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Not easy. I used the filter array action to look for the copied file.

But they have released a new SharePoint copy action since then. The new one gives the document ID as output, exactly what I proposed as a solution.

I tried it out just now and it works like a charm. 

 

Use the SharePoint Copy file action (not the one that says deprecated in the title) followed by the SharePoint Update file properties action.

Use ItemId from the dynamic content list that was output of the Copy file action in the Id field of the Update file properties action and fill in all the property fields with the content you want to update it with. 

Highlighted
Power Participant
Power Participant

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Hello all,

It seems like the only thing that work is to use filter action after you retrieve all items from library. I am not sure if there is already idea for this but such thing can be used in many scenarios.

P.

Highlighted
Regular Visitor

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Hello,

 

Default column name in sharepoint online file library is "LinkFilename".
You can try to use it in OData filter query.

I hope it will be useful to someone 🙂

Highlighted
Helper III
Helper III

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Thanks but this gives me:

 

{
  "status": 400,
  "message": "The field 'LinkFilename' of type 'Computed' cannot be used in the query filter expression.\r\nclientRequestId: 366e1018-06b2-4653-bec4-ab4e18824e6c\r\nserviceRequestId: 366e1018-06b2-4653-bec4-ab4e18824e6c"
}

 

For me, the Filter array action works. So when I'm after the file name I simply do 

Name is equal to testfile.xml

 

Highlighted
Frequent Visitor

Re: SharePoint "Get Files (Porperties Only)" oDATA filters fields with curly brackets

Hello Guys !! 

 

Anybody got update on this ? I'm facing same Issue, Not able to use columns with curly braces in Filter Query - Get Files properties action.

The expression "substringof('WCR',%7BName%7D)" is not valid. Creating query failed.

The expression "substringof('WCR',{Name})" is not valid. Creating query failed.

 

Please help me out

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 (8,467)