We all know the power of references inside an AppSheet app, but there’s also some overhead associated with them; and by that I’m talking about:
Reverse-References
These are the [Related Whatever] virtual columns that appear when you create a reference connection between two tables.
These are great, essential even, allowing you to now only view your data in a clear way, but also customize the functionality around your app as well. But sometimes you don’t need the reverse reference list - you just need to be able to pull some data from the reference.
Enter: Enum/EnumList (Base Type: REF)
In July, AppSheet announced the ability to take an Enum column with it’s base type set to REF, and use it the same way you might a reference column when pulling data.
This means that you can de-reference (and list de-reference) information from your Enum/EnumList columns the same way as you would a reference column or [Related Whatevers] virtual column.
Pulling a Single Value
[Reference_Column].[Column_I_Want_Data_From] <<<Reference column used to de-reference
& [Enum_Ref_Column].[Column_I_Want_Data_From] <<<Enum column used to de-reference
Both function the same way, pulling a single value from the referenced table.
Hi @MultiTech_Visions thank you for posting.
What’s your main intention? Is it sync time, or just to have a cleaner column structure?
[Improving performance by getting rid of unnecessary virtual columns](https://community.appsheet.com/t/improving-performance-by-getting-rid-of-unnecessary-virtual-columns/12152/8) Questions
In general, I would recommend not trying to hack the REFROWS thing. Just treat it as an essential column added by the system. In no performance investigation have I ever found it to be an actual cause of slow performance.
What’s your main intention? Is it sync time, or just to have a cleaner column structure?
A bit of both really.
Mainly it’s about cleaning up the virtual columns; sometimes I’d have dozens of “extra” reverse references, so having a way to remove these helps reduce the clutter.
But also a bit of performance as well. Even if calculations only take “one pass over the data”; if my data is 50,000 records that pass takes… half a second?
I’ve got an app, that’s 4 years old and serves as a platform for a tele-health company, that’s been through every optimization protocol you can imagine for an AppSheet app - at least 3 separate individual times too - so I’m looking for every bit of performance I can squeeze.
But also, one of my gripes about the community is that it seems like we (the people answering questions) are always saying the same things over and over. Re-typing things out, answering each person’s question uniquely while providing the same info we have dozens of times over again.
It FINALLY occurred to me that instead of typing up a long complete and detailed answer in that one post - I should instead make a general post about these things, then link that post into the answers.
This way we finally have a single solid answer to questions, and we can start routing all the different questions people ask (that will be answered by that post) TO that post.
It’s only after we get a ton of different ways of asking a question tied to a single answer that that answer will start popping up for people when they’re posting their question
One thing to tack on to this would be why we would use base type ref instead of just a plain enum… The base type ref, let’s us insert the key, but still see and leverage the labels.
One other note, currently enum base type ref still won’t play ball with interactive dashboards. For that you still need an actual ref field.
I have been using this Enum/Ref and EnumList/Ref technique to remove unnecessary Virtual Columns. However, I just discovered today that they break Interactive Dashboards. So, at least for some, back to the standard Ref column type.
Takeaway: if you want your user to pick from a list of values (like products in a table) - use ENUMLIST and then set it up as shown above. The user will see the description of the product, but the value will be written as the KEY (ID) which you can use to power other column values elsewhere.
Hi @MultiTech_Visions I want to use Enum Base Type Ref as a dropdown. So when I add a new row I want to see all the values from the Parent Table.
I see that we have to add an expression in the ValidIf just as you did in your sample App. Otherwise the Dropdown will be blank.
But with that we cannot add new items to the parent table. To do this we have to
cut the expression from ValidIf and paste it into Suggested values.
Am I right?
This is really neat Idea. @MultiTech but there is a question here. I can use ENUMLIST ref types but there are few detail view like below as inline tables.
So if I use the manually created ENUMLIST Column in a VC that is instead of using something like this REF_ROWS(“Product Region Prices”, “Product ID”) just if i use [Product Region Prices ENUMLIST] will this effect performance? I removed REF_ROWS () and it just generating a LIST VC with ENUMLIST.
If you think about the REF_ROWS() formula, this is actually computing what should be in the list.
Creating a VC to display a list of values that’s stored in a physical column…
that’s a much easier task to complete
with significantly less steps
So yes, changing from a Ref_Rows() to a VC displaying a [Physical_Enumlist_BASE_TYPE_Ref] will increase performance at the sync, reducing the number of operations and speeding things up.
NOTE
By removing the “virtual-ness” of the VC by removing the REF_ROWS() formula, and migrating the list to a stored value in a physical column, you are removing that lists ability to automatically update.
Any time you want to get the list to update, you need to run a data change action or something to physically modify the value.
Fun note: the formula to get the list could be REF_ROWS(), it totally works (^_^)
Any chance you could please elaborate on your last comment @MultiTech ?
It seems to me you can only use raw text values as the inputs to REF_ROWS. I’m trying to generate a RELATED expression but the ref is an ENUMLIST. The question was asked also in the link below but everybody seemed to reply vaguely and in a not so helpful way… hehe. The original author ended up using FILTER() and IN(). Is it possible with REF_ROWS?
The REF_ROWS() formula syntax is specific in that it requires a single value column for the connection; this could be the REF type, or base type ref - it’s the “ref” nature that makes the magic work. But it’s very much requiring a single value, not a list.
Per the link you provided, the solution suggested is the way to go - though I dislike the brute force nature of the solution, as it increases the virtualization costs of your app (making it run slower). But when you’re dealing with a list of values, and you want to do a many-to-many like this, then you’ve got to brute force things.
I’ve got a couple of videos that highlight the specifics of implementing many-to-many connections like this:
Thanks so much for the reply and the video links. I’d been hoping it was going to be easier! haha. I suspect I may actually try to avoid all this work by seeing if I can live without the enumlists completely, just using single REFs (it’s just referencing images anyway…)
Have bookmarked your apps because I’m sure I’ll use them in the future. Thanks again for all the work you’ve put into your videos and this forum.
