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

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(';'));
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.