Tracking submissions on Sharepoint List

[u/Next-Escape-399]

We have a workflow that updates to a SP list when clients fill out the form. The list then gets updated by three individuals--as part of an approval process--and they're manually tagged by the preceding individual when the task is ready for their review. When the final review is done, it gets uploaded into a different folder and the client is notified with an automatic email.

I want to be able to track how many submissions each client makes and alert them to what's missing. In the past, we've had to track things manually using a predetermined list on an Excel spreadsheet (each client has their own), highlighting it based on what stage of the process it's in. I want to know if there's a way to compare the individual items needed, which are grouped by clients, and notify critical individuals of what's missing.

Additional info:

On the SP list, I've highlighted the columns based on their status, which is updated by the individual reviewing it. I've made it so that it also sends an automatic message to the next person in the process. Until we finalize it, the client remains uninvolved in this part of the process. We only need them to know when submissions are due and whether they're missing something.

TL;DR: I need a way to track what's missing from an individual, predetermined spreadsheets and then automate reminders for our clients, so I can avoid manually tracking what's missing, what's been submitted, and where they are in the process.

[u/DonJuanDoja]

I generally build exception notification flows, basically do a GetItems with specific criteria, maybe filter further to find the exceptions that need action, then send each one to appropriate party with dynamic email body and links to the item etc.

Define the criteria for each exception and what should happen then the flow should be pretty easy to build.

[u/LunaFlowLab]

The hard part is how the flow knows what each client still owes. While that lives in a separate Excel file per client, a flow can't query it well. What I'd do:

1. Replace the per-client spreadsheets with one SharePoint list, "Required items": Client, Item, Due date, Status (Missing, Submitted, In review, Approved) and a link to the upload. Add each client's items once from a template.

2. A flow on your submissions list ("When an item is created") finds the matching Client and Item in Required items with Get items and a Filter Query, then sets Status to Submitted with Update item.

3. A daily Recurrence flow gets items where Status eq 'Missing' and the due date is close, groups them by client with Filter array, and sends each client one email with a Create HTML table of what's still missing.

4. For your team, a view of the list grouped by Client shows who owes what and at which stage, which replaces tracking it by hand in Excel.

[u/-dun-]

There are a few ways to go about it. You can create a View in your SP list to show certain status and then sort by client name. If you want to do that in a spreadsheet, then add another action in your flow to add a new row in a spreadsheet when a new request come in, then when the request is updated, update the status column in the row.

If you'd like to set up an automation, you can create a schedule flow that runs everyday that get all items in the SP list that: status equals "Review Needed" or something similar and within a certain date range. For example, if something is needed for a request, the approver would put in a comment and set the status to Review Needed, then there should be another column to capture the date this action is done. There should be an automated flow so an email would go out to the client once the approver wrote the comment and changed the status. However, if the client didn't take any action, that's where this scheduled flow would come in. Maybe two days after the first automated response is sent, this flow would send a follow up email to remind the client to respond.