Salesforce: Calculating ‘Path Progress-%’

Create ‘Path Progress-%’ calculations on Salesforce similar to how it appears on the student’s profile in your Skilljar Dashboard. This tutorial walks through how to create to calculation. 

To learn more about our Salesforce Data Connector integration, see Salesforce & Skilljar 101.

Note: The example used in this article shows the calculation as part of the ‘Path Progress’ object. If you are using visibility settings within the courses that make up a path there might be inaccuracies between the Path Progress-% you see on Skilljar dashboard and Salesforce.

What you’ll need

You’ll need specific objects and fields. For more information, see Salesforce Integration: Custom Objects and Their Fields. 

Objects

The objects you’ll need are (see an example below from Schema Builder):

• Students
• Path Progress
• Path

Fields

The fields you’ll need for the ‘Path Progress-%’ formula are:

• Total Courses: Roll-up formula on Path to count the number of path items (courses) in a path.
• Total Courses Formula: Formula on Path Progress object to pull the value of Total Courses from the Path Object.
• Courses Complete: Count of path items completed by a student in a path
• Path Progress %: Total Courses / Courses Complete.

Total Courses

The below steps are performed within your Salesforce instance. 

1. Go to your Object Manager in Salesforce.
2. Search for “Path’-object.” 
3. Select Fields & Relationships and Create New Custom Field.
4. Select Roll-Up Summary as the data type

5. Provide the ‘Field Label’ and ‘Field Name’ (for example, Total Courses), and select Next.
6. Select:
   • Summarized Object: Path Items
   • Select Roll-Up Type: COUNT
7. Select Next to set up visibility and then Save.

Total Courses Formula

Next, we need to take the value from the field ‘Total Courses’ we created in the last step and add it as part of the ‘Path Progress’-object. We can do this by creating a new formula:

1. Go to Object Manager in Salesforce. 
2. Search for ‘Path Progress’-object. 
3. Select Fields & Relationships and Create New Custom Field.
4. Select Formula as the data type.
5. Provide the ‘Field Label’ and ‘Field Name’ (for example, Total Courses), and select Next.
6. In the Formula-field, enter: ‘skilljar__Path__r.Total_Courses__c’.
7. Select Next to set up visibility and then Save.

Courses Complete

1 Create a new numerical field.

1. Go to Object Manager in Salesforce.
2. Search for ‘Path Progress’-object >.
3. Select Fields & Relationships and Create New Custom Field.
4. Select Number as the data type.

2. Add the completion date of path items, path ID and student IDs to the Path Item Progress object. 

You can create a new field via Object Manager by following the above steps and using these data types and formulas:

• Data type: Formula
• Formula: ‘skilljar__Course_Progress__r.skilljar__Completed_At__c’

• Data type: Formula
• Formula: CASESAFEID(skilljar__Path_Item__r.skilljar__Path__r.Id)

• Data type: Formula
• Formula: CASESAFEID(skilljar__Path_Progress__r.skilljar__Student__r.Id)

3 Create an automation flow in Salesforce Flow Builder to update the ‘Course Complete’-field value when the student completes a new path item. 

Search for ‘Flows’ in the Salesforce setup to access Flow Builder and select Flows under Process Automation:

Select New to create a flow. The flow we build looks as follows:

4 The flow will trigger based on new records being created or updated

For the start configuration, select the following values:

• Object: ‘Path Item Progress’
• Configure Trigger: A record is created or updated
• Set Entry Conditions: 
  • Field: Completed_At__c
  • Operator: Is Null
  • Value: False
• Optimize the flow for Actions and Related Records.
• Run Asynchronously-checkbox: Leave de-selected.

5 Create two variables to store the Path ID and Student ID of the completed path item. 

You can create the variables by selecting New Resource on the side menu:

Create the variable for Path ID by providing a name and description for the variable. 

For the Data Type select Text:

Repeat the same steps for Student ID:

6 Create two formula fields to use the CASESAFEID-formula for both, Path ID and Student ID

After creating the variables, create two formula fields to use the CASESAFEID-formula for both, Path ID and Student ID to ensure the IDs are passed correctly in their original form. You can find more information about the formula here.

Create the formula by selecting New Resource on the side menu. Select:

• Provide name and description.
• Data Type: Text.
• Formula: CASESAFEID({!FindPathID}).

Next, create another formula field:

• Provide name and description
• Data Type: Text
• Formula: CASESAFEID({!GetStudentID})

7 Add a new Assignment-element to assign the Path ID and Student ID

Next, store the path and student IDs associated with the path item completion by assigning them as variables. You can do this by selecting the + icon in the flow. 

From the Add Element menu under Logic select Assignment.

Assign the Path ID and Student ID to the variables you created above (but not the formula fields created in Step 5):

• Path ID:
  • Variable: FindPathID
  • Operator: Equals
  • Value: Record > Path Item > Path
• Student ID:
  • Variable: FindStudentID
  • Operator: Equals
  • Value: Record > Path Progress > Student > ID

8 Add Get Records elements to the flow

Add ‘Get Records’ element to the flow, where you create a list of all path items that the student has completed in that path.

Select:

• Provide Label and Description
• Object: ‘Path Item Progress’
• Condition Requirements: All Conditions Are Met (AND):
  • Field: Student_ID__c
  • Operator: Equals
  • Value: StudentIDFormula (from Step 6)
• Field: Path__c
• Operator: Equals
• Value: PathIDFormula (from Step 6)
• How many records to store: All records
• How to Store Record Data: Automatically store all fields

9 Create a number variable to store the number of completed path items by the student. 

Similar to Step 5. Make this variable available outside the flow:

10 Add another ‘Assignment’ element to the flow and assign the number of completed path items to the variable

Add another ‘Assignment’ element to the flow and assign the number of completed path items to the variable created in Step 9. You can do this by taking the count of records retrieved in Step 8. Select:

• Variable: ‘NumberofCompletedPathItemsbyStudent’ (variable name from Step 9)
• Operator: Equals Count
• Value: {!Get_Path_Progress_for_the_Path_ID_and_Student_ID} (record collection name from Step 8)

11 Add an Update Records element to the flow to update the Courses_Complete field in the Path Progress object

Select:

• Provide Label and Description
• How to Find Records to Update and Set Their Values: Specify conditions to identify records, and set fields individually
• Object: ‘Path Progress’
• Condition Requirements: All Conditions Are Met (AND):
  • Field: Student_ID__c
  • Operator: Equals
  • Value: PathIDFormula
• Field: Path__c
• Operator: Equals
• Value: StudentIDFormula
• Ser Field Values for the Path Progress Records:
  • Field: Courses_Complete__c
  • Value: {!NumberofCompletedPathItemsbyStudent} (from Step 9)
• How many records to store: All records
• How to Store Record Data: Automatically store all fields

Finally, save the flow and Debug it to ensure everything runs as expected. Once the flow has been activated, you should see the ‘Courses Complete’ variable being updated in the Path Progress object any time someone completes a path item on Skilljar.

Path Progress %

Pull the ‘Total Courses' from the Path Progress object to calculate the ‘Path Progress-%’ on the Path object. You can do this by creating a new formula field on the Path Progress object using the Object Manager:

Now that we have the two variables, ‘Course Complete’ and ‘Total Courses’ on Path Progress object, create a formula called ‘Path Progress-%’ by creating another formula field to make the division:

Once you have the flow activated and the formulas created, whenever a student completes a path item it should automatically update the ‘Path Progress-%’ field.