For example:

Bogdan_Stan · 02-08-2021 04:00 PM

Hello guys

Is there a way to use the column header name inside of an expression? I want to make like a matrix table to use it for the show If option. I have too many types and all the columns must behave different depending on the selected type.

Bellave_Jayaram

There may be a way - can you give an example of what you are trying to achieve?

Bogdan_Stan

In a form i have a column named “Type” ( list with 20-30 types ) and more than 20 columns that represent specs of the chosen type. Some of the columns doesn’t apply for some types and i want to hide them.

Instead of writing a long expression that can cover all types i want to make a table with 3 columns ([Column Name] , [Type] ,[Show?] ) and apply a standard expression ( find or select ) that wll return if it needs to be hidden or not. In this case i need to use the column name in the expression otherwise i’ll need to manually write it for each expression, in each column.

Does make any sense?

GreenFlux

Hi @Bogdan_Stan , Yes, that makes sense- but it’s not possible in AppSheet. Here are a few related feature requests.

MultiTech

Hey @Bogdan_Stan
While you can’t natively call the column name, you can certainly “hard-code” it into your proposed formula; then the only thing that would need to change in the formula is the individual column name.

I’ve done things like this before;

and while it can be a pain to go through EVERY column that’s shown and apply a show-if (each requiring a quick copy/paste data change), it doesn’t really take that long - and you only have to do it once.

Ultimately, you’d have to copy a formula into each column’s show if anyways… this process only adds 1 or 2 steps to each.

For example:

Let’s say I’ve got a “Configuration” table (with a sleight modification to what you’re proposing):

[Column_Name] - contains the text value of each column name
[Visible_Types] - an EnumList containing all the types that should show the field

My show_if formula would look like this:

IN([Selected_Type], 
  LOOKUP("Column_Name", 
    Configuration, Column_Name, Visible_Types
  )
)

The only thing that needs changing is the value inside the “double quotes” stating the column name to pull
- So the workflow for this would be the following
  1. Open the show if for a column & Paste your template formula
  2. Copy the name of the column (from the top label of the expression builder) & Paste it into the hard-coded space
  3. Save

You can get real fun and automate most of this utilizing a macro recorder.

You can record the following steps with a PC-based macro recorder:
- Paste the template formula
- copy/paste the column name
- save

Then you simply need to replay these after you open each column’s show if, and all the “work” is done for you in a flash. Then it’s only a matter of “processing” through each of your column’s show-if fields.

Christian

I am trying to follow your advise here from the “For example:” part of your post. But I am fairly new and a bit confused about how to structure this. I basically have the same issue as Stan in that I have:

a) a forest survey
b) different species
c) different questions depending on the species

So, I understand the manual nature of the task but I can’t get the set up right. Would you mind taking a look and see where my error lies?

This is the table (PP_Plants) with the all questions (column names), I am only showing the first 3 of many, and in the rows are the species (what you called “Visible_Types”) to which a particular question has to be applied. But you said “Visible_Types” is an EnumList. But where do I put this EnumList??

And here is the first question’s column properties. I made this the EnumList but that’s probably wrong. It’s a Ref type, so I thought it would populate from the PP_Plants table

And here is the formula, which obviously is missing the “Visible_Types” but I don’t know where they have to go.

I am really stuck here and would appreciate it if my error could be pointed out.

Thank you!

MultiTech

The problem you’re running into here is actually a limitation of the system. When you do a lookup, the base assumption is that you’re doing an equality for the condition.

The first item of the lookup is the value that you want to find, the “value” is singular.
The third part of the lookup is the name of the column that telling the system it will find the first element.
All of this is a basic equality:
“Value” = [column]

It helps to know that look up is really just a port of a select statement, meaning when the system is processing your lookup it’s actually processing it as if it were a select. (There’s actually a translation process that happens before it’s processed.)

The following formula:

Lookup("value", table, column, answer)

Will be translated into the following select formula:

Select(table[answer], [column] = "value")

So what you really need to do is change your lookup into a select, and use your in statement inside that:

Index(Select(Table[answer], IN("value", [column])), 1)

this will give you the value of the answer column.

If the answer column is a list of something, you will then need to post process that answer into a list so the system can work with it. You do this by wrapping the whole thing in a split statement:

Split(Select(table....), " , ")

this will then give you the system a true list, meaning an element of the list type, that the system can then appropriately process with.

Christian

Thank you for the clarification. Excellent. I will try this right away.

Your detailed response is very helpful.

Best

Christian

Thank you again for your advise!

I finally managed to get it almost right. My expression is:

SPLIT(Select(PP_Plants[Initial growth], IN(“Quercus laurina”, [Initial growth])), " " )

The currently selected tree species (the “value”) in the survey form is: Quercus laurina
The column “Initial growth” in table “Plant_PP” has (the “answer”) in row 2: Quercus laurina

So, it is a match and the Show_if should be a YES and thus make the column containing the expression, visible. But it doesn’t. The “Initial growth” column on the left contains the expression, but in the survey form, it is not shown even though it’s a match.

I have not applied expressions to the other columns yet since I am still testing.

This is the “answer” column with the correct answer in cell B2:

This is the test result of testing the expression. I was expecting Quercus laurina

Thus I have two remaining issues and any additional advise would be much appreciated.

How to adjust the expression to solve the SPLIT issue?
How do I replace the fixed name “Quercus laurina” inside the IN expression to instead be a variable that expresses the chosen species column?

This is where I select the tree on the form. And below is the column which identifies the species from another table.

This looks solvable but after two hours in the forum and on the appsheet help page I can’t seem to figure it out.

Thank you in advance!

MultiTech

My friend; you should check out de-references.

Looks like you’ve already got the tables with all this data there; all you need to do is connect things using references.

This way instead of doing a LOOKUP() to get your data, you can use the efficient systems built into AppSheet to get this data.

Christian

Forgot to post my solution (the expression going into Show-if) for anyone else who may be interested or stumble upon this thread in the future:

Instead of:
SPLIT(Select(Table[answer], IN(“value”, [column])), " " )

It worked with:
IN([“value”], SPLIT(Table[answer],","))

In summary, I tried to get data from a Matrix into a form, which is not completely possible through one dynamic expression. The solution I found still has to be manually edited (the “answer” part) for each new form item (column) aka survey question in my case.

Thanks again to @MultiTech_Visions for pointing me into the right direction. I would not have figured this out otherwise.

MultiTech

This will produce true if the “value” specified is anywhere in the table

Is this what you wanted?

Use column header name in expressions

I’ve done things like this before;

For example:

This way instead of doing a LOOKUP() to get your data, you can use the efficient systems built into AppSheet to get this data.