LinkedIn
Twitter
“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 BOTS
Update actions called through a bot 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 bot's task - but in practice, more often than not, it’s as I describe above and only the actual [Update] number changes.
I call these bugs "see-saw bugs" - it's prone to “breaking” and reverting back to the functionality I’m describing here; they fix it, but eventually it breaks again. 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.)
I find that if I keep the updates inside the app - utilizing the actions described above - I have no problems with things updating.
Matt the visionary has done it again ! thank you, i’ll send anyone asking update-related questions straight to this post.
Oh man, that’s a bummer for sure, i’ve got some changes to make then, thanks for the heads up !
Thank you Matt for another cool trick! I’ve been studying this for a while now & would like to ask…
If I wanted to Set the ‘Status’ values on the child table [Related_Line Items], how would you recommend I do this? Can I add my ([Status]=“In Progress”) in with the existing ([Update]+1) Action or should I create a new Action to Set other columns & then composite them all within the Ref Update?
Thanks also for the tips on your naming system… I’ll be using this from now on to help keep me organised 
Ok, thats what I thought so just keep the Update actions to [Update]+1 on their own.
What caught my attention at the start of your post was “How can I mark all the line items in an invoice as paid when I mark the invoice as paid?”. I find that I can have several uses for doing this sort of action or something similar.
What I’m trying to do is when I ‘confirm’(action) an Order from the orders Table, I also want to update the Status of all the [Related Order Details] to “Work Order Created”.
I can see that you ‘very cleverly’ use the [Related_Whatever] columns to Update the child records & LIST([Ref_Col] to Update the Parent tables & so I was just trying to use these techniques in my apps.
Am I right in thinking that the ‘Quick Update’ is best used for Tables that already have App Formulas inside?
I’m still a ‘White Belt’ in the Appsheet Arts
Thanks for the help!
I am able to update 1 row at a time but I want to update all rows in the table when we click the button.
Please let me know how to do this. Thank You! 
Hello @Total_Solutions, please see the section above:
The method is the same as that, but instead of passing the [Related Whatever] column into the “Referenced Rows” space, you instead pass it the table you wish to update:
- Specifying the appropriate table (in the “Referenced Table” dropdown) and the associated Update action (in the “Referenced Action” dropdown.
True... but you lose the ability to see how many times a record has been edited
That data point, the [Update] column, has actually helped identify certain records in system in the past, where users were making tons of edits - which then helped us identify pain points that needed to be addressed.
- When you see one record has 259 edits, vs. all the others with 4.... clearly something is going on... (^_^)
The [Update] column is a part of my Standard Starting Template, which also includes a TON of other metadata columns, all of which have evolved over time.
As I was working on production-level apps, whenever I fell into a problem that I wish I would have had something in place to solve... I added a solution to that problem inside this auto-table;
- this way whenever I fell into that hole again, I had something in place to prevent the fall
----------------------------------------------------------------------------------------------------
The beauty, and the curse, of the AppSheet platform....
