Assign Field based on Alpha Split of Contact Last Name

Question

Ok, so I have been searching for a while and I cannot locate anything that helps me resolve this scenario.

Business Scenario: I have a field "OSF Counselor" that needs to be assigned to a specific counselor based on the contact's last name. A-E = Counselor 1, F-L = Counselor 2, M-R = Counselor 3, S-Z = Counselor 4.

I am not that familiar with Flow formulas, but I believe I need to make a variable in a flow in order to identify the appropriate "collection" of records that match the alpha split and then update the record accordingly. The problem is I am not sure which function/formula to use to basically state the use case above.

Any support on how to start that formula would be most helpful, or if you know of what resources I can use to begin, that would also be helpful.

Answer 1

A formula isn't really scalable (ex. when you have more conditions, more counselors, to make frequent changes, etc), but if you simply have 4 conditions - it can work. You'd leverage IF, CONTAINS, and LEFT to check the first letter of the last name. CONTAINS() allows you to use : between different values in the text argument to check against.

IF(
    CONTAINS("A:B:C:D:E", LEFT(Contact.LastName, 1)),
    "Counselor1Id",
    IF(
        CONTAINS("F:G:H:I:J:K:L" LEFT(Contact.LastName)),
        "Counselor2Id"
        IF(
           ....and so on
       

Important to call out that IF expects IF(logical_test, value_if_true, value_if_false) so there will need to be a default counselor if no conditions are met. The example screenshots are from a Record-Triggered Flow on Contact - your use-case may be slightly different (auto-launched flow, scheduled flow, etc) and necessitate a loop if you have a list of records to act on within the Flow.

The negatives of this approach would be:

• Having to version your flow every time you need to update this assignment
• Not easily accessible to see your assignment details - have to go into Flow builder.
• Will get unwieldy if you have lots of counselors. Formulas have a text limit

---

A different solution could leverage Custom Metadata Types and Flow to store a table of your assignments.

• Create a Custom Metadata Type called Counselor_Assignment_Rule__mdt
• Create a custom field for "Counselor Id"

Then, create the necessary data to correspond to assignments

• Click "Manage Counselor Assignment Rules"
• Click "New" to create a record
• Create a record for each letter & counselor

Now, in the Flow you can simply "Get Records" that match with your current record and assign it.

Answer 2

In addition to Kris' answer

CASE(LEFT(Contact.LastName,1)),
   'A','Counselor1Id', 
   'B','Counselor1Id', 
   ..., 
   'F','Counselor2Id', 
   'G','Counselor2Id', 
   ... 
   NULL /* or 'defaultId', by definition, Contact.LastName is non-null but could have accented UTF-8 value or be a non-Latin character or even start with a number!
)