Quick Update System - How to update records in your app with a push of a button

[MultiTech]

“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?

1. A hidden column of the number type
2. 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.

---

[SkrOYC]

Nope, you update the row that has the expression you want recalculated

[Markus_Malessa]

Apologies, I had it reversed. Yes, you are correct I would manually update a child row in my case, but then I would still need to run an action on the parent row. In this case I would have to either update the column in the parent table via expression run in that action or update another column which would recalculate that row right? There is not an action that would simply just 'save' the parent row?

[SkrOYC]

If you use an extra column on your table to update the row, you could have an App Formula that will get updated when you update the Update column.

If you don't have an Update column, you can use an action to directly update the column but that column can't have an App Formula.

You need two actions.

One in the parent table to do either of the two things I said in the beginning
One in the child table to "Execute an action on a set of rows" where the table is the parent, the action is the one you just created and the rows is the FK from the current row. AppSheet expects a list, so wrap it with LIST()

LIST() - AppSheet Help

Of course, none of this is needed if you use a virtual column in the parent table

[jennym]

I've implemented this quick update system, triggered through bots. However, I'm finding that most of my columns with the app formula type (not virtual) are NOT being updated. Even though the [Update_Column] has been updated as has the Change Timestamp column.

Reading through this thread, I found, "Update actions called through a workflow WILL LIKELY NOT update the app formulas for the row".  I did not consider bots to be a workflow-- am I wrong? Should my columns with app formulas be updating as well as Update number column? 

Anybody else experiencing this?

[SkrOYC]

A workflow is what Bots was called back in the day.
If you want instant change, use actions, which are going to run on the client's side.

I found that using bots works but you need to understand that those changes are not going to be propagated to the client

[jennym]

Thank you. The changes do not need to be instantaneous. Potentially, hundreds of child rows would be affected by the parent action which is why I moved the action into bots. 

I have around 5 columns that have an app formula as the content, dependent on each other. It seems like only some of these formulas update when the quick update action is run, while others have the old content.

If the quick update system updates all columns with no issues, I will assume there is a problem somewhere in my formulas or composite actions.

[SkrOYC]

When using AppFormulas, instead of Initial Value, it should always update. Could you confirm it's actually AppFormula?
Finally, you could also try adding the expression right into the action, but that shouldn't be allowed if the column has an AppFormula, but may fit your needs having it in an action instead of the AppFormula section

[jennym]

I can confirm that the columns are of the AppFormula type. I have thought of changing these to text type and inputting the formulas into the initial value, then updating these directly with the action as you suggested. A little more work, but it's absolutely crucial that these update correctly. 

Perhaps the order of the columns are important? For columns with the AppFormula type, if Column1 depends on values from Column3, but the action updates Column 1 first.....

[jennym]

Also note, when editing the child directly, all changes are correct.