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

[MultiTech_Visions]

“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 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.)

I find that if I keep the updates inside the app - utilizing the actions described above - I have no problems with things updating.

---

[Rafael_ANEIC-PY (Rafael Almeida)]

Matt the visionary has done it again ! thank you, i’ll send anyone asking update-related questions straight to this post.

@MultiTech_Visions

Update actions called through a workflow WILL LIKELY NOT update the app formulas for the row

Oh man, that’s a bummer for sure, i’ve got some changes to make then, thanks for the heads up !

[WillowMobileSystems (John Baer)]

@MultiTech_Visions

Update actions called through a workflow WILL LIKELY NOT update the app formulas for the row

@MultiTech_Visions

The true functionality for the AppSheet system is “supposed to” update ALL app formulas when you change data from a workflow

I’m not sure I agree with the top statement. So, far I have not seen an issue where an App Formula was supposed to update a value and didn’t.

It should be made clear that App Formulas in OTHER rows (e.g. a parent row) will not fire even though they depend on the row being changed. Those OTHER rows must be “touched” (i.e. edited) in order for its App Formulas to update.

Taking the bottom statement as the expected behavior, we should implement our systems with that expectation and when it doesn’t happen open it as a bug to AppSheet to address the issue. Workflows are extremely important functionalities. They and all the bits and bobs should just work!

[Brand-It (Darren Friel)]

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

[MultiTech_Visions]

The action used for this update system should be kept isolated by itself;

• this way when you need to “update the record” you can easily call the update action and have confidence that it’s not going to do anything else.

If you need to “update a record” in addition to doing something else, that’s what composite actions are for.

---

For this sort of interaction, @Brand-It I have to first ask:

• Are you essentially “copying” the status of the parent into each of the child records?

If so, why copy that data there - it’s already present on the parent level.

---

But to answer your questions directly:

• You’ll need to make use of a ref action to update the [Related Child Records] from the parent;
  • but instead of the “update” action, create your own action that sets the [Status] column to whatever value.

[Brand-It (Darren Friel)]

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!

[MultiTech_Visions]

@Brand-It

Am I right in thinking that the ‘Quick Update’ is best used for Tables that already have App Formulas inside?

Yes, that’s exactly what they were intended for.

@Brand-It

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

For the scenario I’m talking about here, what I did was create a field that had an App Formula that contained logic to check various fields and data to determine the status of something - then all you have to do is call the update action on the child records, and all their [Status] fields will update.

[MultiTech_Visions]

@Brand-It

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”.

Wait… so you’re storing the status of the parent… on each of the child records?

Why not just suffice with that data on the parent level? What purpose does it serve having that on the child level?

You might check out “data normalization” as it’s something very helpful to know when developing your data schema.

[Brand-It (Darren Friel)]

@MultiTech_Visions

What purpose does it serve having that on the child level?

Well, my ‘Order Details’ (Child) also holds a job specification on each row. It is used for the “Quotation” to begin with then, when the status changes to [Status]=“Work Order Created” it becomes the specification for a copied instance of a new 'Work Order".
I use this “Work Order Created” change, to filter out the data in my “Work Order Request” valid_if column.

I’ve been using ideas from Ty’s “Medical Tasks” workflow sample app where I can create a new work order with all the associated Tasks attached.

Anyway I don’t want to hijack this post with my issues! Lol

[teambelair (Alex Truong)]

Really cool. Thank you @MultiTech_Visions !

This tutorial would have saved me a lot of time when I looked into that. I ended up creating a Date field that I updated with NOW() to refresh the record’s fields and keep a log on when it happened.

I did try to use Workflow in some instances but that did not work for me as it was too slow.

[AlexM (Alex Meraru)]

Just WOW!

BUT, I have an app for freelancers to create jobs FIRST and then creating a weekly Jobs List.

The way I used to do it is by having a VC in the Jobs table with the formula

Lookup([_THISROW].[WeekNum],Jobs Lists,“WeekNum”,ID)

I have tried the Update system but if the related column in the parent table (Jobs Lists) is a VC, it works, if it’s a physical one, the update action doesn’t work anymore.

That is the only change I have made again and again and with it being VC, works, Update column gets +1, with physical, it doesn’t.

I have checked the related columns to be ref.

ideas?

[AlexM (Alex Meraru)]

Got it!

Because at the time I want to run the action, the child’s ref column is blank.
The parent record was not created so there is no relationship between them just after the parent is created.

I have solved it by using the next formula in the parent update action for related records

SELECT(Jobs[ID],ISBLANK([Job List]))

[romanus (Romanus Edy Prabowo)]

I love this magic… thank you @MultiTech_Visions

[Fabio_Nabuco_Correia]

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).

Is there a way to, instead of changing the value for a fixed value ( in this case +1), changing it with the value of another form?

For example: I have a form being filled. I’d like that the value of some column that feeds table A to change the value of certain columns in table B. Both summing or deducing from its value.

Regards;

Fábio.

[MultiTech_Visions]

@Fabio_Nabuco_Correia

Both summing or deducing from its value

Sounds like a perfect case for the quick update system.

You’ll need to use an app formula to “sum and deduce” - it’s from the quick update that we cause those formulas to update.

[AY2450 (Adam Y)]

Thankyou - just what I was looking for …LEGEND

[Fabio_Nabuco_Correia]

Thanks for your reply.

I’ve been trying it for a long time, and still can’t find a way to make it work.

This part is already done. Although I can’t find anywhere some path to follow in order to change the value of a parent column with the value of the child table.

For example:

• [Update_Column] + 1
  if you wanted to change the value of the [Update_Column], instead of +1, make the below change:
  [Update_Column] + The value of the child row being input in the child form,

Is there a way to do so?

Truly appreciate your help .

Regards;

Fábio

[1111 (Ami)]

Hi

What if i want to run the update on all the rows of my parent table from my parent table? What do i ref in the sequence action? Is using LIST([_THISROW]) ok?

[MultiTech_Visions]

Hey @1111

Essentially what you’re saying here is you want to update every row in the parent table, and you can accomplish this by simply calling every record from that table:

Table[TableKey]