Use Enum/EnumList (Base Type: REF) to De-Reference Data & Reduce Reverse-Reference Clutter

Hey Everyone.

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.

How we dealt with this in the past

To help compensate for these “extra” reverse reference lists, many years ago AppSheet put a “hack” of sorts into the system; where if you:

  1. Left the name of the column the same (ie. you didn’t rename the virtual column); and
  2. Changed the app formula from the system-generated ( REF_ROWS("Table", "Ref_Column") ) to LIST("").

If these two criteria were met, then AppSheet would leave that virtual column alone.

  • And a LIST("") formula processes extremely fast. :wink:

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. :wink:


Pulling a List of Values

[Related Whatevers][Column_I_Want_Data_From]
&
[EnumList_Ref_Column][Column_I_Want_Data_From]

Both function the same way; creating a list of values from the referenced table.


I’ve put together a simple sample app showing how to accomplish both, if you want to see it in action:
https://www.appsheet.com/samples/Showing-how-to-dereference-from-enum-columns?appGuidString=038fd74c-d059-4b10-8469-97ebeb542b90

19 Likes

Thank @MultiTech_Visions for this amazing trick

1 Like

@Guy_Merlin_Dyangnou you’re very welcome; this is a newer feature, but one all should know about!

partyparrot (Appsheet)

2 Likes

Hi @MultiTech_Visions thank you for posting.
What’s your main intention? Is it sync time, or just to have a cleaner column structure?

4 Likes

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. :slight_smile:


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

10 Likes

Oh my god, this is amazing ! thank you @MultiTech_Visions, i felt my brain explode once i understood what a smooth solution this is. Explody Parrot

3 Likes

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.

4 Likes

@Grant_Stead @MultiTech_Visions @Fabian @Guy_Merlin_Dyangnou @Rafael_ANEIC-PY

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.

I am opening a new post to discuss this.

4 Likes

Yes… There’s a lot that breaks interactive dashboards :rofl:

2 Likes