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 running on a daily basis. 

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. 

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 a schedule

For the start configuration, select the following values:

• Set a schedule: Pick a Start Date and Time, and the Frequency (daily, weekly)
  
  
  
• Choose Object: ‘Path Progress’
• Condition Requirements: ‘None - Run For All Path Progresses’

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 {!$Record.skilljar__Path__c}
• Student ID:
  • Variable: FindStudentID
  • Operator: Equals
  • Value: Record > Student ({!$Record.skilljar__Student__c})

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)
  • AND
  • Field: Completed_At__c
  • Operator: Is Null
  • Value: False
• Sort Path Item Progress Records: 
  • Sort Order: Ascending
  • Sort By: skilljar__Path_Item__c
• How many records to store: All records
• How to Store Record Data: Automatically store all fields.

9 Remove duplicated path item completions

Next, we need to remove duplicate path item completions for students from the list we created in Step 8 above. This way the path progress-% will never exceed 100%, even if the student has completed the same path item multiple times.

Since we have already sorted the list by Path Item ID, we can compare each Path Item ID to the previous one and keep only the unique completions. First, we will add a variable to store the previous ID. We’ll leave it empty in the beginning:

Second, we need to create a new Collection Variable that will be the final list containing only unique path item completions. This list will be empty in the beginning:

Next, add a ‘Loop’ element to check for duplicates.

Select:

• Collection Variable: {!Get_Path_Progress_for_the_Path_ID_and_Student_ID} (from Step 8)

Next, add a ‘Decision’ element where we have two outcomes depending if a duplicate is found. In the outcome for when there’s a duplicate value add the following condition to compare the IDs of the current loop item and the previous record:

• Condition Requirements: All Conditions Are Met (AND):
  • Field: {!StorePreviousPathItemID}
  • Operator: Equals
  • Value: {!Remove_Duplicates_from_Path_Item_Progress_List.skilljar__Path_Item__c}

If there is a duplicate (‘Yes’ outcome in the above screenshot), we can leave this branch empty in the flow. 

If there was no duplicate (‘No’ outcome), we will add the record to the final list (‘PathItemCompletionsList’) by adding an ‘Assignment’ element. Select:

• Variable: {!PathItemCompletionsList}
• Operator: Add
• Value: {!Remove_Duplicates_from_Path_Item_Progress_List}

The last step in the ‘Loop’ is to update the previous path item ID variable ({!StorePreviousPathItemID}) by adding an ‘Assignment’ element after the outcomes. Select:

• Variable: {!StorePreviousPathItemID}
• Operator: Equals
• Value: {!Remove_Duplicates_from_Path_Item_Progress_List.skilljar__Path_Item__c}

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

Create a new variable, similar to Step 5: 

11 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 10. You can do this by taking the count of records retrieved in Step 9. Select:

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

12 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: Path__c
  • Operator: Equals
  • Value: PathIDFormula
  • AND
  • Field: Student_ID__c
  • Operator: Equals
  • Value: StudentIDFormula
• Ser Field Values for the Path Progress Records:
  • Field: Courses_Complete__c
  • Value: {!NumberofCompletedPathItemsbyStudent} (from Step 9)

Finally, save the flow and debug it to make sure everything runs as expected. Once the flow has been activated, you should see the ‘Courses Complete’ variable being updated in the Path Progress object whenever 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.