How to instantly apply "global" format rules

Postscript: After posting this @MultiTech_Visions helped me see that my approach is not the best one after all. My big takeaway from the help I got is that, in the situation I describe below, it is much better to use INDEX() in the format rule than LOOKUP() to find values. My situation is rather unique in that I’m working on two free apps that people copy as samples and then user as single-user apps. So, my Settings table is basically the same as Multitech_Visions’ User table but it only has one row as there is only one user. Another takeaway may be that using an action to write something to your sheet can force a calculation and provide the user with immediate feedback, instead of waiting for a sync. That was what I found and reported on in my original post. Thanks again to MultiTech_Visions!

First of all, let me provide a brief description of the kind of situation in which this tip should be useful:

  1. You have a table with settings values
  2. You have another table (let’s call it your “data table”) to which those values apply (show hide and formate rules)
  3. You want to be able to change the settings values from any record in the data table and have the changes reflected instantly in all of the records of the data table

My tip is about how to accomplish this without getting a warning from AppSheet that says your format rule “may be expensive and slow the user experience of your app.”

Approach No. 1: Format rule works instantly but warning generated

Here’s a illustration of this first approach, which I don’t recommend:

All of the records in the data table (of which there are 8,000 in my app) use a LOOKUP() expression in the format rule. It generates an error but in the editor, it seems to work as follows:

vertical1a.2021-06-25 21_35_43

A parallel detail view holds a long list of records that may be displayed in the first (and main) detail view you see. When you tap on the “show/hide” icon, a value on the settings table is changed by an action and then a LOOKUP() expression in the format rule changes the appearance of the show/hide icon. It seems to work well but a warning is generated. At first, I thought I might be able to ignore the warning but today I noticed that the large number of such format rules I was using caused problems on mobile devices. So, I had to find the approach I’m explaining in this tip.

Postscript note: The solution that @MultiTech_Visions’ comments led me to is basically an adjustment to this first approach. I just use INDEX() instead of LOOKUP() to get the required value from the settings table. That gets rid of the warning regarding the format rule and eliminates the need for the virtual columns, etc. that I describe in what follows.

Approach No. 2: No warning but format rule must wait for sync

This approach uses a virtual column to hold the value taken from the settings table as follows:

This approach doesn’t cause a warning to be generated but it’s not instantaneous:

vertical2.2021-06-25 21_36_50

As you can see, my use of the virtual column results in a long wait (the actually wait time was even longer) for the sync to end. Not good enough. :frowning:

Approach No. 3: Good performance, no error

Here’s my illustration of this approach:

It’s basically the same as the previous approach except that I use a grouped action to write the value written to the settings table in the row from which the change is made. This value is never used. It’s only function is to tell the AppSheet platform to recalculate the virtual column values immediately – which it does quite dutifully.

vertical3.2021-06-25 21_39_00

This looks almost the same as my first approach except that the number of values to be synced is now 2, not 1.

That’s it. I hope there are some people out there for whom this tip proves useful.

By the way, this tip is an extension of a previous tip I wrote about the utility of virtual columns when building format rules:

Several experienced app builders on this forum agreed that virtual columns are useful for format rules. Please check out the thread for detailed discussion, including some caveats.

5 Likes

Such a great breakdown of the various different ways of doing something. So many times, when making a post trying to communicate something about an AppSheet app, we (the people making the post) can forget to “show our work.” Kudos to you @Kirk_Masden for such a detailed a thorough approach.


That being said… do you use a Current_User (Slice)?

  • Whenever I need to apply a global variable like this, I include a column inside the User table

Here’s an example of the “Settings” section inside the User table for a production app of mine

  • 20 variables in total
  • with some extra space at the end for more

After I have a column inside the Users table, then I can create SET actions to change the values…

…and Ref Set actions to execute those changes from anywhere.

Last piece of the puzzle is to include show if formulas that pull a value from the Current_User (slice)

Final result looks like this:


Would something like that fit your scenario?

3 Likes

Thanks so much, @MultiTech_Visions ! Although I’ve been using AppSheet for about 5 years (I remember watching MultiTech_Visions videos when I was getting started), I have never tried to use the user settings. Part of the reason is that I’ve only worked on free sample apps that can be copied for use by single individuals.

As a beginner regarding user settings, I went to the documentation:

I think I can see now that user settings are very useful because data is not stored on a spreadsheet and so syncing isn’t required. And, apparently, values can be accessed without using LOOKUP() – a big plus.

One limitation that concerned me a little is the following:

Unlike normal tables, the user settings are stored on the device itself, not in the cloud. Consequently, user settings do not follow the user between devices.

As I indicated above, my app will be shared as a free sample and then used with one user per app. I would like that one user to be able to change the settings on one device and then have those same settings reflected when the app is synced on another device. So, given my rather unusual usage of the AppSheet platform, I think I’ll stick with the workaround I outlined here.

Still, I’m glad to have learned about user settings! Thanks again!

1 Like

To be clear - I’m not advocating the use of UserSettings (as you’ve linked to the documentation above).

I’m advocating your create your OWN “Settings section” inside a Users table.

UserSettings is… well let’s just say it has it’s difficulties, and a much smoother interface will be achieved through the use of an actual table with one record per user.

The implementation I detailed above doesn’t make use of UserSettings - it’s from the “User” table I’ve included inside my app.

1 Like

Sorry for my confusion and thanks for the explanation. I’ll study up on this some more.

Since my app is single user and I only have one row in my settlings table it looks like my big take away from your very helpful post will be using INDEX() instead of LOOKUP() to get values from my settings table. I’ll report back after I try it out.

I guess what I wrote in this tip may only wind up underscoring the following point: There are times when making your app write to the sheet can force a calculation and so give the user immediate feedback about a change.

The setting information is stored in the cloud, so it can apply to any device. If you wanted to include something that was device specific, you might need to do two column variables… one to control a PC setting, in another to control a device setting.

Best
Matt

1 Like

Wow! It was super easy to just replace my LOOKUP() expression with an INDEX() expression and that got rid of the warning regarding my format rule. Moreover, doing this seems to have eliminated the need for the extra virtual column and real column. This solution is MUCH better for my particular case than the solution I wrote up. Thanks again to @MultiTech_Visions and apologies for being so dense.

1 Like

Thanks! I should say that I edited my question, which included something about device specific changes, after it occurred to me that it was a pretty stupid question after all.

This discussion has REALLY helped me, though it took me a while to see it. Thanks again!

3 Likes

@MultiTech_Visions @Steve is there any advantage in using INDEX instead of ANY?
INDEX(User[Column],1)
instead of
ANY(User[Column])

I have one row per user.
The security filter for this table is set to:
[UserEmail]=USEREMAIL()

1 Like

In the past, in obscure edge cases, I’ve seen some difference in behavior between ANY(...) and INDEX(..., 1), but I typically use ANY() for brevity (it’s shorter and looks better) unless I stumble across one of those edge cases. Off the top of my I head, I don’t recall what those edge cases were.

4 Likes

I follow the same practice - I know @MultiTech_Visions has done a write-up on where ANY() and Index(,1) seem to differ.

3 Likes

I have???

:thinking:


I have now! Small moonwalker

2 Likes