Lookup or select with variable column's name

[Mic]

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

[1minManager]

Its kinda hard to understand without knowing exactly how table A & table B work together. But try something like this instead:

ANY(Select(TableB[Column 1],AND(
[Column 2]=[_ThisRow].[Question 1],
[Column 3]=[_ThisRow].[Question 2]
)))

This will return whats in TableB[Column 1]
The row is chosen with the following 2 options, which basically say
TableB[Column 2]=TableA[Question 1]
TableB[Column 3]=TableA[Question 2]

Hope this helps

[Steve]

Mic:

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.

This seems like a reasonable approach. Can you provide screenshots of the actual expression, and of the error?

[Mic]

@Steve, here are the working and non-working lookup screenshots:

[Mic]

Non-working:

It seems like I just need a way to put this concatenated value in the returnColumn. Maybe I’m wrong…

Sorry for the multiple post but the forum only allow me one image per post.

[Steve]

Your expression:

LOOKUP([Sequence], "Temps_Rincage", "Sequence", "0-10_2")

Probably should be:

LOOKUP([_THISROW].[Sequence], "Temps_Rincage", "Sequence", "0-10_2")

Prefix [Sequence] with [_THISROW]..

See also:

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Mic]

Thanks for your reply. Maybe I miss something but unfortunately it does not address the issue as the returnColumn of this Lookup must not be a constant but variable. Instead of “0-10_2”, it has to be the variable (according to users selection in the form of the sequence table).

Do you know if it is something possible, having a relative returnColumn ?
Best regards, Mic

[GreenFlux]

The LOOKUP() expression takes 4 parameters:
LOOKUP( find-value , in-dataset , in-column , return-column )

The last 3 are ALWAYS strings; the name for the dataset or column.

[EDIT]- removed bad info

Looks like there’s no way to do this with out a massive IFS(), and with 4500 combos that would be insane!

[Mic]

Hi and thanks for your reply.

The problem is that I always need to insert a variable related to the other refenced table somewhere and it seems that AppSheet neither support this function in select() or lookup() or “[table].[variableFromRefTable]” or …

As for IFS() and SWITCH(), there still have around 4500 different possibilities so without a variable, it’s out of scope.

Any other idea ?
Thank, best regards !

[Steve]

GreenFlux:

A dynamic return column can be created by replacing return-column with a sub-expression, or a new Virtual Column that dynamically switches the return-column to be used.

That’s news to me! Do you have a working example?

[GreenFlux]

@Steve, you’re right. I thought the string input parameter could be replaced with an expression that returns a string. I guess this is a limitation of the LOOKUP() expression.

I could have swore I’d substituted the input string for an expression before, but that may have been a different expression that takes a text input.

Sorry, @Mic. It looks like there’s no easy way to do this in AppSheet. Maybe a different data structure would prevent the need for a dynamic LOOKUP.

[GreenFlux]

@Mic, I usually avoid spreadsheet formulas unless there is no equivalent AppSheet function, and that seems to be the case, here. You might be able to pull this off using a sheet formula with INDIRECT().

And here’s a feature request to add this functionality to Appsheet:

Indirect Parameter Evaluation Feature Requests

I would like to be able to use indirect evaluation. Where I store an expression as a string to be found and evaluated in evaluated expressions. Template[To]="[Key].[Email]" initial_expression=eval(Template[To])

FileMaker has a similar function, but it’s called EVALUATE(). It’s a very powerful expression but would probably make error checking impossible so I don’t know if we’ll ever see it in AppSheet.

[Steve]

Mic:

unfortunately it does not address the issue as the returnColumn of this Lookup must not be a constant but variable. Instead of “0-10_2”, it has to be the variable (according to users selection in the form of the sequence table).

It’s not possible. You’ll have to have independent LOOKUP() expressions for each possible return column.

[Mic]

Hello everyone,

First, thanks for your prompt replies. I appreciate.
To explain my reply’s delay: meanwhile I was composing my answer, I had some thoughts that popped into my mind. I need to work on the app before posting a follow through.

Thanks again. Sincerely, Mic.

[Mic]

@1minManager, you are right it’s pretty abstract. Here is a screenshot of the tableB named Temps_Rincage :

.

I also tried with another table that was disposed the other way around so the column A was the combinations and the row 1 was the sequences. I tried many stuff…

The rince sequences are equal as the key column of the tableA named Sequences.

The question that comes first in the Sequence’s form is Pression dynamique which is an absolute number that I then need to transform into the appropriate range (0-10, 10-20…) and the following related question is the size of the pipe. With a concatenation expression and a series of “if”, I create the concatenation containing the pression range and the pipe size. Then the concatenation column is [concat_tempsRincage] in the table Sequence.
…

Thank again. Sincerely, Mic.

PS: I can still only add one image per post.

[Mic]

Ok thank you all for your help. I will try to find an original way to counter these Appsheet’s limitations. Maybe one day we’ll be able to store variables ?

I wish you all a great end of day ! Mic

[alphacp]

Again, thank you to you all for asking the question and answering it. I have been stuck trying to implement this all morning and now I know the issue. In my case I was trying to look up localization values and have all the language options in one file and then wanted to select the correct column based upon a USERSETTING for language. Since returnColumn cannot be an expression of any form, this is not going to work. My two options therefore are a big SWITCH statement or one dictionary file per language and still a big SWITCH statement. Oh well.

[Steve · Accepted solution]

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")

[alphacp]

What a great idea! Thanks for suggesting this. I will give it a go.

[YashpreetSingh]

Can someone throw some more light on this part in layman's terms I am a beginner to AppSheet my formula in excel gives Value errors in the appsheet hence had to make the formula compatible with the app sheet but failed to add the formua within the lookup formula. Struggling with the same issue in adding Formula within the Lookup's Return column.

I have 12 columns named JAN to DEC Wanted to have current month's name within the lookups return column.

[dbaum]

---

@YashpreetSingh wrote:

adding Formula within the Lookup's Return column

---

You can't. Review the LOOKUP help article:

• return-column - ... The argument may not be an expression.

[alphacp]

Works a charm, thanks Steve!