Re: Use Enum/EnumList (Base Type: REF) to De-Refer...

MultiTech · 12-08-2020 06:59 AM

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:

Left the name of the column the same (ie. you didn’t rename the virtual column); and
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...

Guy_Merlin_Dyan

Thank @MultiTech_Visions for this amazing trick

MultiTech

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

Fabian_Weller

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

MultiTech

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

Rafael_ANEIC-PY

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

Grant_Stead

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.

WillowMobileSys

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

Grant_Stead

Yes… There’s a lot that breaks interactive dashboards

Scatterscape_Ph

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.

Fabian_Weller

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?

WillowMobileSys

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

Rifad

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.

Instead of this

To this

MultiTech

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

leonsteber

Any chance you could please elaborate on your last comment @MultiTech ?

@MultiTech wrote:
Fun note: the formula to get the list could be REF_ROWS(), it totally works (^_^)

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?

How to create a REF_ROW when using a Enum List, base type Ref

Cheers!

To be specific, I'm trying to display a list of images from another table.

MultiTech

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:

View in Answer Portal

leonsteber

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.