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:
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.
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.
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!
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.
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
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!
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.
@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()
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.
I follow the same practice - I know @MultiTech_Visions has done a write-up on where ANY() and Index(,1) seem to differ.
Format Rules with a deref expression may work better than using a VC.