1

I am trying to create a validation rule, which says the following:

If any value is selected in the multiselect picklist, then at least two of the following 4 fields must be filled in.

Multiselect picklist Status = Accepted, Rejected, Transferred

4 Percentage fields Grade 1 Grade 2 Grade 3 Grade 4

I created the following validation rule, but have no idea how to treat the mutliselect picklist field, or the rule that at least 2 fields need to be selected. If possible, it would be great to include, that the sum of all 4 Grades must be 100%.

IF( Status__c ,  OR( Grade1__c , Grade2__c ,  Grade3__c ,  Grade4__c , 0)

Tia, Lily

1
  • I have some ideas for a solution, but I need more clarification on what you are asking for. -First, the Status field: is it a mulit-select picklist or just a picklist. I would assume just a picklist since it seems unlikely that something can be both Accepted and Rejected. -Second, your formula: Are you saying if Status field is filled in, than atleast 2 out of the 4 percentage fields (Grade 1-4) need to be filled out, and that those fields need to tally to 100%?
    – Evan
    Sep 28 '18 at 15:18
1

Formula functions to the rescue!

The documentation is a bit obtuse to navigate (Salesforce, y u no provide anchors so that when I click on a function I am taken to that function instead of the top of a page with an entire half of the functions?), but using your browser's search to find key terms (like multi-select picklist) generally helps narrow things down.

The INCLUDES() function works on multiselect picklist fields, and returns TRUE if the specified text literal is one of the selected values. The downside to this is that you can only test for one value at a time.

The ISBLANK() function also works on multiselect picklists, which will be needed to short-circuit the rest of your validation rule (if Status__c is blank, then you can ignore the rest of the rule).

Counting how many of the grade fields have been populated requires a bit of function composition. The first thing that comes to mind here is to use IF() functions to calculate your value, along with ISBLANK() to test if a field is populated...something like

(2 <
    0 +
    IF(ISBLANK(Field_A__c), 0, 1) + 
    IF(ISBLANK(Field_B__c), 0, 1) + 
    IF(ISBLANK(Field_C__c), 0, 1) + 
    IF(ISBLANK(Field_D__c), 0, 1)
)

This expression starts with a 0 value (which conceptually represents how many fields are populated), and adds 0 if a field is blank, and adds 1 if the field is not blank. Pretty straightforward. This will directly evaluate to either TRUE or FALSE, and you can fairly easily plug that into logic for the rest of your validation rule (which expects a Boolean result) without the need to enclose it in another IF().

AND(
    <expression to test if multipicklist is empty here>,
    <expression to count number of grades populated here>
)

Finally, as for validating that the grades add up to 100%, I'd actually suggest making that into its own validation rule. The reason I suggest that is that

  1. Adding this makes your validation rule more complex and thus harder to understand
  2. You only get to specify one error message per validation rule, and

X is filled but either you didn't populate at least 2 fields or the grades don't add up to 100%

Is not as clear as if the messages are separated

X is filled, you must populate at least 2 of A, B, C, D

and

Grades do not sum to 100%

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.