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

[Kal_Man]

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!

[Steve]

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.

[Kal_Man]

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.

[Steve]

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.

[Kal_Man]

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?

[Steve]

Kal_Man:

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.

Kal_Man:

Dropdown is still appearing.

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

[Kal_Man]

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

[Steve]

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?

[Kal_Man]

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

[Steve]

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.

[Kal_Man]

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

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.

[Steve · Accepted solution]

Kal_Man:

I have also tried an enum with a base type of ref which also didn’t work.

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.

[Kal_Man]

Steve:

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.

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

Steve:

Did you get the same behavior, or something different?

Same behaviour as previous.

[Antonio_Depetro]

Hi @Steve,

I also have the same problem. Did you find the solution?

thank you!

[Steve]

Please start a new topic for help with this.

[administracion]

I know the post is old, but I recently had a similar problem and did not find any other conversation that could help me, what I did is

1) I left the "ID" as a normal text, no validation.

2) I am assuming you have a password for that ID to match to... being the case, I validate the password with a lookup expression: [_this]=lookup([ID], "table", "id", "password")

3) As for the INVALD VALUE ERROR message, I used the following expression:   if(not(in([ID]),select(Table[ID], condition))),"ID doesn't exist","Incorrect Password")