Need help with combining expressions

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.

0 1 127
1 REPLY 1

Steve
Participant V

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().

Top Labels in this Space