How to hide list of items in ref column in form view?

Hello,

I have a form where the user needs to enter an “ID”. I have this ID column referenced to another table for the sole purpose of being able to dereference a few entries in the form. My issue is that the form is now displaying a dropdown of all the IDs - the only other option is buttons. I don’t want it to show any options, the user needs to type it in and the dereferences work from that.

I hope that makes sense.

Thank you!

Set the column’s Valid If expression to:

OR(IN([_THIS], ref-table[key-column]), FALSE)

Replacing ref-table with the name of the referenced table, and key-column with the name of that referenced table’s key column.

2 Likes

Hi Steve,

Thank you so much for your response. I added OR(IN([_THIS], Database[ID]), FALSE) in the valid if for the ID column and saved. When I open the form it is still behaving the same way. As soon as I go to enter an ID it drops down a list of all available options.

1 Like

Put your exact same expression in Valid If for the input column (the column with the dropdown you don’t want), not in the ID column of the Database table.

1 Like

Hi Steve, that’s the way I have it set up. Both tables have an ID column. The ID column for the form references the ID column in the database table (which is also the key for the database table). I have the expression in the valid if section of the ID column for the form. Dropdown is still appearing. I am doing this just so I can dereference the next few form inputs after the ID is entered.

In layman terms, what is the expression doing to eliminate the dropdown?

The Valid If expression is magic: if the expression produces a list result, or if the entirety of the expression is an IN() expression, the user will be offered a dropdown from which to select an option from the list. To bypass this magic behavior when using an IN() expression, the IN() expression must be “hidden” by wrapping it in some other benign expression, such as the one I suggested.

Please post a screenshot of the column’s configuration screen in the app editor, including everything down to and including the Data Validity section.

1 Like

Hi Steve, thank you for the explanation. Makes much more sense. I have attached 3 screenshots.



1 Like

An the ID column of the Traffic Log table is the column the user is trying to input, but is incorrectly being given a dropdown menu?

1 Like

Hi Steve, yes, that is correct. Does the expression work on your end? Specifically with a ref column.

Thanks for all your help with this so far :slight_smile:

1 Like

Yikes! Apparently I’ve never tried with a Ref column. I see that it doesn’t work as I expected! I’m gonna ask about this internally.

2 Likes

Okay good to know. Please do update if you hear anything :slightly_smiling_face:

Do you have any suggestions for the interim, to achieve the same effect I’m going for? I have also tried an enum with a base type of ref which also didn’t work.

1 Like

That’s what I would have suggested. Did you get the same behavior, or something different?

The other option is to make the column Text and add a(n optionally virtual) column of type Ref that mirrors the input column’s value.

2 Likes

That did the trick! Thank you for solving my problem, Steve!

Same behaviour as previous.

1 Like