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

[AnotherMSprofil]

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!

[v-yamao-msft]

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.

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

[Rebetcha]

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

[Anonymous]

Same issue with "Get Files" here.

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

[pheil]

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

[Rebetcha]

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. 

[vecerpa]

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.

[Aquanikus]

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 🙂

[colonel_claypoo]

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

[Praveenpk365]

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

[Anonymous]

Do we have any updates year and a half later? 

[Anonymous]

This is still an issue as far as I can tell, so posting to bump! As with @Praveenpk365 above I just tried:

https://{mysharepoint}.com/sites/{sitename}/_api/web/lists/GetByTitle('list-title')/items?$filter=su... and with curly brackets with no success. If the column is a non-bracketed one e.g. editor, it works fine. Surely there must be a way to call these?

[RK800-1]

Hello!

Just managed to figure it out. Try to use Title field as on my screenshot. It worked out for me.

[RK800-1]

Answered below, please try

[RK800-1]

Answered below, please try

[RK800-1]

Answered below, please try