Mileage tracking workflow

[PowerEmployee7]

I need some general direction on how to design an app. We are trying to replace a manual process where once a month, about 5-10 employees have to submit vehicle mileage. Currently, they submit it to a specific manager and sometimes that manager has to send email reminders for people to get their mileage submitted. 

This seems like something super easy to build in combination with a Sharepoint list / Power App. What I'm struggling with is that the people who have to submit this data is dynamic. They are all apart of a 365 group, but the group membership changes from month to month. Here is what I want the app to do:

On the first of the month, PowerAutomate automatically pulls in Azure AD group membership, sends an email to the 5-10 members. The 5-10 members submit their mileage in a Sharepoint list / Power App type form. Then I'd want daily email reminders to the people who haven't submitted it yet.

I know I'll use the recurrence process in this workflow, but need some general direction on how I should build this. Maybe I'm overlooking something and it is relatively basic, but I'm a little stuck and need a small nudge. Thank you!

[creativeopinion]

Will it be a single record per person? If so then I would probably set up a recurrence trigger at the start of the month to add a record for each employee that is required to submit their milage to a SharePoint List. 

Your SP List could contain a Mileage Due Date column and that could be set based on the time the flow is triggered. For example, if the flow is triggered at the start of the month, the due date is set to the end of the month or 30 days from the trigger.

This way you can run a weekly reminder (which would be set up with a different flow that could be run weekly to check the list for any records that do not have a value in the mileage column).

What are the requirements to submit the mileage? Does the employee need to only submit a number for the month or is there a bit more involved? Or do they need to submit a record for each trip?

[PowerEmployee7]

@creativeopinion - So it is 1 entry per person. These are rentals that need to stay under certain mileage so once a month is enough. That's a good idea on the sharepoint list containing a milage due date column. 

And if I'm understanding this correctly, basically I could have the flow start and pull in the dynamic Azure group at the start of the flow. Then send an email to everyone to submit their milage.

For the reminder, I think your idea is solid that I could run the flow daily on who has data in the column missing.

So to recap, create a flow that runs on the first of the month for example. The sharepoint column will have employee name, mileage, and month/date. I'll have the employee name and month / date automatically be created each month for everyone that needs to submit the data. Then I'll have a flow that runs daily checking for blanks in the mileage column and send a reminder to those. Anything obvious I'm missing?

Thanks so much for your help - that's been extremely helpful.

[creativeopinion · Accepted solution]

Yes I think that could work. I'm not sure what the permissions would be on your list, you could grant item level permissions to an item for each employee if you'd like. For the employee name I would use a Person column. I would also recommend you have a Person column for the manager.  

I uploaded a YT tutorial recently that covers this. If you will be requesting users to update the list directly, with item level permissions, they would only see their line items. 

You could take it one step further by creating a view that only shows line items that have empty data columns. The link to that specific list view could be included in the email or Teams reminder.

In addition to that view, you can create a Manager View where the list is filters out [Me] in the Manager column. Customize this view to show them all submissions or recent submission (whichever).  

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[PowerEmployee7]

@creativeopinion You've been really helpful and I have the app 98% built, but have one small question. I have two separate lists. One is the master list that the manager has access to has 5-10 records in it for the 5-10 people with rental vehicles. It has name, vin, miles, etc. The second list has the same columns, but it will have new records every month for the individuals to submit their miles data.

I want the manager's list to update with the latest miles once the employee submits their monthly form. I tried to create a list relationship, but PowerAutomate creates a new record for the employee every month so I don't want to try and manage the relationships each time a new record is created. All I want to do is patch the data from the employee form over to the manager list. The Employee Name and the VIN are both values that will be unique. I'm basically looking for a very simple vlookup function. That way, the manager's list always has the most up to date info for how many miles each employee has when submitted. Thank you!

[creativeopinion · Accepted solution]

Couldn't you solve this by creating a custom view in the SharePoint list? Are you looking to display a total numbers of miles for the month?

If your SP List has a column for the manager (which i think it does) the SP view can filter on the Manager column equal to [Me]. This means the manager would only see the list where they appear in the Manager column.

You can then group by the employee who has submitted the milage (sort alphabetically... or however you'd like).

Add a Total to the mileage column to display a total per employee and there should be an overall total displayed at the bottom of the list as well. 

Not saying you can't patch a number to a different list (you can... but doesn't mean you should) Let me know if you think creating a custom SP list view would be sufficient. I think it might. Would make it a lot easier on your end!

[PowerEmployee7]

You've been tremendous help. The reason I didn't do a custom view was because I wanted the manager to have a list of the employees to be able to add / delete and I didn't want it to affect the monthly submission records. I figured out to patch, I looked up the sharepoint list and matched the VIN across both sharepoint lists, then patched the mileage form. Here is the formula if someone runs across it. Your Item level permission videos will come in handy for me in the next couple of weeks. Thank you!!

Patch('Employee Vehicle List_1', LookUp('Employee Vehicle List_1', VIN = 'VIN-Employee-Form-Record'.Text), {Mileage: Value('Mileage-EmployeeForm'.Text)});SubmitForm('form-vehiclemiles-miles');Navigate('Vehicle Miles');