Limit values in a reference column

AppSheet AppSheet Q&A
1

I have a reference column in ‘table_A’ that references a unique_id in a people_table. The people_table has a name column that is used as a label. So, when I select a dropdown from the reference column it shows the name, and I can also type in to search for a name. This is great. Now, if I want to limit the options in the reference column dropdown, I add a formula in the ‘valid if’ field. The formula tests if the unique_id value is valid. This effectively limits the options in the dropdown and still shows the names in the dropwdown. The problem is that now if I start typing a name in to search for a name, it is not searching the names but the unique ids. So if I was searching for the name “Dave Smith” and I started to type in “Dave”, no values will show up…

By adding the valid if formula, the search no longer search the labels (names) but the unique_id. Is there anyway around this?

2

i had trouble with this too. You might try changing your valid_if statement to something using SELECT()

3

What kind of Valid If formula are you using?

4

It would be helpful to see the Valid If expression you’re using.

Also make sure your unique_id column has the Searchable? property set to OFF.

5

Steve:

Also make sure your unique_id column has the Searchable? property set to OFF .

@Steve - that’s interesting. can you clarify the negative side effects of this? search effects dropdown behavior as well?

6

Searchable? affects the search box only (to my knowledge).

A key generated with UNIQUEID() will never have any legitimate value to a typical user. Exposing internal values may cause confusion or be otherwise undesirable. In general, its a good idea to try to keep internal data from user view.

2 Likes
7

Aha! yes… good point. why confuse the users in searches… I was thinking unique key (like PO1234)…