5

I would like to ask something about FORMULAS.

I have a multipick list field where the user select one or more options(text), i would like to count how many options he select and multiple that for one value...how can i do that?i don t find any function that let me count the number of values that he select...

Thanks in advance

8

This blog gives an example of how to do this. The idea is that the formula knows all the possible values of the picklist. Example: if your multi-select field was called Colors and had 3 possible values in it then the formula would be:

IF(INCLUDES(Colors__c, 'Red'), 1, 0) 
+ IF(INCLUDES(Colors__c, 'Green'), 1, 0) 
+ IF(INCLUDES(Colors__c, 'Blue'), 1, 0) 

The downside of this is that the formula needs to be kept in sync with the picklist values. For Example if you added a 4th Color called Yellow then the above formula wouldnt count this value.

Implementing this in a trigger may be a better solution as you will be able to add/remove/edit the picklist values without having to modify the logic that does the count. The logic would be something like:

trigger CountingValues on MyObject__c (before insert, before update) {

    if (Trigger.isUpdate) {
        for (MyObject__c mo : Trigger.new) {
            if (mo.Colors__c != Trigger.oldMap.get(mo.Id).Colors__c) {
                if (String.isNotBlank(mo.Colors__c) {
                    List<String> colors = mo.Colors__c.split(';');
                    mo.ColorsCount__c = colors.size();
                } else {
                    mo.ColorsCount__c = 0;
                }
            }
        }
     }
}

The above code is untested but it will give you an idea of how to implement the trigger

| improve this answer | |
  • You think that the only option? because he can select for a lot of fields and if he add a new filed we will need to change the formula.... but thank you very much Bart – Tomas Q. Nov 20 '13 at 12:09
  • I will update my answer with the Trigger solution as well. I dont know any other way to do it with a formula – BarCotter Nov 20 '13 at 12:13
  • 1
    Sorry for the drive-by comment, by if Colors__c is not null, the size() is not zero. Fields are always null when empty, and never have size() of 0. – sfdcfox Nov 4 '14 at 6:24
0

This is awesome. I have a use case where we need to have counters for multiple Objects and Multi-Select Fields.

I took the trigger code from above and used it in a dynamic class. This way you can call a method, and pass the Object, Multi-Select and Counter Fields in from a trigger. This allows you to replicate the functionality across many Fields and Objects

Class

public with sharing class MultiSelectCounter {

public void getCount(string mspField, string objectName, list<string> recordIDs, string counterField){
    list<sObject> recUpdates = new list<sObject>();
    list<sObject> recQuery = database.query('Select ID, '+mspField+', '+counterField+' FROM '+objectName+' WHERE ID IN :recordIDs');    
        for(sObject sObj : recQuery){
            if(String.isNotBlank(String.valueOf(sObj.get(mspField)))){
                List<String> mspSize = String.valueOf(sObj.get(mspField)).split(';');
                sObj.put(counterField, mspSize.size());
                recUpdates.add(sObj);
            }
            else{
                sObj.put(counterField, 0);
                recUpdates.add(sObj);
            }
        }
        update recUpdates;
}
}

Trigger on Case where I'm looking at two Multi-Select Fields:

Activities_Skill_Types_Completed__c, Activities_Skill_Types_Counter__c 

and two Counter Fields:

of_Activities_Skill_Types_Completed__c, of_Activities_Skill_Types_Started__c

Full Trigger Code

trigger MultiSelectCounterCase on Case (after insert, after update) {

list<String> startedIDs = new list<String>();
list<String> completedIDs = new list<String>();

list<Case> newCases = [SELECT ID, Activities_Skill_Types_Started__c, Activities_Skill_Types_Completed__c, Of_Activities_Skill_Types_Started__c, Of_Activities_Skill_Types_Completed__c FROM Case WHERE ID IN :trigger.new];
if(trigger.isUpdate){
    for(Case newCase : newCases){
        Case oldCase = trigger.OldMap.get(newCase.ID);
        if(oldCase.Activities_Skill_Types_Started__c != newCase.Activities_Skill_Types_Started__c){
            startedIDs.add(newCase.ID);
        }
        if(oldCase.Activities_Skill_Types_Completed__c != newCase.Activities_Skill_Types_Completed__c){
            completedIDs.add(newCase.ID);
        } 
    }

}
if(trigger.isInsert){
    for(Case newCase: newCases){
        if(newCase.Activities_Skill_Types_Started__c != null){
            startedIDs.add(newCase.ID);
        }
        if(newCase.Activities_Skill_Types_Completed__c != null){
            completedIDs.add(newCase.ID);
        }
    }       
}
MultiSelectCounter msc = new MultiSelectCounter();
msc.getCount('activities_skill_types_started__c', 'case', startedIDs, 'of_activities_skill_types_started__c' );
msc.getCount('activities_skill_types_completed__c', 'case', completedIDs, 'of_activities_skill_types_completed__c' );

}
| improve this answer | |

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.