How to pass a value from a dropdown list to a colu...

kasun_charitha · 08-21-2021 12:43 PM

As the return value of the lookup command, how do we give the value of a field in our main form?

table 1 (main form)
ID
Road Name (enum = 165,263,157,168)
layer description (this field enum = ABC, Subgrade, Subbase)
layer des. No

table 2 (hidden data)
ID
Road Name (enum = 165,263,157,168)
ABC
Subgrade
Subbase

This is what I want.
First select the road name in table 1. Second, select the layer description. Then in the table 2 corresponding to the road name, in table 2 which matches the layer description in table 1, the value should be passed to “layer des. No” in table 1.

LOOKUP([Road No],"Layer names","WCO-",[Description of Layer])

This is the lookup expression I wrote. But this is not working. Can you help?

Steve

kasun_charitha

Are you saying to use “select” for the retun value of lookup?

This is what really needs to happen.
Give the return value of the lookup one of the last three columns in Table 2. It is determined by the layer description enum in table 1.

Marc_Dillon

I can’t follow a word you said, but I can tell you this:

The fourth argument of LOOKUP() must be the name of a column, in quotes, not the name of a column in square brackets.

Also, you probably need to use [_THISROW]. with your first argument (read the help article that Steve linked).

kasun_charitha

Sorry about my poor English ability.
I need to give a variable to the return column.
When that value changes from the main form, select the relevant column in the data set (tabale 2).
See Figure below

Marc_Dillon

Not possible.

You need to do this instead:

IFS -or- SWITCH(
  ... , LOOKUP( , , , "return col 1" ) ,
  ... , LOOKUP( , , , "return col 2" )
)

kasun_charitha

is done. very very long formula. thank you.

WillowMobileSys

Looking at your Table layout, I don’t think you want a LOOKUP() at all. It seems a Ref Column would be better suited.

Layer Description for a Road Name is defined by 3 values - ABC, Subgrade and Subbase.

In Table 1, I would recommend columns Road_Name and Layer_Description but with Layer_ Description defined as a Ref column - i.e. a pointer to a row in Table 2. The pointer is the ID column.

Then you can use “dot” notation to dereference any value from that referenced row - no lookup required.

From your example, If the Road_Name is selected as 263, then Layer_Description Ref value would be set to 2 - pointer to row with ID of 2.

Then when you need any values from Layer_Description, you can retrieve them like so:

[Layer_Description].[ABC]
[Layer_Description].[Subgrade]
[Layer_Description].[Subbase]

The article below helps describe this a little better but it doesn’t really explain the “dot” notation and how to use it.

How to pass a value from a dropdown list to a column name given to the return value of a lookup?