Create your own Line Item Numbers in Salesforce

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 removed, so we do not have gaps in the sequence.

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 (objects with a Master-Detail relationship). For Lookup 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:

Variables:
- VarCampaignId holds the Campaign Id from the record that starts the Flow
  - Resource Type = Variable
  - Name = VarCampaignID
  - Data Type = Text
  - No Default Value
  - Check Available for input

VarOne holds the new Campaign Member Line Item Number
- Resource Type = Variable
- Name = VaOne
- Data Type = Number
- Decimal Places = 0
- Default Value = 1

SObject Collection Variable:
- Resource Type = Variable
- Name = CollUpdatedMembers
- Data Type = Record
  - Check Allow multiple values (collection)
- Object = Campaign Member
- Check Available for output

Now that the resources are created, the actions can be created.

Get Campaign Members action

The first action is to get all the Campaign Members for a specified Campaign. This will be the first action of the Flow, connected from the Start element. It needs to look up all the Campaign Member records 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.

Loop action

Now we want to loop through the collection of Campaign Members, one record at a time, and update their line number. Drag a Loop element onto the canvas, and configure it to loop through the Get_Campaign_Members collection variable just created. Be sure to accept the default Direction of First item to Last item.

First Assignment action

Now we need to edit each record in the Loop. Drag an Assignment element onto the canvas. For Variable, first, select “Current Item from Loop Loop_Campaign_Members. In the list of Campaign Member fields, select “Member Line Item Number” (or whatever you named your field to hold the line item number).

Set the Operator to Equals, and for Value, select VarOne. On the first record processed in the loop, Line Number is set to VarOne’s default value of 1. Your Assignment action should look as follows.

Second Assignment action

This action could have been part of the first action, but keeping it separate provides better clarity on the actions. The first action set the first Campaign Member’s line number to “1”, the default in the VarOne variable. This action adds 1 to the VarOne variable before the next record is processed.

It also adds the record in the loop to CollUpdatedMembers for the update action. For value, select “Current Item from Loop” as shown.

Update Records action

After the loop has processed all of the Campaign Member records in the Get_Campaign_Members collection, the flow is ready to mass update the records that have been added to CollUpdatedMembers. Drag the Update Records element onto the canvas, and select the CollUpdatedMembers collection for update.

Your flow is ready! Save the flow with a logical name such as “Set Line Numbers on Campaign Members”, and Activate the flow.

Call the Flow from a Process

The final step is to create a Process Builder process which calls the flow when the Roll-Up Summary field on a Campaign record changes. We called the field “Total Members” above.

Create a new Process to act on the Campaign object, whenever a record is created or edited. Set the action Criteria to Total Members (or your field name) Is changed equals True.

The criteria’s Immediate Action is to call the flow you just created. So select Immediate Action Type of Flows, give it an Action Name, and select your Flow. If your new flow isn’t listed, it probably hasn’t been Activated.

Finally, set the Flow Variable. Select VarCampaignId from the flow, set Type as Field Reference, and for Value select Campaign Id.