Condition to determine if field value in one model is selected in multipick field in another model

  • 1
  • Question
  • Updated 12 months ago
  • Answered
Model A has a Multipicklist__c field.
Model B has a Picklist__c field.
Both use the same value set.

Is there a way I can write a condition on Model B to return records where the value of Picklist__c is one of the selected values in the Multipicklist__c field in the first row of Model A?

I would have expected 'in' to work, but apparently 'in' acts like '=' over multiple rows?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb

Posted 12 months ago

  • 1
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Hmmmm .... difference in what's being compared. Single value vs array of values. 

Did you try Contains?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Just tried 'contains', but it doesn't work.

If the value in Multipicklist__c is "This;That"

And the values of Picklist__c are:
1. "This"
2. "That"
3. "The other thing"

I want to return rows 1 and 2.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
I have run up against this before. Multi-picklists can be the devil. Consider:
Salesforce formula fields can determine if a value exists in a multi picklist. So let’s say you have 5 items in your multi-picklist. You could have 5 salesforce formula fields that return true if the value you are looking for is in the picklist then build conditions on those 5 Boolean fields.
You may be able to create one field that expresses all picklist choices in a comma separated list and then use the contains feature of actions to determine if the text field contains the value you are looking for.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
What's the object relationship?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Hmm. Interesting idea, Raymond. The series of conditions on boolean fields would work, as I only have 4 options in the value set.

No relationship between the objects.

This did force me to rethink the use case a bit... I was going to use two models to display information for the "in" and "not in", but I'm thinking I'll just return all the rows in Model B, and then write a skuid formula field on that model as my "condition", like so:
IF(CONTAINS({{$Model.SelectedMenu.data.0.Default_Profile_Visibility__c}},{{Profile__c}}),"fa-eye","fa-eye-slash"). 
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
Working?

Assume Model A will only have the one row then?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Yes, that's right. Thanks!
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Also, you guys rock.
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Hi Matt, Pat and Ray. I know you all have a very strong understanding of this already, and it sounds like you may have a workable approach, but I wanted to mention that Skuid Millau has a couple new formula functions that sounded like they might come in handy for a scenario like this. I'm not sure if the field's type will come into play or not, but this might be useful either way. Here's the relevant bit from our release notes:

New formula functions allow for more precise text analysis:
  • CONTAINS_ANY determines if a string contains any of a list of provided parameters.
  • CONTAINS_ALL determines if a string contains all of the provided parameters.
  • CASE returns different results based on whether the parameter meets the specified criteria, meaning no more complicated chains of IF functions. This function also allows for optional default values to use if none of the provided values match.
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
All,

Skuid has a Case statement before Salesforce implements this in Apex!

Thanks!

Bill