cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Support
Community Support

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.

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

Same issue with "Get Files" here.

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

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

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. 

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.

Regular Visitor

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 🙂

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

 

Frequent Visitor

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
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (7,472)