I have aable that serves as the many to many link between two other tables. That all works great.
In this table, I have two expressions that each work separately but I need to combine them to get both to work.
In the valid If of one of the reference fields I want to use @Steve tip to prevent duplicate lists from being added. This works by itself.
ISBLANK(
IFS(
ISNOTBLANK(
FILTER(
โItem_to_Listโ,
([_THIS] = [ListID])
)
- LIST([_THISROW])
),
โDuplicate List!โ
)
)
I also want the Valid If to include the following. This works by itself.
ORDERBY(SELECT(Lists[ListID],TRUE),[ListName]))
How can I format an expression that will accomplish both of these? I tried simply combining them with an AND function but didnโt work.
Thanks.
Update: the first expression is working correctly after all. Back to editing it.
The ORDERBY(...)
expression produces a list result, which would allow the user to choose from a dropdown menu. the ISBLANK(...)
expression produces a singular Yes/No result that merely declares the input valid or not. Iโm guessing what you want is to offer the user a dropdown menu from which to choose that prevents choosing a value that would duplicate some value already in another row. To achieve that, better to use list subtraction to remove all already-chosen values from the list of options. The pattern is:
(
list-of-all-options
- list-of-chosen-options
)
It appears list-of-all-options
would be Lists[ListID]
, but Iโm not confident Iโm interpreting what list-of-chosen-options
would be. It appears it might be SELECT(Item_to_List[ListID], ([_THIS] = [_ListID]))
. Together, these would give you:
(
Lists[ListID]
- SELECT(
Item_to_List[ListID],
([_THIS] = [_ListID])
)
)
I would interpret the above to mean: allow/choose from all lists, excluding this list only if this list is already used in an existing item listing.
You could then wrap the above in ORDERBY().
User | Count |
---|---|
43 | |
28 | |
23 | |
14 | |
14 |