Report Identity Based Check

lshneier · ‎05-23-2022

I have asked our central finance team to drop files that we are meant to be sent but the report filler forgets into a our regions SharePoint folder. I then set a flow, to tell me when new items are in this folder so I can move them into the correct archive locations. The issue I have is the amount of files that would get uploaded is variable, it should be about 17 a month but sometimes people don't send a report in, or adhoc reports get added after the big batch of reports gets moved into the archive folder.

Currently I have the trigger condition of the flow being if the folder list in questions is greater than 15 message me on teams (can't use email alerts due to admin policies). However, I want to reduce this number to 0 but not get tons of messages on teams when the bulk upload of files happen.

I assume I should be able to put in a condition on the flow that says something along the lines of if flow triggered successfully in past half hour stop running the flow otherwise continue (which would then trigger the message to hit teams. however my knowledge of power automate is very limited as i tend to work in excel more than anything else so have no idea how to accomplish this.

Any advice you could offer would be great, do let me know if screenshots etc are needed. This Flow is being created in the web app if that makes a difference as that is all i have access to.

eliotcole · ‎05-23-2022

A simple solution to your current issue is have a SharePoint list which monitors flow runs.

Report Identity Based Check

I'm going to assume that there is at least one identifying piece of information for each file that helps you discern what it's for. So in this example I am pretending that for each report, they create a folder for it.

First up, create the SharePoint (or Microsoft) List, with a 'folderid' and 'foldername' column, and you can use the 'Title' column to either repeat the foldername, or if you like do something special.

Then for each run:

The first action is a filter on the monitor list to see if this folder item was added/modified in the last 30m.
If not, check if it exists at all, and either modify an existing item, or create an item, with the folder's ID and name.
If so, terminate the flow.

This will still mean that the flow runs on each new item, but it will halt the notifications if something has just been done.

You can also add more information to this about how many files are there, or file properties, etc.

---

Alternative Method - Trigger Conditions or Flow Management Actions

There is almost definitely a way to use a just stop a flow triggering using trigger conditions to check for previous runs. However I don't have time to research that for you right now. But see what you can find, or maybe another user here will advise on it.

Alternatively, you can use the MS Power Automate management flows to check on flows, and achieve the same things that I detailed in my main solution without a SharePoint list. However the flow would still be running, and you might miss out on the additional management information you could gather on the habits from the SharePoint list. But it's doable. Play with the PA management flow actions if you have access to them.

---

Separately - Information / Business Process Management

Finally, and purely thinking aloud (not suggesting that you're doing anything wrong) ... perhaps a different approach to the business process here could benefit you. I would ask yourself the question:

Are these reports necessarily already the right place for the data to be in?

If the data that they carry could be kept in a SharePoint list, then reports would literally never need to be made. People would always know the current data that they need at a glance.

This would mean that anyone that needs to see the data that would normally be in an excel file could:

Look at a list view (general or personalised) of the data on a SharePoint site.
Have the data imported into a separate, departmentally own (and potentially protected) SharePoint List.
Have it converted into an excel sheet for legacy purposes (or personal viewing preferences).
Have it fed via API into relevant applications automatically.

This would mean that you don't need a complex folder and storage structure for information that might well be better kept in a list format anyway. Plus, once it's there, anyone that does still need an excel file for some reason can have that. 🙂

View solution in original post

eliotcole · ‎05-23-2022

A simple solution to your current issue is have a SharePoint list which monitors flow runs.

Report Identity Based Check

I'm going to assume that there is at least one identifying piece of information for each file that helps you discern what it's for. So in this example I am pretending that for each report, they create a folder for it.

First up, create the SharePoint (or Microsoft) List, with a 'folderid' and 'foldername' column, and you can use the 'Title' column to either repeat the foldername, or if you like do something special.

Then for each run:

The first action is a filter on the monitor list to see if this folder item was added/modified in the last 30m.
If not, check if it exists at all, and either modify an existing item, or create an item, with the folder's ID and name.
If so, terminate the flow.

This will still mean that the flow runs on each new item, but it will halt the notifications if something has just been done.

You can also add more information to this about how many files are there, or file properties, etc.

---

Alternative Method - Trigger Conditions or Flow Management Actions

There is almost definitely a way to use a just stop a flow triggering using trigger conditions to check for previous runs. However I don't have time to research that for you right now. But see what you can find, or maybe another user here will advise on it.

Alternatively, you can use the MS Power Automate management flows to check on flows, and achieve the same things that I detailed in my main solution without a SharePoint list. However the flow would still be running, and you might miss out on the additional management information you could gather on the habits from the SharePoint list. But it's doable. Play with the PA management flow actions if you have access to them.

---

Separately - Information / Business Process Management

Finally, and purely thinking aloud (not suggesting that you're doing anything wrong) ... perhaps a different approach to the business process here could benefit you. I would ask yourself the question:

Are these reports necessarily already the right place for the data to be in?

If the data that they carry could be kept in a SharePoint list, then reports would literally never need to be made. People would always know the current data that they need at a glance.

This would mean that anyone that needs to see the data that would normally be in an excel file could:

Look at a list view (general or personalised) of the data on a SharePoint site.
Have the data imported into a separate, departmentally own (and potentially protected) SharePoint List.
Have it converted into an excel sheet for legacy purposes (or personal viewing preferences).
Have it fed via API into relevant applications automatically.

This would mean that you don't need a complex folder and storage structure for information that might well be better kept in a list format anyway. Plus, once it's there, anyone that does still need an excel file for some reason can have that. 🙂

lshneier · ‎06-01-2022

Hi Sorry,

for the delayed response i got side tracked trying to get a macro on a different project to work as i knew it'd be a couple of weeks before the team that uploads files into the folder my flow tracks would give me the latest files. i have however, managed to get the reduction of notifications done based the list and then a past time step to create the within x time loop as you suggested in your solution.

I'm still currently having to manually define the ID column in the list filter (currently set to greater than 32 as that only picks up this months items. However, i was wondering if you had any idea how i could set this to pull just the max list ID rather than a set variable which will progressively pull more and more lines.

eliotcole · ‎06-01-2022

If you have created the monitor list with a field that is specifically set to hold the library file ID (which is the same as a list item number), then you can make that field require unique values.

Then you can Get items, filter by that column using the File's Library ID number, it will return one result (in an array). From there you can just pick out the fields that you want using the expression builder. When you're in there, select the body of the Get items, place a '[0]' after it, then a question mark, and the field name inside quotes and square brackets. Like this:

body('Get items')[0]?['FieldName']

That zero picks the first item from the one item array, then the ? means you're querying it and in that query you're asking for the 'FieldName' value.

lshneier · ‎06-06-2022

Thanks after a lot of searching through setting sub menus I think my admin team have blocked me from adding in the document ID column for our sites. However during this process I was able to work out that as I did have the standard ID column that is automatically added to with the sequentially next number i.e. 1 2 3 etc. I could just order the get items by this field in descending order for the top item and achieve the same result of pulling the max item out. thank you for your help in this process

I am not 100% if this is what you meant in your reply. However, this seems to remove the need for a filter criteria and ensures that only the most recent relevant items get passed into the rest of the flow.