“How can I update a parent record when a child record is changed?”
“How can I mark all the line items in an invoice as paid when I mark the invoice as paid?”
“If I have App Formulas inside a physical column, how can I get those to update like a virtual column?”
“How can I recalculate formulas with a push of a button?”
All of these situations can be solved by implementing a Quick Update System inside your app.
What is the Quick Update System?
A way to update app formulas and recalculate values on the fly.
How does it work?
Through the fact that updating any value inside a record in an AppSheet app will cause ALL formulas on that row to recalculate (physical & virtual columns both).
What does it take to implement?
- A hidden column of the number type
- An action to add 1 to the current value of that column
- [Update_Column] + 1
A note on Naming Conventions
Once you start implementing systems like this, you can easily end up with hundreds of actions that you have to look through to find what you’re trying to fix or work with. The search feature is helpful, but to make things even easier it’s handy to adopt a naming convention.
My naming convention for Update actions is the following
- Update | Name_Of_Table
My naming convention for reference update actions
- Ref Update | Triggering_Table > Updating_Table
What else can this system be used for?
Once you have this system in place in all the tables in your app (that need updating like this), you can utilize reference actions to update large sets of records all at once.
Update All Child Records
You can easily update all the child records from a parent-child relationship inside your app
All you have to do is create a reference action that runs the update action over the [Related Child_Records] column.
What do I do when I’ve got more than one related table to update?
When you’ve got more than one [Related Whatever] column (and associated tables) that you need to update, the process is relatively the same - you just have to add in a composite action into the mix.
Update Parent from Child Record
Working in the opposite direction from the example above, you can utilize the exact same ref-update system to update a PARENT record from the child.
Setup is almost exactly the same (you can copy the same Ref Update action and just change a few things to speed things up).
Update Flagged Records
If you include an additional column in your tables that will allow you to flag columns in such a way, you can create a slice to pull those records out and a corresponding reference action that runs the update action only on that subset.
This is nice when you’ve got records or data that you need to upload into the system (and you’re not wanting to utilize the CSV upload feature);
- You can copy/paste your data directly into the table, with that flag I mentioned earlier set so these records are held in that slice, then run your update action over them with a single button push.
CRITICAL NOTE TO UNDERSTAND ABOUT WORKFLOWS
Update actions called through a workflow WILL LIKELY NOT update the app formulas for the row
- The only data change that happens is the [Update] column’s value increasing by one.
The true functionality for the AppSheet system is “supposed to” update ALL app formulas when you change data from a workflow - but in practice, more often than not, it’s as I describe above and only the actual [Update] number changes.
(This is one of the features of the platform that’s prone to “breaking” and reverting back to the functionality I’m describing here; they fix it, but eventually it breaks again - so I’d rather assume it’s broken all the time and this is how things function. I’m content to work within the constraints of something that will be reliable and constant in it’s behavior, vs building something that MIGHT work or might not.)