Limit ENUMLIST choices with VALID_IF

AppSheet AppSheet Q&A
,
1

Hello all,

I have an ENUMLIST in which I have all theses options available :

  1. Déjeuner :hot_beverage:
  2. Diner :hamburger:
  3. Souper :poultry_leg:
  4. Nord du 54e - Déjeuner :hot_beverage:
  5. Nord du 54e - Diner :hamburger:
  6. Nord du 54e - Souper :poultry_leg:

Options as you can probably tell are similar, Options 1 and 4 are the same type (Déjeuner) with a slight variation.

Now I want to restrict the users options so that they dont make mistakes inputing the data. I don’t want the user to be able to enter option 1 AND option 4, or option 2 AND option 5, or Option 3 and option 6

Here’s how I’ve tried to do it in the Valid_If Expression :

AND(
count([_This])<=3,
OR(
IFS(IN("Déjeuner <li-emoji id="lia_hot-beverage" title=":hot_beverage:"></li-emoji>",[_this]),NOT(IN("Nord du 54e - Déjeuner <li-emoji id="lia_hot-beverage" title=":hot_beverage:"></li-emoji>",[_this]))),
IFS(IN("Diner <li-emoji id="lia_hamburger" title=":hamburger:"></li-emoji>",[_this]),NOT(IN("Nord du 54e - Diner <li-emoji id="lia_hamburger" title=":hamburger:"></li-emoji>",[_this]))),
IFS(IN("Souper <li-emoji id="lia_poultry-leg" title=":poultry_leg:"></li-emoji>",[_this]),NOT(IN("Nord du 54e - Souper <li-emoji id="lia_poultry-leg" title=":poultry_leg:"></li-emoji>",[_this]))),

IFS(IN("Nord du 54e - Déjeuner <li-emoji id="lia_hot-beverage" title=":hot_beverage:"></li-emoji>",[_this]),NOT(IN("Déjeuner <li-emoji id="lia_hot-beverage" title=":hot_beverage:"></li-emoji>",[_this]))),
IFS(IN("Nord du 54e - Diner <li-emoji id="lia_hamburger" title=":hamburger:"></li-emoji>",[_this]),NOT(IN("Diner <li-emoji id="lia_hamburger" title=":hamburger:"></li-emoji>",[_this]))),
IFS(IN("Nord du 54e - Souper <li-emoji id="lia_poultry-leg" title=":poultry_leg:"></li-emoji>",[_this]),NOT(IN("Souper <li-emoji id="lia_poultry-leg" title=":poultry_leg:"></li-emoji>",[_this])))
)
)

My approach was the one that if the value is IN the list, the OTHER value shouldnt be in the list, so IN(Option1,[_This]),NOT(Option4,[_This]) should return a false value and I would have an error.

I might have been bashing my head on the wall too hard for this “simple” one but It only seems to work if the EXACT parameters I’ve put in are there. If there’s a variation (List made of Option1,Option2,Option4 in which OPTION4 shoulndt be allowed) it just doesn’T produce the error.

Any help will be appreciated !

thanks

2

well, I found my own solution (while writting the question I figured using IFS was off)

IF(
OR(
AND(IN("Déjeuner <li-emoji id="lia_hot-beverage" title=":hot_beverage:"></li-emoji>",[_this]),IN("Nord du 54e - Déjeuner <li-emoji id="lia_hot-beverage" title=":hot_beverage:"></li-emoji>",[_this])),
AND(IN("Diner <li-emoji id="lia_hamburger" title=":hamburger:"></li-emoji>",[_this]),IN("Nord du 54e - Diner <li-emoji id="lia_hamburger" title=":hamburger:"></li-emoji>",[_this])),
AND(IN("Souper <li-emoji id="lia_poultry-leg" title=":poultry_leg:"></li-emoji>",[_this]),IN("Nord du 54e - Souper <li-emoji id="lia_poultry-leg" title=":poultry_leg:"></li-emoji>",[_this]))
)
,
false
,
true
)

So I’ve wrapped in a OR() the scenarios I DONT want, which are themselves in a AND().

then it’s within just 1 big IF any of those statements = true, then FALSE.

thanks for reading