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 |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |