Formula returning specific values depending on multiple choice selection

wow, 40 choices! Anyways, you can use the switch command, but you have to follow this unwritten twist.

Switches go down the list of cases, so you have to remember to put the cases that have the lowest chances of happening on top and ending with cases with the highest chances of happening.

Since you have a multichoice field, you can no longer use the equal sign. That is because you get an array of numbers so you have to start using different commands. Look at this post for more information. So you can do something like:

let mcArray := numbers(dMC1);
switch true do
case mcArray[= 1] > 0 and mcArray[= 6] > 0:
    "case 1"
case mcArray[= 2] > 0 or mcArray[= 4] > 0 or mcArray[= 6] > 0:
    "case 2"
default:
    "error"
end

If you put line 3 under line 5 then Ninox will always evaluate the record as "case 2" because the existence of 6 is there before it checks for 1.

There are about 40 primary choices, you said. But how many "X", "Y", etc do you have?

Giovanni Zagarella said:
I have the feeling this is not doable via the switch function though - unless I identified every possible scenario through a line of code, which is not feasible with 40 different options. Would you have a solution in mind?

 You are correct that a switch would be too complicated. I'm sorry for misunderstanding your request. You can try the following:

let mcArray := numbers(dMC1);
let firstGroup := [1, 4, 6];
let secondGroup := [2, 5, 7];
let thirdGroup := [3, 8, 9, 10];
let fgCheck := for loop1 in mcArray do
        if count(firstGroup[= loop1]) > 0 then
            "X"
        end
    end;
let sgCheck := for loop1 in mcArray do
        if count(secondGroup[= loop1]) > 0 then
            "Y"
        end
    end;
let tgCheck := for loop1 in mcArray do
        if count(thirdGroup[= loop1]) > 0 then
            "Y"
        end
    end;
concat(unique(fgCheck), unique(sgCheck), unique(tgCheck))

Of course changing:

1) dMC1 to be your multi choice field

2) firstGroup, secondGroup, thirdGroup numbers in the array to match whatever grouping you need.

3) Change the result in the if statements on lines 7, 12, 17

You can easily add Groups by adding a new variable, then add a new Check code, then adding the new Check variable to the concat at the end.

This toy database contains four possible solutions. The last one is completely different, since the multichoice has been made dynamic. This means that an auxiliary data table is needed. The advantage is that all the information needed to compute the result is contained, as data, in that table, which makes the solution easy to maintain.