Valid if "included in column"

Hi everyone, what would be a goof Valid If expression: For data that is included in a column from a different table?

i.e.

Want to make sure that the Name typed in the form is included in the “Reservations” table in the “Client” Column.

Thanks!

Solved Solved
0 4 234
1 ACCEPTED SOLUTION

Valid If will generate a dropdown if the expression produces a list result, or if the entirety of the expression is an IN() expression. Any other expression will be expected to produce a Yes/No result and will not present a dropdown to the user. Given that, the way to test that a value is in a list without presenting a dropdown to the user is to wrap the IN() expression in some other benign expression:

AND(IN([_THIS], select(Reservations[Reservation ID], [Client] = [_thisrow].[Name])), TRUE)

(i.e. AND(IN([_THIS], ...), TRUE))

or:

OR(IN([_THIS], select(Reservations[Reservation ID], [Client] = [_thisrow].[Name])), FALSE)

(i.e. OR(IN([_THIS], ...), FALSE))

are common approaches.

My preference, though:

ISNOTBLANK(FILTER("Reservations", ([_thisrow].[Name] = [Client])))

View solution in original post

4 REPLIES 4

Try

select(Reservations[Reservation ID],
[Client] = [_thisrow].[Name])

Alternatively this could create a dropdown of just the names that exist in the reservations table.

Thank you Markm that would work in another scenario.
For me, the thing is I don´t want it to be a list, its a security measure to have an exact match between what the user types and the “Name” in the Reservation table before they can continue.

Thanks again!

Valid If will generate a dropdown if the expression produces a list result, or if the entirety of the expression is an IN() expression. Any other expression will be expected to produce a Yes/No result and will not present a dropdown to the user. Given that, the way to test that a value is in a list without presenting a dropdown to the user is to wrap the IN() expression in some other benign expression:

AND(IN([_THIS], select(Reservations[Reservation ID], [Client] = [_thisrow].[Name])), TRUE)

(i.e. AND(IN([_THIS], ...), TRUE))

or:

OR(IN([_THIS], select(Reservations[Reservation ID], [Client] = [_thisrow].[Name])), FALSE)

(i.e. OR(IN([_THIS], ...), FALSE))

are common approaches.

My preference, though:

ISNOTBLANK(FILTER("Reservations", ([_thisrow].[Name] = [Client])))

Thank you Steve, I also liked better the last expression.

Thanks for sharing your knowledge and explaining about the valid if dropdowns!

Top Labels in this Space