Comparing Multi-Selects
Looking for help on how to compare two multi-selects.
Specifically, I need to return a true result when none of the options in the second list are selected in the first list. Example below
list A | list B | list B | list B | |
item 1 | x | |||
item 2 | x | x | ||
item3 | x | |||
item 4 | x | x | x | |
item 5 | x | x | ||
TRUE | TRUE | FALSE |
6 replies
-
You can try something like this in a formula field:
let a := [1, 2, 3]; let b := [2, 4, 5]; let xCntA := a[(var subA := this; count(b[= subA])) > 0]; if cnt(xCntA) = 0 then "True" else "False" end
Line 1 and 2 we are creating static arrays, but you can replace these with select statements.
Line 3 and 4 we are using the same procedure that I suggested you use in your previous post. Except this time we put the results into a variable so we can do an if then check on the results. Let see if I can explain what is happening.
Starting from the inside, we have
count(b[=subA]))>0
Which says compare each item in a (cause subA is variable that takes each item in a) and compares it to each item in b and only returns results if they are equal. So 1 would return nothing. 2 would return 2 since it is in both arrays. 3 would return nothing.
Now we have to return to each item in the array. Back to item 1 in array a. Since there is no item in array b that equals 1 from array a, when we count the number of returned value, we would get 0, and since we are looking for values greater than 0 we drop item 1 to our shorten list and we then move on to the next item in the array.
Which leads us to 2. Since 2 is in both arrays, we will get item 2 returned. When we count the returned values it will count as 1. Which is greater than 0 so we now keep this value.
Then we check 3 and it is like 1 so we drop this value.
So now we end up with:
let xCntA := a[=2]
It seems simple here, but if you can imagine doing this with two arrays that have hundreds of items that is where the magic comes in.
Which leads us to line 5, where if the count of the results of variable xCntA is 0 then we know whatever is in variable b is not in a so we can say True. Otherwise we say False.
So back to our example, since we return an item from a our count would be 1. If you remove the 2 from b then it will return True. You can play around with the arrays to verify that it works for all cases you could encounter.
-
awesome, and Yes, my multiple choice field has 7 items and I need to allow for all possible combinations, including where nothing is selected.
I'm used to thinking in terms of loops and was planning on just nesting one inside another where I loop through each item in the second list and compare it against each item in the first list. If they are equal I immediately exit the loop returning false, if at the end none are equal I return true.
I wasn't sure though if there were any built-in comparison functions for multiple choice fields (or I guess just two arrays), which is why I asked the question because this is a whole new way of thinking for me.
So, in the case of the multi-select it would be let a := numbers([name of multiple choice field 1]) correct?
what confuses me is the var subA := this, would it be var subA := numbers(this.[name of multiple choice field 1])?
or maybe that's at the top:
let a := numbers([name of multiple choice field 1)
let b := numbers(this.[name of multiple choice field 1])
-
Mike N. said:
which is why I asked the question because this is a whole new way of thinking for me.It is a whole new way of thinking for me too. My written description of the process is just as much for me as it was for the common good.
Mike N. said:
So, in the case of the multi-select it would be let a := numbers([name of multiple choice field 1]) correct?From my testing yes, you would numbers (with an s). This works with either multi choice field or dynamic multi choice fields.
Mike N. said:
what confuses me is the var subA := this, would it be var subA := numbers(this.[name of multiple choice field 1])?
or maybe that's at the top:
let a := numbers([name of multiple choice field 1)
let b := numbers(this.[name of multiple choice field 1])
You actually don't need to do anything at the xCntA variable. As long as the top two variables are correct everything below will flow.
Though what you wrote for a and b look to be the same thing. You have no table name in front of the field name in a and you put this in front of the field name in b and that is the same thing.
-
Can anyone please send the correct complete formula using a Dynamic Multiple choice field as fields 1 and 2? Because this code confuses me:
let a := [1, 2, 3]; let b := [2, 4, 5]; let xCntA := a[(var subA := this; count(b[= subA])) > 0]; if cnt(xCntA) = 0 then "True" else "False" end
For example, as I understand, if I have a Dynamic multiple choice field named "Country", thenlet a := numbers(Country);
returns an array?
My problem is, I have different tables with a Country field (dynamic multiple choice). I want to retrieve ALL the records of table A whose Country contains at least ONE value of the Country field of table B.
Table A: products offered in different countries
Table B: catalog for two countries
I want ALL the products of tabla A that are offered in at least one country where I publish my catalog.
-
Hi Javier -
The code I provided if simply switched to numbers(dMultiChoice) then would break down because record Id's probably don't match between Table A and Table B.
Which table are you in when this formula is run?
Is this formula part of a view or formula field?
Content aside
- 2 yrs agoLast active
- 6Replies
- 214Views
-
3
Following