Dereferencing question. I have a column with...

[Charlie_Wells]

Dereferencing question.

I have a column with type Ref. Right now it shows a dropdown of all the rows in the ref’ed table, since I don’t have a column on the form that has the same value as the key on the ref’ed table. (Don’t ask).

I do want to narrow down the rows shown, however. Kind of like showing the user a slice of the ref’ed table, where only those rows are displayed where a certain column matches a value I’ll specify (actually the value in a column on the form).

Can I specify this as an expression on the Ref-type column definition itself?

I have a feeling I should know this but it’s getting late and I’m getting punchy…

Thanks!

[Aleksi_Alkio]

You can filter the list with a SELECT(TableName[KeyColumnName],[ColumnName]=“DesiredValue”)

[Charlie_Wells]

@Aleksi_Alkio thanks you! that worked great as far as showing the right list of rows goes. The next step: how does a user select a specific row? The idea is that once the user is presented with a list of matching rows they would select the right one, and I would then pull the values of a couple of columns from that row to add to the form.

[Charlie_Wells]

@Aleksi_Alkio it also turns out that I can only add SELECT to a virtual column. Ideally I would like to filter the values shown to the user on the actual column on the form so they can pick one…

[Charlie_Wells]

Baby steps! I defined a slice for the table to look up and hard coded a value in the Row condition. I then ref’ed to that slice and the right thing happened: I only see the right rows from that table in my form, and can select the right one. Yay! So now - how do I specify a condition in the slice that uses a value in the form table?

Something like this. Passes the expression verify, but of course it doesn’t work, probably because I’m not using this correctly. HMC Log is the form table that has a Member Number column (the slice also has a column named that, for maximum confusion)

[Member Number]=MAXROW(‘HMC Log’,_rownumber).[Member Number]

[Charlie_Wells]

… found this demo and app, may be what I need:

youtube.com - Dropdowns - AppSheet Office Hours Excerpt Dropdowns - AppSheet Office Hours Excerpt