3

I need to field(read only) Field1 that auto populates in the following way:

Field1 = Sum of number of Field2 selected *Field3

Where Field2 is a multiselect. I know this can be done through triggers where I can count how many values of Field2 selected. I was however curious to know if, instead ,i can use a formula field to achieve this. As the multiselect values are stored in colon separated format, so if salesforce provides the options of counting the number of colons in a formula field, my task would become easier.

2 Answers 2

2

Yes that can be done. The method is a little unorthodox but it works.

You will have to check each value that exists within the multi-select picklist and if the particular value is selected then add 1 else add 0. This will not work if you have a large number of values within your field due to compile size. Look at the example I have provided below.

(IF(INCLUDES(Test_Multi__c, "A"), 1, 0) + 
IF(INCLUDES(Test_Multi__c, "B"), 1, 0) + 
IF(INCLUDES(Test_Multi__c, "C"), 1, 0) + 
IF(INCLUDES(Test_Multi__c, "D"), 1, 0)) * Custom_Field_3__c

You will also want to wrap all of the addition IF statements within parenthesis before performing the multiplication.

One thing I would advise is trying to utilize checkboxes instead of multi-select picklist fields. But if you would prefer to continue using the multi-select than this formula will do the job.

2

A formula is usually not the appropriate way of counting the selected items in a multi-select list, due to the limitations of the formula syntax. It's not maintainable, as you have to hard-code the values you're looking for into the formula, and field references are pretty expensive. If it's an option, simply use a trigger:

trigger sumFieldCount on Some_Object__c (before insert, before update) {
    for(Some_Object__c record: Trigger.new) {
        record.Field_1__c = record.Field_3__c * (record.Field_2__c == null? 0: record.Field_2__c.split(';'));
    }
}

You must log in to answer this question.

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