Nov 04 2021 12:46 PM
Nov 04 2021 12:46 PM
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.
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.
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.
Nov 08 2021 03:43 AM
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.
Nov 17 2021 01:01 PM
Nov 18 2021 01:15 AM
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.
Nov 19 2021 09:39 AM
Nov 19 2021 12:17 PM
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 :)