Lookup challenges I have a table of data tha...

[Karl_Kenny1]

Lookup challenges

I have a table of data that I want to use in my form for various functions. A is set as the ref column to get the dropdown - this works OK B has a valid_if pulling from A - this works OK C is text that I want to pull into the field in the form as an instruction to the user and not a dependant dropdown - I can’t figure this part out D is a field that I want to use along with a show_if to display a specific entry field - I can make this work but not when I have multiple same items in column A i.e., the formula simply doesn’t know which row I’m referring to.

Questions: a. How do I simply pull column C data back into a field in my form? b. What formula do i use to be able to use column D in a show if?

A B C D 1 abc 1a Yes 1 xyz 2b

1 jjf 3c Yes 1 kkk 4d

2 abc 3c Yes 2 jfh 4d

2 kkk 3c

2 ert 4d Yes

[Aleksi_Alkio]

If I understand your request correctly…

a. SELECT(TableName[C],TRUE) b. When the column A is a ref field, you could use deref expression [A].[D]=“Yes”

[Karl_Kenny1]

No joy, I had tried these previously.

I wonder if the issue is that the ref column ‘A’ does not have unique values, so when I use the deref it simply does not know which row I want to return for either question a or b.

[Aleksi_Alkio]

The Ref column doesn’t need to be unique. The table where this ref field is reading the value from has it’s own unique value.

[Karl_Kenny1]

Understood.

I don’t have any problem creating the ref and getting the two levels of drop down to work i.e., column A and column B.

When I use the select expression to pull back the text in C I get a dropdown, where I only want a text.

When I use the deref for column D it only works when the data in column A is unique.

I must be doing something wrong.

Here is my main data collection table

[Karl_Kenny1]

and here is my table with the info I want to use in the form dropdowns, controls etc