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.

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

3 Likes