Weekly submitting of a file to a Document Library

[Matthew Carter]

Goal - check if a file has been uploaded in over a weeks time after the other uploaded file was uploaded by the same person yet will have a different file name.

Start:

People fill out a Microsoft Forms form with an attachment (file) and other metadata.

I create a file (from the uploaded Forms one) into a Document Library in SharePoint Online with that associated metadata.

Issue:

I am trying to figure out the logic.  I can get a calculation item to show that the file has been submitted on date x, yet I am trying to figure out how to know that John Doe uploaded a file 01/01 and then on 01/08 uploaded another, so that is a week apart..great.  If OLDER, then an email goes out to that person.  I can surely figure that out.

Jane Doe uploads another file 12/1 and then on 12/6 uploads another one, that's not exactly a week.

I know that I can build an array of people and the date that they submit and can check, just not sure how to go about getting counters.  There will be a LOT of people submitting files on a weekly basis.

I don't know how to get it so that when a new person submits a file, it is checking if they are new and if not, then to set a status to something.  Then to somehow keep track of files that are two weeks or three weeks old and needed for archive, yet not "counted" as it is only the current week and the one before.

Thank you!
Matt

[#2]

Hi @Matthew Carter 

If you are able to maintain a SharePoint List in addition to the Document Library, I would suggest having a list item for each person with a column which stores the date of the most recently uploaded file. Let call this column LastUploadDate.

I assume you have a flow which is triggered by form submissions and use this to write the uploaded file to your document library. You can use this flow to add/edit entries in the new list to keep the LastUploadDate column up to date.

You can then have a flow triggered daily to query the list for any entries where LastUploadDate is older than 7 days. The result of this query is the list of people you need to send notifications to.

The new list could also record when the reminder notification was last sent out, allowing you to avoid sending duplicate notifications.

Presumably, when someone leaves your organisation you don't want to send them notifications about their late form submission. In this case you can remove them from the new SharePoint list.

[#3]

Hello I am having trouble figuring out this logic. Would I create a separate column from the Created By column which exists and the Date Submitted Column which I have? I don't know how to group them together in say an array or something to check.
I appreciate the assistance, I am just having trouble understanding the specifics of it to get it to work. I know what I want to do, it is getting it to work in Power Automate.
Thank you.
Matt

[#4]

Hi @Matthew Carter ,

I think you might be running into problems if you are trying to manage this process solely within the Document Library.

My suggestion is that you make use of a NEW SharePoint List in ADDITION to the SharePoint Document Library you are already using. As an example, lets call this new list Submission Tracker.

This new Submission Tracker list would have only one List Item per person. When someone submits a new form response you would create or update the corresponding List Item for that person in the Submission Tracker list and set the LastUploadDate column to the current date.

You then set up a Power Automate flow to run every day which queries the Submission Tracker list for all people where the LastUploadDate column value is older than 7 days. The result of this query contains the people you need to send notifications to.

Hopefully that makes a bit more sense, but please come back to me if not.

[#5]

Thank you. I DO appreciate your taking the time to write to me! I DO! THANK YOU!

I have a Microsoft Lists and called it Submission Tracker and my issue is sadly HOW do I do this?
How do I keep track of the persons that have submitted already and maybe a unique field? if so, how do I account for that? I don't know how to check a column to see who all is in it and then if they are in the list or column, then overwrite it.
I thought to create a variable array to somehow grab the column with the person's name, maybe the Created By field and put that into an array - not sure exactly how, and then check if it is in that array. If NOT, then add it and then append to the array - however that might be only one time.
This is where I get frustrated and confused.

[#6]

Hi @Matthew Carter 

Don't worry about arrays, I don't think you need them.

I think we might be structuring our lists differently as I'm not quite sure where you're hitting problems. Perhaps an example of what I did might help.

1. I created a new communication site called Submissions.

2. On the Submissions site I created a new list called Submission Tracker. I'm using the Title column of the list to store the email addresses of the people making the submissions. I'm assuming you have an email address included in the form submission. If you restrict the form to people in your organisation then you will get their email address in the Responder field.

I added a DateTime column called Last Submission Date.

Both Title and Last Submission Date were configured to require unique values and to have indexes.

3. I created a new Power Automate flow called Submit Form Handler, triggered by new submissions to the form.

The first three actions of the flow are shown below.

Our aim is to see if we already have an item in the Submission Tracker list for the user making the submission. We are storing email addresses in the Title column.

The third action is used to retrieve items from the Submission Tracker list. The important properties are Filter Query and Top Count. Filter Query is used to query the list for any items where the Title column matches the email address of the user making the submission. 

The next action is a Condition where we test if any items were found by our above Get Items query. If the collection of items returned by the query is empty then we need to create a new item, otherwise we will update the existing item. 

Whether creating a new item or updating an existing item, we write the form's submission timestamp as the Last Submission Date column.

It can be difficult to see what formula is in use for the condition.

left part:    empty(outputs('Get_items')?['body/value'])

operator:   is equal to

right part:   true

Setting formulas can fiddly, it might take a bit of time to figure out how to enter them in to Power Automate correctly.

The aim of the above condition is to see if the array of results from the Get_items action is empty.

You should be able to add the above actions to your existing power automate flow for uploading documents to the submission document library.

Once you are able to get the Submission Tracker list populated, then you can turn your attention to querying the list every day to see if any email addresses have a Last Submission Date older than 7 days.

Good luck 🙂