Track Time at Client Sites With the Click of a Button using Microsoft Flow

If you travel to multiple client site locations in a day, keeping track of how many hours you are in each place for billing purposes can be tedious. With Microsoft Flow buttons you can create a solution that captures the date, your location, when you arrived, and when you departed the location with the touch of a button! You can store this data in several different ways including in a SharePoint list, Google sheet, or Excel workbook.

For this example we will be using an Excel workbook. Before you begin creating the Flow, create an Excel file with the following columns in a table: Date, Arrival Time, Departure Time, Hours on Site (Hours:minutes), and Address. A _PowerAppsId_ column will get automatically added when you connect the Flow.

hours on site flow button.png

On the Arrival Time, Departure Time, and Hours on Site (Hours:minutes) columns change the number format to Time.

number format time.png

In the Hours on Site (Hours:minutes) column you will need to enter the formula: =TEXT(C2-B2,”h:m”)

hours minutes utc

Now we will create two buttons, one to press when you arrive at the client site and another when you depart the client site.

To create the Arrival Button:

Navigate to My Flows and click Create from blank.

create from blank Flow

Search for “button” and select the Flow button for mobile trigger. No need to create any inputs.

Flow button from Mobile trigger

Click New step then Add an action.

New flow step action

Search for Excel and then select the Insert row action. Click the folder in the File name and select the file you created. The Table names in the file will then appear in the drop down. Choose the table, if you didn’t name it anything it will show as Table1.

Insert Row Excel file table

Click Add dynamic content and add Date to the Date field and Full address to the Address field. For the  Arrival Time (Arrival_x0020_Time) field you need to enter an expression by clicking Add dynamic content. The expression is: formatDateTime(addhours(utcNow(),-4),’g’)

Note: The -4 represents the difference between my current time zone (Eastern Time) to universal time UTC. You cannot change the time zone for your Flow so we have to have this in the expression. You can figure out the difference you need to enter by googling Universal Time UTC or gong to here and then calculating the difference. 

format arrival time

Name the Flow Arrived on Client Site. Click Save Flow.

save flow

To create the Departure Button:

Navigate to My Flows and click Create from blank.

start from blank Flow Microsoft

Search for “button” and select the Flow button for mobile trigger. No need to create any inputs.

Flow button from Mobile trigger

Click New step then Add an action.

New flow step action

Search for Excel and then select the Get Rows action. Click the folder in the File name and select the file you created. The Table names in the file will then appear in the drop down. Choose the table, if you didn’t name it anything it will show as Table1.

get rows excel flow

Click New step then Add an action.

New flow step action

Search for Compose and then select the Data Operations-Compose action.

compose.PNG

Click on Dynamic content, click Expression and type in last(body(‘Get_rows’)?[‘Value’])?[‘ItemInternalId’] then click OK.

compose data operation dynamic content

The function will now show up in the Inputs field.

last row function

Click New step then Add an action.

New flow step action

Search for Excel and then select the Get Rows action. Select the File name and Table name fields again. Click Add dynamic content and add Output to the Row id field. For the Departure Time (Departure_x0020_Time) field you need to enter an expression by clicking Add dynamic content. The expression is: formatDateTime(addhours(utcNow(),-4),’g’)

format date time utc add row

Name the Flow Departed Client Site. Click Save Flow.

save flow

Your buttons are now ready!

flow client site buttons

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s