Solved: Lookup or select with variable column's name - Page 2

Mic · 10-07-2020 04:56 AM

Hi !
I am currently blocked on this problem : the return-Column name in a Lookup() expression must come from the source table and not the

Context :
The app has a table A with a form and a table B with read-only data.
When filling the form, the user make choices that must populate the value of one field of this form with a value of table B.
Example : if Question 1 = 80 and Question 2 = “yes” then Value of row 3 = [related specific cell from table B matching the row id of table A]

Since there is a limited number of combinations, I populated the table B’s 1st column named concatenated with all these combination and the other column with the key values of table A. Then, I created a virtual column in table A with the concatenated answer of Question 1 and Question 2 so it can matches the table B related value for the lookup. After, I tried to implement the Lookup() expression to retrieve the cell. Here is the formula :

LOOKUP("concatenated","table B","Column 1","**table A key value**")

The problem there is that the return-Column is from a table A value and it creates an error.

After that, spent a lot of time studying and trying different options like inverting the table B and trying with the Select expression. So far nothing work because the fact that the table B’s column name is coming from a table A value.

Now I am facing this blockage and a delivery deadline with is unfortunately stressful as I do not find any solution.

The context of this app : An operator must rince pipes. He has 240 different sequences to follow in order to rince all these pipes correctly. The rince time depend of the read value of dynamic pressure and the size of the pipe. Each 240 sequences has a different rince time table depending on these variables (ex: in sequence 1, if 0-10 psi and 4"pipe then rince for 4 minutes. In sequence 2, if 20-30 psi and 2,5" pipe then rince for 8 minutes…). One question in the form is the rince pressure and the other is the pipe size. Then the operator must see what is the rince time.

Any help will be greatly appreciated.
Best regards, Mic

Steve

Translations table

Key	Phrase	Language	Translation
En: Hello	Hello	En	Hello
Es: Hello	Hello	Es	Hola
Fr: Hello	Hello	Fr	Bonjour

My Translations slice

(
  [Language],
  = ANY(
    LIST(
      USERSETTINGS("Language"),
      "En"
    )
    - LIST("")
  )
)

Phrases table

Key	Label
Hello	`=LOOKUP([_THISROW].[Key], "My Translations", "Phrase", "Translation")`

View solution in original post