I have an app that has 3 virtual columns that are taking around a second each. There are 30k rows in this table so aside from cutting down the rows, which I am also working on, does anyone have any tips for making these columns less expensive? The columns are a lookup of another table in my app, I have a concatenated column of the 3 that I need already so would pulling that column and splitting it be faster than 3 lookups? These values are based on a foreign key that can be changed so adding them to the original table is not an option.
What app formulas are you using with these columns?
And I’m grabbing different columns with the same formula for all 3.
Provided your formula evaluates a value of “AAA” for example and you have more than 1 row which this expression can evaluate to the same value, then it always returns the result from the very 1st record from your table.
So you’re saying it remembers if it’s evaluated this formula with “AAA” and doesn’t re-execute the formula for any later rows that have a value of “AAA”?
Correct…LOOKUP returns the very first value that matches the search criteria
What you describe here is result caching, which AppSheet does not do.
That was what I thought. So would it be of use to have one lookup virtual column that contains all 3 values that I need, which I already have in the requisition table for other needs, then use a split to get the values I need in my table?
If you already have the values either (1) in the same row, or (2) in a different, small table, using those existing values would be vastly preferable to doing multiple lookups on a table of 30k+ rows.
Curious why you “have a concatenated column of the 3” values. Why not have them in an EnumList? Then you wouldn’t need to SPLIT() it to get the individual values.
The 3 values are in a header column to make them fit our UI better and to allow our users to use the header in a drop down selection.