Limit values in a reference column

[Will_Henry]

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?

[Belinda]

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

[Aleksi]

What kind of Valid If formula are you using?

[Steve]

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.

[Mike_A]

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?

[Steve]

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.

[Mike_A]

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