0

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

null
    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • Mike_N
    • 1 yr ago
    • Reported - view

    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])

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Mike_N
      • 1 yr ago
      • Reported - view

      Fred 

      "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."

      Well, I am using the formula in a select statement, so what i was trying to infer was the one with this was the current record, and the one without were all the ones from select we are comparing against. But regardless, i think I have enough to take a decent shot at it.

    • Consultant and developer
    • Javier
    • 1 yr ago
    • Reported - view

    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", then

    let 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.

    • Fred
    • 1 yr ago
    • Reported - view

    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

  • 1 yr agoLast active
  • 6Replies
  • 201Views
  • 3 Following