I wanted to share a few techniques that I just implemented in my app.
A little background…I’m building a process in my app that will replace the email address with a template value. What it does is replace
ARCHIVEfirstname.lastname@example.org. The email column is a row owner column. Once
ARCHIVE is appended to the email, it becomes an unowned row and is permanently hidden from the app. I’m doing this in an effort to speed up my app by hiding data that’s more than 2 years old. Unowned rows will not be downloaded, and thus, unnecessary computations won’t be performed on those old rows. That should hopefully speed up the app by reducing the amount of data that has to be processed.
I’m doing this for a table that tracks individual student lessons that are taught by a coach. This table gets around 10 new rows per day. This table has become quite large with data going back to the middle of 2019. In that table, I have a math column that subtracts the year that the lesson was taught from the current year and gives me the difference between the years. Then I have an IF column (called CanArchive) that returns
true if that lesson is in any year that is older than the most current two years. This means that the row can be archived.
I also have a working table that I use as the driver for my tab to display a list of lessons, as well as using it for a custom form to add new lessons to the lessons table. In that working table, I have a basic boolean column that’s marked as checked/true. I then use that true value to build a single relation to my Lessons table and match up to the first matching row where the CanArchive value is also true. Then I do a lookup of the coach’s email, and follow that with a template column that appends
ARCHIVE- to that email. Through a custom action that opens the form, as well as an action that submits (Add Row) the form, I perform a Set Column action that overwrites the email in the Lessons table with the template archive email through the single relation to the Lessons table. This works great and every time the form is opened and submitted, not only will it add a new row to the Lessons table, but it will also change the email in two of the old rows to the archive email, so those old rows now become unowned rows and disappear from the app. Over time, more and more rows will become “archived” and I won’t need to move those rows to a separate sheet. They will just become unowned. This will reduce the number of rows downloaded to the user’s device to just the most recent two years worth.
Now, one thing that worried me is the fact that I use ‘true’ to build my single relation to the Lessons table. What if I happened to uncheck the boolean value accidentally? If that would happen, then all of a sudden, my single relation to the Lessons table would start relating to data in the current two years, and it’s possible that it could start archiving lesson rows that are actually within the current two years. So, I added a sanity check. What I did is create a lookup column that retrieves the CanArchive value from the Lessons table. Then I have an IF column that checks if that CanArchive value is true. If it is, then I return true. Ultimately I have an IF condition in my custom actions that checks if the Sanity Check value is true. If it is, then I will first do a Set Column action to set the email to the archive email, followed by a Show Detail Screen action (if I’m going into the custom form), or an Add Row action (if I’m submitting the form). Otherwise, the custom action will go down another path in the custom action, which will not archive any old rows, but will still perform the Show Detail Screen or Add Row action.
Another trick I figured out is that I can display a single custom form (as a reusable form) called from multiple different actions throughout the app without having to rebuild the custom form each time. There’s been discussion about the advantages and disadvantages of using either the User Profile table for a custom form, or using a separate single row working table for a custom form. I tend to use a separate single row working table more often and I realized an advantage to doing that. I have a tab that is driven from this working table. None of the values from that table are displayed in the tab. Instead I only display an inline list from my Lessons table and a couple of floating buttons. One thing about how glide works is that a detail screen that’s the top level of a tab, or navigated to via a Show New Screen action, will always allow for an independent detail screen layout. However, if you navigate to a detail screen through and inline list, or through a Show Detail Screen action, then that layout is forever attached to the table. Using this knowledge, I realized that my tab can be sourced from the working table, which will have it’s own independent layout, but also, when I use a Show Detail Screen → This Item instead of a Show New Screen → This item, to navigate to my custom form screen, it goes to the detail screen that is attached to the table. By using the Show Detail Screen action instead, I can then call that same Show Detail Screen action, to the working table, from anywhere in my app and end up on the same custom form screen without having to rebuild it. So with a working table to act as a custom form, along with a Show Detail Screen action to navigate to that form, you can then reuse that form anywhere in your app, and you can makes changes to that form once without having reproduce those changes in multiple forms. The only additional thing you need to do this, is add a Single Value column in your other tables that refer to the Whole Row of that working table. Then the Show Detail Screen action will allow you to pick that single value row, which will be your custom form.
It’s been awhile since I’ve made some major changes to my app, so this was a fun little experience. Hope it gives the rest of you some ideas.