Get files from dynamic SharePoint folder name

[BA_Pete]

Hi all,

As you may be aware, MS recently changed how email message folders in Teams are named.

All emails received in a Teams channel used to go into a folder called 'Email Messages'. They now go into a folder called 'EmailMessages_M_yyyy' e.g. EmailMessages_2_2021 for emails received in February, EmailMessages_3_2021 for those received in March etc.

I had a flow that was triggered by new files being created in the 'Email Messages' folder which, obviously, is now broken. For reference, it looked like this:

I'm trying to amend this to pick up any new files created in the new, dynamically-named, folders. I've got this far:

When I test this flow it seems happy enough, but when I actually run IRL, I get 'NotFound Status 404' errors at two stages:

1) At the Get File Metadata stage when the first condition is TRUE

2) At the Delete File 2 stage when the first condition is FALSE and the second condition is TRUE

I'm thinking that maybe the Get files (properties only) stage is the incorrect action to use, but can't seem to see anything that would do what I need.

It isn't necessary for my flow to delete the *.eml files, it's the file rename/move action that I'm really after.

Please help.

Pete

[DamoBird365]

Hi @BA_Pete 

Unfortunately it is really hard to see your flow but I am wondering if you can share your expression for the folder path.  Have you tried sticking it into a compose and checking back on the history to ensure you have the folder name in the expected format?  The 404 would suggest file not found which would make me think the path is incorrect.  The compose action will let you actually see what you have supplied the get files step if it is hidden by the failure in the history.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

[BA_Pete]

Hi @DamoBird365 

Thanks for taking a look for me.

The variable that generates the folder path is:

concat('/Shared Documents/Data Files/EmailMessages_',replace(utcNow('MM'),'0',''),'_',utcNow('yyyy'))

The Get files (properties only) step looks like this from the history - it appears to be populating the 'Limit Entries to Folder' field correctly from the variable:

The raw inputs from the failed Get file metadata step looks like this:

{
    "host": {
        "connectionReferenceName": "shared_sharepointonline",
        "operationId": "GetFileMetadata"
    },
    "parameters": {
        "dataset": "https://xxxxxxxxxxxxxx.sharepoint.com/sites/BPAData-DataFiles",
        "id": 667
    }
}

The fact there was an "id" entry indicated (to me) that it had found the file in the folder path.

I'm really new to Power Automate, so not sure about using the Compose action that you suggested to be honest, sorry.

Pete

[DamoBird365]

Hi @BA_Pete 

Ok, if that step appears to be working and you know you have an item ID 667 (just add the ID column to a view of a list and check if not sure), then we need to look at your delete step.  Can you screenshot that and look at the history?

The compose action is just a wee tip for debugging if you aren't able to get strings in history.  You can output a dynamic field to a compose and see what it actually looks like after a run. It doesn't affect the flow operation.

Damien

[BA_Pete]

@DamoBird365 ,

I've not taken a screen shot of the delete step, as that's secondary for me at the moment. I'm hoping that once the actual new file creation part is fixed, I can work on fixing the delete steps at my leisure. Obviously, if this part is critical to understanding the whole problem, I'll send it over.

The failed file creation branch, specifically the Get file metadata step looks like this:

Again, it seems to have picked up the file ID ok, but I'm not sure what else it's looking for that it's not found.

Pete

[DamoBird365 · Accepted solution]

Hi @BA_Pete ,

That's helped.  The ID you are supplying is the individual item or file ID but not the File Identifier.

You need the following:

Damien

[BA_Pete]

@DamoBird365 ,

Ah, ok.

I think I used that on my previous, successful, flow as it was available dynamic content, having used "When file is created in a folder" trigger. My new flow, however, uses the Teams connector "When a new channel message is added" trigger. This trigger doesn't appear to provide the [x-ms-file-id] dynamic content item to select.

I changed the trigger as I couldn't tell the flow which folder to check for files being created in without initialising the variable first. This is why I've used the later Get files (props only) action to subsequently pick up this info. However, this action also doesn't provide the [x-ms-file-id] item to select later.

Is there another action that I can/should use instead of Get files (props only) to make this dynamic content item available?

Pete

[BA_Pete]

Never mind, think I've found it.

Testing now using [Identifier] instead of [ID]

Pete

[BA_Pete]

@DamoBird365 ,

Ok, progress. The Get file metadata and delete steps (from the right branch of the first condition) now appear to be happy using the new content item. I also used this new content item in the Get file content step.

However, it now fails on the Create file step with a BadRequest error.

This is how that step is set up:

Here is what the history shows:

Apologies for moving the goalposts here, I'll make sure I mark solutions for the separate issues separately 🙂

Pete

[DamoBird365 · Accepted solution]

@BA_Pete certainly getting your moneys worth 🤣 

Can you check the input/output of the create file, must be something to give it away in there. 

I don't suppose the file name already exists?  You would want to include something dynamic in the file name to avoid this otherwise filename_   formatdatetime(utcnow(),'ddmmyyhhmmss')     .xlsx

[BA_Pete]

@DamoBird365 ,

LOL. I had to apologise and mention about the separate solutions as I'm a Super User on the Power BI forum and hate it when people do this to me, so, sorry again 😄

Input is a million pages of gobbledegook, so presumably a file binary.

Output shows this nugget: 

"A file with the name Shared Documents/Data Files/employeeModelPA.xlsx already exists"

Which clearly validates your subsequent question.

I've countered this by disabling "Allow Chunking" which, for some odd reason, allows it to overwrite the previous file.

WHOOP!

Thanks for sticking with me, you're a star!

Pete

[DamoBird365]

@BA_Pete no probs - I am sure you could teach me a lot about PowerBI.