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

MultiTech
Participant V

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.

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.


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

31 13 10.4K
13 REPLIES 13

Thank @MultiTech_Visions for this amazing trick

MultiTech
Participant V

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

3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif

Hi @MultiTech_Visions thank you for posting.
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

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

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.

@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.

Yes… There’s a lot that breaks interactive dashboards

This works great.

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.

Thank you for taking the time to explain this.

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?

That is what I had to do (with help from the community)

Rifad
Participant V

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.

Screenshot 2023-08-15 at 12.49.33 PM.png

 

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.

Instead of this

Screenshot 2023-08-15 at 12.55.33 PM.png

To this  

Screenshot 2023-08-15 at 12.56.10 PM.png

 


@Rifad wrote:

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?


Yes!

  • 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 (^_^)

 

Top Labels in this Space