This weeks Flow of the week is written by Flow & PowerApps MVP Daniel Christian. Daniel is a rockstar community member, Lego superstar and all around problem solver 101. Check out his YouTube Channel and follow him on Twitter HERE. Also, be sure to leave some questions or comments below and he will be happy to answer them!
Introduction:
Recording your travel mileage is a crucial and currently a manual process to get travel reimbursement, however, now thanks to Flow and the Bing Maps connector we can automate this process as well. There are two Flows, one is for departures and the second for arrival but both of them update the single row in the Excel spreadsheet. The Flow is smart enough to see which was the last entry submitted by the user that only has the departure information and is pending arrival.
Requirements:
1. A Flow subscription
2. A Bing Maps Key
3. A location to save the date. In this scenario we are using Excel saved in OneDrive but you can do the same using SharePoint lists, Common Data Services Entity or SQL tables.
Bing Maps Connector:
You need to first create an account with Bing Maps Portal at https://www.bingmapsportal.com/ using an existing Microsoft account. You can then select the My Keys option in My account drop-down list.
Copy the key
Next head over to https://Flow.Microsoft.com to add the Bing Maps connector which is currently in preview.
This is where you add the API key
Excel spreadsheet:
The Excel spreadsheet is located in the OneDrive for Business and is shared with all the users who are using this Flow.
Flow #1: Depart
This flow uses a virtual button to trigger. Here is what the Flow looks like.
Here is the expression for the StartTime column addHours(formatDateTime(utcNow(),’MM-dd-yyy HH:MM’),-5)
Flow #2: Arrive
When the flow is triggered, we first create two variables, one is to save the email address of the user provided by the Flow button action, the other is where the distance is going to be saved. The FlowUserVar is required for a condition to work.
Next we Get all the rows from the Excel spreadsheet.
The following steps are required to first find the rows that contain the end user’s email address. In it find the row that doesn’t have the destination or end time and address. Here we use the Length express to find that row where the end time is empty. Instead of end time you could use end address as well.
length(items(‘Apply_to_each’)?[‘Endtime’])
As you can see below if the length is equal to 0 then we are updating that row with the end time and date. We find that row by using the Row id.
Now that we have the start and end address we can use Bing maps to find the distance between them. To do that we add the Get route action. Waypoint 1 is the start address which we get the Excel spreadsheet and Waypoint 2 is the end address which we got from the Manually trigger a flow action.
Finally, we can take the total travel distance and update the row. For some reason Flow does not like to use the Travel Distance value as is and hence we have to wrap it in a variable and then save it to the row.
Now you have a single row which has recorded the start address with date and time, end address with date and time and the mileage.
This video demonstrates how the two Flow virtual buttons work and the logic behind the flow.
Conclusion:
The total mileage is presented by Bing Maps and might have subtle differences if compared to other maps such as Google. Also, in this case I have only recorded the mileage but you could record the total time it took by calculating the delta between the start and end time.
