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.
There may be a way - can you give an example of what you are trying to achieve?
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?
Hi @Bogdan_Stan , Yes, that makes sense- but it’s not possible in AppSheet. Here are a few related feature requests.
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.
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.
Let’s say I’ve got a “Configuration” table (with a sleight modification to what you’re proposing):
My show_if formula would look like this:
IN([Selected_Type],
LOOKUP("Column_Name",
Configuration, Column_Name, Visible_Types
)
)
You can get real fun and automate most of this utilizing a macro recorder.
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.
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!
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.
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)
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....), " , ")
Thank you for the clarification. Excellent. I will try this right away.
Your detailed response is very helpful.
Best
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.
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!
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.
This will produce true
if the “value” specified is anywhere in the table
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |