Solved: Expression to recalculate all rows in a table (chi...

CHRISTOPHE_CHAN · 01-31-2020 08:01 AM

Hi,

I try to set an action and a workflow to update correctly all rows of a table containing sums of related table rows. I need to update the sum colum of the parent table as soon as there is an add/edit/delete in the child table.

I think I understood the concept of worflows to watch changes in child table but I don’t understand what kind of expression is needed in the action to be linked with the workflow. Any suggestion ?

Thanks

MultiTech

This is what I was trying to show in the second video, it shows how I’ve setup a “Data: Execute an actin on a set of rows” action type to update the parent.

Instead of putting the summing column into the list([Column]) space, you need to put the ref link of the parent there. The system is looking for a reference, technically a list of references (or records).

These data change actions can be a little tricky:

Read more about actions here:

No this is not automatic, though it is pretty simple to setup:

Here’s a sample app that you can use to see how it’s all put together:
https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

View solution in original post

Steve

The first thing to consider is that a workflow is performed on the AppSheet servers when your app performs a sync. Consequently, a workflow-driven recalculation will not happen immediately–there will be a delay before the app user sees the updated values.

If the sum to be updated is maintained in the parent record, and your workflow is monitoring changes to child records, you would want to perform an action of type Data: execute an action on a set of rows that targets the parent row to perform an action of type Data: set the values of some columns in this row.

Aleksi

How about a virtual column for that purpose? Would that work?

CHRISTOPHE_CHAN

Sure it actually works like it right now but virtual column are not visible in the data base. So I add a normal column that copy the virtual one for that purpose. Anyway this is not perfectly satisfying because some wrong information could occur in the database at some point.

MultiTech

Instead of using a workflow to make this calculation, I would insert a ref-update action into the save action event of the form (or insert it into an action stack when someone pushes an action button) - this way the calculations are made right away and the app proceeds with updated info.

Here’s an example of how I have the lowest level in a parent-child database updating two levels above it whenever a form is saved.

Here’s a brief look at one of those Ref Update actions

CHRISTOPHE_CHAN

@MultiTech_Visions Hi thanks a lot. This way suits me.

I did not get a point on the second video. About the link list(log_phase_link). I am not sur to understand what I should put in here. In my case I have a sum column in parent table. Suming values of records of child table in a column called [Quantité UMO]. So I wrote here list([Quantité UMO]) but I get an error “The value ‘=list([Quantité UMO])’ of data action ‘Ref update MO’ does not match the expected type List or its expected type details”

Also about " insert this action to the save event of this form". Is this “insertion” automatic or should I find somewhere the “save event” to configure it ?

MultiTech

This is what I was trying to show in the second video, it shows how I’ve setup a “Data: Execute an actin on a set of rows” action type to update the parent.

Instead of putting the summing column into the list([Column]) space, you need to put the ref link of the parent there. The system is looking for a reference, technically a list of references (or records).

These data change actions can be a little tricky:

Read more about actions here:

No this is not automatic, though it is pretty simple to setup:

Here’s a sample app that you can use to see how it’s all put together:
https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

CHRISTOPHE_CHAN

Thanks @MultiTech_Visions a lot for these precisions. I got it and It works fine !

CHRISTOPHE_CHAN

@MultiTech_Visions Just another detail, is it possible to get the same kind of update when a record is deleted using the system generated delete button ?

MultiTech

Yes, but you’ll need to do some trickery to make it work:

CHRISTOPHE_CHAN

@MultiTech_Visions I was thinking about this trick precisely. However it doesn’t work and this is tricky while it is the exact same process as we discuss before and quite straightforward.

MultiTech

When you say it doesn’t work… what do you mean? What is or isn’t happening.

Based on what you’ve said you’re trying to accomplish, there should be the following things happening when someone pushes the delete button as the trigger:

some ref-updates happen
the row is deleted

Which isn’t happening? (I’m wondering if you’ll need to not use the system delete and instead create a separate delete action and use THAT in the stack. I know it’s weird, but sometimes things like this work.)

CHRISTOPHE_CHAN

On the new delete action, goruped, the delete action works but the recalculation does not. I use the same ref update action I created before which works perfectly.

MultiTech

Just to confirm, in the Grouped action the Delete action is the last one right? (Just covering all bases )

Try creating your own delete action (just copy the system generated one) and put that at the bottom instead.

If that doesn’t work… we’ll need to tag a dev to loop them in as that would be a bug.

Bellave_Jayaram

You could try this trick as the last action in the delete sequence - Sync Action?

CHRISTOPHE_CHAN

Actually I tried both ways as I do not have all bases but none working

MultiTech

@Adam @Aleksi This is beyond my capabilities to debug.

Bahbus

Using what we call the “forced sync” that @Bellave_Jayaram just linked to might get your recalculations, as it tells AppSheet to make sure all data sync’d is the latest it possibly can be.

CHRISTOPHE_CHAN

Thanks for the trick. Same reaction, this action works on itself (forced sync happening) but fail in a grouped one. If I put the delete first, I get the delete but no sync. If I do the opposite, I get sync but no delete.

Bellave_Jayaram

Sounds like you need to contact support@appsheet.com. I had a complex delete operation similar to yours and @Phil was able to add a fix that worked.

MultiTech

Sounds like the delete action is ignoring the fact that it’s inside a stack and jumping to the front of the line - so to speak.

Expression to recalculate all rows in a table (child/parent)