Solved: Filter Enum options

Mark_11 · 02-08-2020 12:27 PM

Hi,
is it possibile to filter enum options?
I have 4 choices, but only three of them always possibile, the forth only in a specific condition.
Can i hide this forth option?

column 1
Enum (A,B)
column 2
Enum (A,B,C,D)…

If column 1 is A, column 2 show A,B,C,D…
If column 1 is B, column 2 show A,B,C.

thanks

Steve

Because the current value is also included in table1[spot], so the current value is being removed from the list of allowed values.

Try this:

(
  coordinates[spots list]
  -
  select(table1[spot], ([_thisrow].[_rownumber] <> [_rownumber]))
)

which means “the entire list”-“the list of values already taken by other rows”.

View solution in original post

Kirk_Masden

Yes. This is possible. You need to make an expression like

if([Column 1]=A,{A, B, C, D},{A, B, C})

. . . and put that in the “Valid if” spot of your Enum column.

I’m not clear about the relationship between your columns one and two so the expression to create the appropriate list based on what is in column 1 may need to be tweaked but the idea is to make an expression that produces a “list” based on the conditions you described and then to put that expression in the “Valid if” spot.

Here’s some discussion about how to use this feature:

Mark_11

This works for me, thanks.

I also have another scenario:
column [spot] (enum)
It is a very long list of spot like spreadsheet cell:
A1
A2
A3
B1
B2
B3
.
.
.
AA1
AA2
AA3
.
.

I have a static table where i just update things, and every day i change these Spots.
When i try to update one spot, i would like to filter the spots already occupied.
So, if A2 is already taken by another row, my dropdown would be A1,A3,B1…(not A2) cause i can’t assing 2 items at the same spot.
@Steve @Kirk_Masden
thanks

Kirk_Masden

I’m not sure that I understand your situation fully but can respond to this part:

if A2 is already taken by another row, my dropdown would be A1,A3,B1…(not A2)

If you have two lists, you can subtract on list from the other. So, if A2 were part of a list (or the only item in a list), subtracting that list from the list that contains A1, A2, A3, B1… should work for you.

This page should help:

Mark_11

I have one list
[Spot] is an ENUM dropdown
valid if: Coordinates[Spots list]
I would subtract from my dropdown all the spots already choosen in other rows of my table.
It’s the “preventing duplicate” thing but i don’t know how to write my own formula

EDIT: seems ok with a valid if coordinates[spots list]-table1[spot] that would means, “the entire list”-“the list of values already taken”.
I can’t understand why whathever i choose using quick edit give me a "Unable to save, [spot] is invalid.
The same exact choice by ordinary edit works perfetcly.

Steve

Because the current value is also included in table1[spot], so the current value is being removed from the list of allowed values.

Try this:

(
  coordinates[spots list]
  -
  select(table1[spot], ([_thisrow].[_rownumber] <> [_rownumber]))
)

which means “the entire list”-“the list of values already taken by other rows”.

Mark_11

It works, great.
Thanks to all!!

Joaquin_Otero

if([USO:] = “GRANO”, {“Z8”}, {“RAYO”})

The expression is valid but its result type ‘List’ is not one of the expected types: Enum

It seems like it is not working with Enum.

Kirk_Masden

Is this expression in the “valid if” spot?

Steve

The curly braces ({ and }) are used to denote a list. By enclosing a value in curly braces, you’ve created a list containing that value:

{“Z8”}: a list with one item, the Text value Z8.
{“RAYO”}: a list with one item, the Text value RAYO.

The error you shared indicates a single value is expected rather than a list. To produce a single value rather than a list, remove the curly braces from your expression:

if([USO:] = “GRANO”, “Z8”, “RAYO”)