When Things Look The Same, But Are Different
Have you ever picked up someone else’s phone by accident, thinking it was yours?
Sometimes our technology can look the same even when it is different. Same goes for our data.
So when data looks the same but is actually different, how can we avoid being parent-trapped by it? If you have two pieces of data or collections of data that have nothing to differentiate them, then those are the same data. But in most cases, even when they look suuuuuuper similar, there is a unique something that gives you the key to telling them apart.
In a log-in database system, like a CRM like Salesforce, each record gets a unique ID. It could be just number combos or letter and number combos (and always watch out for the case-sensitive ones, because sometimes “123abc” is different from “123abC”, and sometimes it isn’t).
But those IDs can often be long and totally not useful for most of your work - NAMES of records are used more frequently to differentiate your data. The record called “Emily’s 2022 Grant Application” and “Emily’s 2023 Grant Application” can be more helpful than their unique IDs. But Emily is such an overused name (sorry, Mom, but can I have a new name??) - how many Emily’s applied for grants this year? Guaranteed more than 1. Add in last name? OK, “Emily Smith’s 2023 Grant Application” - but you see the issue with that already.
What to do, what to do.
I ran into this issue at work. We have a bunch of records that are all going to be named the same thing. It wasn’t such a big deal in the past, but now we are searching for those records a lot more and needing to choose the right one among them. Their record IDs might differentiate them, but no one wants to see a bunch of long strings of numbers and letters in their record names.
My mind turned to INCREMENTS (an increase or addition, especially one of a series on a fixed scale). What if I just had the first record created have the number 1 in the name, then the second one have the number 2, then 3 and onward and upwards. But in my use case this might be confusing because sometimes our records look like (1 of 3), (2 of 3), (3 of 3), etc. Fine, if numbers won’t work, how about letters? Sure, we can give it a try.
USE CASE: I need a differentiator in my Salesforce record names, there is nothing else unique about these records that I can use, and the unique thing can’t be numbers that just go up.
SOLUTION: Create a flow that will add a number to each record created, starting with 1 and incrementing up when the record is basically the same data, and then convert that number to a letter.
STEP-BY-STEP:
Create new field on my record called “Increment Number”
Create Flow that will add the right value to the “Increment Number” field for each new record. Here is how I did it:
Flow begins when a new record is CREATED only
Get all the records that are “sister” records of this one - in this particular case it is all Opportunity Products related to the same Opportunity (called “Engagement” in screenshot). Also particular to this case, we are only getting the records related to the same Opportunity that also have the same value in a field called “Training Curriculum”.
Count how many records got got. If it is the first record of its kind to be created, the count will be 1. If there are any subsequent records created linked to the same Opportunity with the same Training Curriculum, it will be more than 1.
We need to store the information about the count of records somewhere. I made a variable called “Count of Get Records” because I’m lazy with my naming. I’m so lucky that Salesforce knows I am lazy, because they gave me a two-for-one. They made it super easy to count the records and assign that count to a variable all in one step.
Now we have a decision that leads us down two different paths. If it is the first record of its kind, we go one way. If a record like it exists already, we go another way.
If the count of records is only 1 (first record of its kind) then we update the record so that the value in the “Increment Number” field is set to 1. It’s the first one! I’m one of one, I’m number one, I’m the only one.
If there is already a record like it that exists, then the count will always be more than 1, because what started the flow was creating this second (or third, or fourth, etc) record of its kind.
In this instance, we enter a loop. In a loop, Salesforce looks at each record in that collection of records that we got above. And then for each record, it performs the next action.
For each record, we look at the value in “Increment Number” and assign that number to a variable that exists only in the loop. I called this variable “Next Up Increment” because I like to have fun at work. So if we have 1 existing record like the one we just created, it already has a value of 1 in the “Increment Number” field. Now my variable called “Next Up Increment” is set to equal 1. If we had two records, then when the loop came back to the second record, it would replace the 1 with whatever is in the “Increment Number” field. This variable can only hold 1 number at a time (unlike the variable in our next step).
Then for each record in the loop we ADD the number from the “Next Up Increment” to a variable that is a collection of numbers (it can have more than 1 number in this variable, unlike the variable from the previous step) that I have cleverly named “Increment Collection”. So it is invisible, but the values in this variable will look like this: {1,2,3} etc.
Now I have a collection of all the Increment Numbers on my records. If I had 5 existing records that all looked the same, then my “Increment Collection” would look like {,1,2,3,4,5} - there would be a blank one to start, because the loop does include the record that I just created that starts the flow, but no value is in the “Increment Number” field on that record yet, that is what we are doing now!
The thing I want to do is identify the largest Increment Number that we have on a record so far, then update my new record to have an Increment Number that is one more than the last one. So first I sort the ”Increment Collection” from largest to smallest. But here is the trickiest trick of them all. In this sort you can choose to set the maximum number of items kept in the collection after the sorting happens, and I can choose to only keep 1 (one) item. So that means that the sort first makes my collection have the largest number first - {5,4,3,2,1,} - and then it only keeps the first value in the collection, which is 5 in this example. Now my collection has only 1 number in it - the largest “Increment Number” of all the similar looking records.
Then I get into another loop - looping through the Increment Collection. But what kind of a loop is this if there is only 1 number in my Increment Collection? Not a very long one thank you very much. It just has 1 value to go through. But hey, nothing wrong with that, it is what I need.
Just like my previous loop, for each record I am taking the value that the loop is on and assigning it to a variable. I’ve made another oh so cleverly named variable (“Max Increment”) - this is the kind of variable that can only have 1 value in it. We set that value to the number we get from looping through the “Increment Collection” (and because we limited the “Increment Collection” in the previous step when we sorted it highest to lowest to keep only the first value, we will always only be adding that one singular value to the new “Max Increment” variable).
Almost done!! Now when we exit the loop after the last item it runs through (which is always after only 1 item because we had only 1 value in our “Increment Collection”) we want to update the record that started this whole thing off. We want the “Increment Number” field value to be set to one higher than the largest existing “Increment Number” on a similar looking record. In our example, the largest number was 5.
So I’ve made one more variable - this time it is a FORMULA. The formula takes the value that we assigned to the “Max Increment” variable as part of the flow and adds 1 to it.
And then finally, we update the record that kicked off the whole process with the value of that formula. Since our flow set the variable “Max Increment” to be 5, adding 1 to it makes it 6.
It is totally possible to convert the numbers to letters within this Flow. But I just decided to go a different way. A better way? No. A shorter way? No. A simpler way? No. Truly this way is just different way than doing it in the flow. No judgment at all.
I have another field on my record I called “Increment Letter” and it has a dumb yet workable formula powering it:
I also have a formula powering the Name of my records. So I added the “Increment Letter” field to that formula. And now, finally, after all of that, two records that otherwise would have had the exact same name and been really, really hard to distinguish, are now distinguishable:
