Forum Discussion
Weekly submitting of a file to a Document Library
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
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.
- Matthew CarterNov 19, 2021Iron ContributorThank 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.- DanWatfordNov 19, 2021Copper Contributor
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 🙂