Automation, Flow

Track Project Resources with a Project Management Timesheet Application – Timesheet Calculation Flow

This is the second post in the post series titled “Track Project Resources with a Project Management Timesheet Application”. The first post of the series on the Data Model is at this link.

This post will cover a couple of concepts:

  • Build a before-save fast update flow where data on a related record is retrieved (get)
  • Build a decision element
  • Build time and date formulas

We covered the objects of the Project Management Application in the first blog post. There is a Timesheet Record that is created to keep track of time and effort dedicated to a project. The important fields on this record other than the contact and the project are:

  • Start date and time
  • End date and time
  • Hours (duration)
  • Cost (optional)

Without automation, a user entering a new Timesheet Record would have to calculate some of these fields using the values in the other fields and enter the data. Instead, we will use automation to calculate and update some of these fields before they are saved to the database.

Here is what our final autolaunched before-save flow will look like:

Before Save Timesheet Calculation Flow

What will this flow do for us:

  • Start the flow whenever a Timesheet Record is being created or updated (before-save).
  • Get the Hourly Rate Record for the Contact on the Timesheet Record if it exists and save the rate to a variable.
  • Check which fields are entered: Start Date & Time and End Date & Time or Start Date & Time and Hours (duration).
  • Calculate the missing field: Either the Hours (duration) or the End Date & Time.
  • Calculate the Cost based on the Hourly Rate if it exists.
  • Assign all values to the fields for them to be saved.

You will need to read the first blog post, create a development org and install the unmanaged package to be able to build this flow and test the functionality yourself.

First, start a new flow in Salesforce Setup and choose Autolaunced on the next screen. For a before-save flow, we need to double-click on the start element that comes with every new flow and change the flow trigger parameters. Here is a screenshot of what that looks like once it is completed:

Start element: Flow will be triggered when a Timesheet Record is created or updated.

Next, we need to use a get element to retrieve the related Hourly Rate record if it exists. We will use a variable to store this information. The default value for the variable should be zero, so that the cost comes out as zero when there is no Hourly Rate record for the Contact on the Timesheet Record.

Hourly Rate Variable
Get Hourly Rate Element: Please note that the Timesheet Record we are working with is being referred to as !$Record. Therefore the Contact on this record is {!$Record.Contact__c}.

Then we build a decision element to check which fields are entered by the user:

Outcome Conditions for when the start date & time and end date & time are entered.
Outcome Conditions for when the start date & time and the hours (duration) are entered.

Before we assign the missing values to the fields, we need to build a couple of formulas for our time-based calculations:

Formula to calculate the duration of work in hours: ({!$Record.End_Date_Time__c}-{!$Record.Start_Date_Time__c})*24
Formula to calculate the end time & date: {!$Record.Start_Date_Time__c}+{!$Record.Hours__c}/24
Formula to calculate the cost: ({!$Record.End_Date_Time__c}-{!$Record.Start_Date_Time__c})*24

Now we can proceed to the assign elements that correspond to our decision outcomes:

Assign formula value to the Hours field.
Assign formula value to the End Date & Time field.

Regardless of the decision outcome, we would like to assign the cost of the Timesheet Record in the final step:

Assign formula value to the Cost field.

Here are the test results (gallery of 4 screenshots):

And here is the final flow product with the manager view on top (see left panel):

Enjoy and do not forget to activate your flow.

Please comment with your feedback and questions.

Leave a Reply