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:
- You have a table with settings values
- You have another table (let’s call it your “data table”) to which those values apply (show hide and formate rules)
- 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:
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:
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.
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.
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.