Forum Discussion
Weekly submitting of a file to a Document Library
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.
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 🙂