Today we will teach you how to create your own Line Item Numbers in Salesforce.
We recently had a need to add Line Item Numbers to the Salesforce Campaign Members related to a Campaign. At first thought, one would think to use an “Auto Number” field on the Campaign Member object but this field assigns numbers sequentially across all records in an object, so the numbers for each record would just keep increasing across all Campaign Members.
What we actually want is for the numbers to start over for each Campaign, so as Campaign Members are added, the numbers increase for that Campaign. Then, to add more complexity, we needed the numbers to recalculate as records are added or removed, so we do not have gaps in the sequence. After doing a bunch of research, it seemed the only option was to use Apex code, since the records needed to be updated each time a record is added or removed from the list.
As it turns out, this functionality is quite easy to create using standard Salesforce automation, including Process Builder and Visual Flows, with a little help from a Roll-Up Summary field. We built this on the Campaign/Campaign Member relationship but it can also be used for Quotes and Quote Line Items, Accounts and Opportunities, or anywhere else where a Roll-Up Summary field is available. For other relationships, like Accounts and Contacts, the free DLRS application will give you the ability to count all related records (and more) from the parent record.
The first thing you need to do is create a couple of fields. On the Campaign object, create a Roll-Up Summary field called “Total Members” that counts all related Campaign Members. This field gets updated each time a Campaign member record is added or removed, which allows us to fire a Process Builder process off of it. Then, on the Campaign Member object, create a new “Member Line Item Number” number field with zero decimal places. You may want to make this field read only or remove it from page layouts so it cannot be edited by a user.
Now for the fun part; creating the Visual Flow. Each time a Campaign Member is added to a Campaign, we need to look at all associated members and assign sequential numbers, based on the order that they were added. Below is a picture of the Flow as a whole. This needs to be saved as an Auto-Launched Flow to work properly.
Before creating any steps, you will want to create a few Variables. Below are the ones referenced in this Flow:
- “VarCampaignID” (Text, In/Out) to hold the Campaign ID from the record that started the Flow
- “VarOne” (Number, 0, In/Out) with a default of “1” that we will use to increment for each Campaign Member
- SOBJECT Variables
- “SoMember” (In/Out) with an Object Type of Campaign Member for our first Assignment
- SOBJECT Collection Variables
- “CollCampaignMembers” (In/Out) with an Object Type of Campaign Member for the Fast Lookup to populate and the Loop to pull from
- “CollUpdatedMembers” (In/Out) with an Object Type of Campaign Member for the second Assignment to fill and the Fast Update to pull from
Fast Lookup Step
Now that the resources are created, the steps can be created. The first item is the Fast Lookup. This will be the start of the Flow. It needs to look up to the Campaign Member where the Salesforce Campaign ID equals the value in the “VarCampaignID” variable. We want the Line Item number applied by when the member was added so this step is sorting by the Created Date in Ascending order.
Select the “CollCampaignMembers” SO Collection Variable for the “Variable” field to hold all Campaign Member records found and then select the “ID” to store in the variable. See the screen shots below for this step:
Now we need to loop through the collection of Campaign Members found, one record at a time, and assign it to a new variable so we can edit its fields. Create a Loop step to Loop Through the “CollCampaignMembers” SO Collection Variable in ascending order and set the Loop Variable to “SoMember”. See the screen shot below:
Now we need to edit the single record from the Loop. Create an Assignment step and in the “Variable” picklist, work your way down through this path: SOBJECT VARIABLES > SoMember > Member_Line_Item_Number__c (or whatever you named your field to hold the line item number). Set the Operator to “equals” and then select the “VarOne” variable for the Value. See the screen shot below:
This could have been done in one assignment but having two allows better clarity on the actions. The first step set the first Campaign Member’s line item number to “1”, the default in the VarOne variable. This step adds 1 to the VarOne variable and assigns the updated record from the first assignment to the collection of records to be mass updated. Then, the Flow goes back to the Loop for the second record, adding “2” to the line item number field. The process repeats until all records have been updated. See the screen shot below.
After all of the Campaign Member records have had their line item numbers assigned, the flow is ready to mass update the records. It does a mass update of all records in the “CollUpdatedMembers” SO Collection Variable. See the screen shot below:
Now, we just need a Process Builder Process to fire the Visual Flow. I created a Process on the Campaign object with a Criteria Node that checks to see if the Campaign “Total Members” Roll-Up Summary field has changed AND the field is greater than zero.
Then, for the Immediate Action, I have it launching the Flow and passing the Campaign ID to the “VarCampaignID” variable. Save and activate the process and then add or remove child records to see it in action. Below is a screen shot of this in action on a small campaign:
StarrData helps you get the most out of Salesforce. If you would like information on the services we offer, call us at (888) 391-4493 x101.