Confused by an Action

[scott192]

From my understanding with various topics, I know that Formulas that are assigned to fields only recalculate whenever an item in the row is modified.

For instance I have a Parent/Children setup where one of the parent fields holds the total cost of the children. SUM([Related PackageItems][RowCost]) is the formula I use.

Whenever I modify or add a child record, I use that Form’s behaviour section to call an update to a timestamp field on the Parent record…a last modified = NOW() sort of expression. This dutifully causes the SUM([Related PackageItems][RowCost]) formula to update and the correct totals are always displayed in the client window in real time.

However, here is the problem I am having…

If I use an Action construct called ‘Execute an Action on a set of Rows’ and set my last modified = NOW() formula on each of the rows in sequence…The last modified field updates as usual, BUT the SUM([Related PackageItems][RowCost]) formula on each of those rows does NOT recalculate.

If I then touch one of the child records and save the form it instantly recalculates as normal.

Is this a BUG, or are Formulas deliberately excluded from being updated with the ‘Execute an Action on a set of Rows’ behaviour?

[Aurelien]

Hi @Scott_Robinson1,

I don’t know if this is a bug, but I don’t think so. I think what you built is more a workaround, as App Formula are supposed to be calculated on edition mode.

help.appsheet.com

App Formulas and Initial Values

How you can set app formulas and initial values to dynamically assign values.

I notice this in the article above:

Each time such a change occurs in the form, the App Formula of every affected column in the record is evaluated and the column is assigned the result of the formula evaluation

On a general way, formulas “written down” are unreliable when it comes to iterations, in my opinion.
The situation you describe is typically the one that make me cautious about it.

I suggest, and this is my humble opinion, that you use virtual column instead, as these are “always true” (modulo a short sync time), calculated “on the fly”, and you can export these whenever you need it through an action button.
Moreover, this will be more efficient and avoid some pain into workarounding the formula update.
For reference:

help.appsheet.com

Virtual Columns

Check out how you can use virtual columns to add more information to your app.

[Steve]

If the child rows have an is-part-of relationship to the parent, there is some AppSheet magic that (sometimes?) updates parent expressions that reference those children when those children are updated. I’ve never understood what that magic is, but that magic may explain the behavior you’r seeing.

[Koichi_Tsuji]

I could not repro the problem.

[scott192]

Hey Steve,

I did have the is-part-of set on the parent and children. I have since taken away that setting and the problem still exists.

I feel there may be a little misunderstanding from the replies on what it is that is happening, so rather than write it all down, I will endeavour to make a video that shows whay I mean instead and post it back here.

[Koichi_Tsuji]

I always try to avoid to update fields with NOW() type of expression to update the physical fields.
It is possible that you may set the fields to time column. If you are quick enough to update row value and do transaction to change the data within the “same min” then the action is to return the same value.
Then it will not trigger the update for the rest of the column for the rest of the fields on the same row.

Better to set your action to trigger to update the row values (to recal the app formula) by triggering the update the column value by incrementing number.

What i do always to set the number type column, then set the action to increment number for that fields by action, just increent by 1 by action. Then the rest of fieds value would be recalc regardless of the type of actions.

@Steve

[scott192]

So as promised, here are a couple of videos recorded using Screencastify which has a 5 minute recording limit…sorry for the break

Video 1 Screencastify

Followed by Video 2 Screencastify

Sorry for the waffly nature…you would think when set up against a time limit it would focus you…but it didnt!

Thanks again in advance for the feedback and @tsuji_koichi I have looked at what you say about incrementing a value rather than storing a datetime…I may investigate doing both at the same time as I do need to store the datetime value as well.

Thanks to you all.

[Koichi_Tsuji]

@Scott_Robinson1
Thanks for the sharing the movie.
Your use case was more complicated rather than you mentioned earliers.

My initial impression (after wathing your movie) is your coloing action to copy pareng along with child record) is fired at first by creatine new cloned parent records. Then your resst of action will creat the clones chunk of child record.
At the time when your new parent new record is created, then no child records are generated, so your target sum field to return the null.

I would sugget (no testing), but you push another action after adding child cloned records attached to cloned new parent, then you add anothe action to update the same cloned record by passing now() expresion against the newly created single record.

1. copy for new parent records.
2. copy the chunk of child records.
3. then update the time stamp field for 1) recoard (newly created parent)

Just add one more action to update the timestamp for the newly created parent record. I believe it will solve your problem.

[Koichi_Tsuji]

or other than adding new action, on your grouped action, just swich the sequenct, i.e. add child clone records first then add parent new row.
Again, not tested, curious to see what the result is going to be. If this swapping the action not help, then i still believe the addiion new action to update newly created parent record will solve the problem.

[scott192 · Accepted solution]

@tsuji_koichi you absolutely nailed it!

I switched the sequence around. I added the cloned child records first and then added the new parent for those children! A simple matter to swap 2 actions in my sequence.

Nicely spotted bit of logical trickery there sir! Many thanks for taking the time to respond and thanks to the community for being there to solve little gotchas like this!

Scott

[Koichi_Tsuji]

You are welcome, @Scott_Robinson1
Good to hear you solved the problem instantly, by just swapping actions.

This is one of headachel, but as well as fun with appsheet.

Appsheet is no-code platform, but the subject we discussed here is almost codings stuffs. haha.

i learned new lession from your post, thanks.

[Suvrutt_Gurjar]

Welldone Koichi San !

This was indeed a tricky one.

[Koichi_Tsuji]

We got new knowledge.

[scott192]

It’s actually better than that as well as I went back and redid a few things after this solve.

I managed to remove an ‘Execute an Action on a set of Rows’ step and replace it with the ‘Data : Set the value of some columns in this row’ step instead…I bet it saved a few processor cycles downstream and helped the return from sync refresh faster

[Koichi_Tsuji]

This is funky for me. Parent is always come first otherwise no kids. But on this particular case, kids comes first followed by parent.
Logically I understand why it happens on Appsheet, but it is not true on our real world, haha.

[scott192]

Yeah, it’s the same here. I doubt a full on ‘proper’ RDMS would allow you to create ‘orphaned’ records in this way.

I hope some future updates to the way Appsheet behaves won’t break it…

[Koichi_Tsuji]

On this particular case , the lessons we learned is the Appsheet sequence of actions are done in sequences , async ways.

There is no syntax , to define async or not, but beauty of Appsheet could be we just swap the action sequence by drag and drop to see different returns.

I’m not google boy.