Context: I have an Object named Objective__c which has a lookup field to itself named "Parent Objective". I also have a field named "Level" which has 4 types of levels. Using these 2 fields, a user can create a kind of hierarchical framework of objectives.

The requirement: For every Objective record, I would like to initiate a numbering system for it and have all child records follow on from that numbering system.


Impact 1
   Outcome 1.1
   Outcome 1.2
      Output 1.2.1
   Outcome 1.3
      Output 1.3.1
      Output 1.3.2
Impact 2
   Outcome 2.1
   Outcome 2.2
      Output 2.2.1
   Outcome 2.3
      Output 2.3.1
      Output 2.3.2

Users could build this hierarchy in any order or starting from any level.

What would be the best and easiest way to achieve this?

  • You mention a field with "4 types" of levels. What are the field types for these levels? Are they integers or something else? Off hand from your example, it appears that everything could be done using RegEx formulas based on the Level IF the Level is an Integer. Otherwise, you'd need to convert it via some kind of conversion to a either an or enum to integer, then use looping or Case if doing it in Apex. Formula with RegEx if creating it in some other context.
    – crmprogdev
    Jan 18, 2020 at 14:23
  • @crmprogdev The "Level" field is a picklist value with these 4 values: Impact, Outcome, Input, Output. The Hierarchy should also be in the order that I listed. I would like a numbering system that uses the decimal number system to reflect the hierarchy, just as I depicted in my example. I'm not entirely sure what you mean by using RegEx for this. I am fairly certain that I will need some automation to run on insert to check for existing records on the same Level and follow on from the numbering system. Maybe you could give me an example of what you thought I could use? Thanks!
    – JustinF
    Jan 18, 2020 at 21:57
  • What do you mean by users could build this hierarchy? Do you mean if user created child of child record, value of the field should be Output 1.2.1? Could you please explain in detail Jan 19, 2020 at 11:39

1 Answer 1


Let me see if I can quickly "rough" this out for you. I'm going to assume by "Level", you mean that an Impact is at the Root level of the Hierarchy, an Outcome is what's below an Impact, an Input is a child that has a lookup to something above it, and an Output is a child of an Input. If that's not correct, you'll need to adjust accordingly. That's the only thing that makes sense to me from what I can see from your example. Its clear there can be a List <Impact>, etc.

To learn about RegEx (Regular Expressions), I recommend you visit GSkinner's Site which has excellent tutorials and on-line help for how to use them. They're very helpful in searching for and replacing text using pattern matching. You can use them for a number of things developers frequently do in Salesforce related to formulas, like validation rules, workflow rules, pattern matchers, string methods and more.

It appears that your issue is going to be one of first creating a map of Impacts to list<Outcome> for a given list<Impact> for a single "Ultimate Parent" Objective_c (the one at the root). Clearly, each Impact can have a list<Outcome>. Below each Outcome record, you'll have a list<Input>. Each of those Input records should return a list<Output> that would include all of your 3rd level Output records.

In essence, you should have a set of loops based on what's below:

list<Outcome__c>Impact = new list<Outcome__c>(); //--> Outcome__c where Level == 'Impact'
list<Outcome__c>Outcome = new list<Outcome__c>(); //--> Outcome__c where Level == 'Outcome' 
list<Outcome__c>Input = new list<Outcome__c>(); //--> Outcome__c where Level == 'Input'
list<Outcome__c>Output = new list<Outcome__c>(); //--> Outcome__c where Level == 'Output'

    = new Map<list<Outcome__c>,list<list<Outcome__c>>>();
// notice that you have a map of a list to a "list of list" in the above
    = new Map<list<Outcome__c>,list<list<Outcome__c>>>();
// The keyset() will have child lists of Outputs you will need to iterate on
// You will also need to iterate on the keysets() from below for each Output
// where there is a record that matches to get it's lists of children as well.
// This is where using RegEx may be helpful to you. Is up to you.
    = new Map<list<Outcome__c>,list<list<Outcome__c>>>();

You're now going to need to iterate on each of the keysets within the appropriate loops. If you use integers to track the progression within each of the sub loops, you can then assemble your hierarchy numbers as you've used them now without RegEx. BUT to do that, you'll need to declare your integers before your loops and carry them into the next sub loop while the current loop is running without losing context. The latter can happen at the end of a loop if a variable is reset.

You'll want to create your "list of lists" from Trigger.old first, then add the values from trigger.new.

I'll leave the remainder for you to sort out in terms of the nested loops. There have been plenty of other similar questions on how to process data using loops.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .