Sum values from child table

[Alejandra_Petro]

Hi there,

I got two tables:

Parent: Fatigue
Child: Break_Fatigue

I’m trying to add together the values from the [Total Break] column in the ‘Break_fatigue’ table. And I want the result to show up in the table “Fatigue” on the column [Total Break]. so I’m using the expression below but for some reason is not working and I can not figure out why. I will appreciate your help with this one please

SUM(SELECT(Break_Fatigue[Total Break],[Fatigue_ID]=[_THISROW].[Fatigue_ID]))

Note: when I test the expression, this Error comes up: ‘[ID].[Fatigue_ID]’ : Unable to find column ‘Fatigue_ID’. I have tried to change the type colum to REF (Break_Fatigue) and still not working.

[Koichi_Tsuji]

I saw same sort of problems before, my best assumption is [_thisrow] expression not working as expected. What is going to happen if you remove [_thisrow] ?

And I suggest you take a step by step approach to debug the issue. Firstly, what is going to happen to get the list type of virtual column just to see if appsheet expression is pulling the set of target column correctly, just simply adding select expression, which is like

SELECT(Break_Fatigue[Total Break],[Fatigue_ID]=[Fatigue_ID])

If this does pull the list of correct set of the number for each target row, the expression with sum() should work.

[AlexM]

[Fatigue]=[_THISROW].[Fatigue_ID]

It’s the ID column from Parent table and the Fatigue column in the Child table

[Jervz]

Hi @Alejandra_Petro, you need to make sure that the [Fatigue_ID] in Break_Fatigue is type REF and the source table is Fatigue.

After that you can simply use
SUM([Related Break_Fatigues][Total Break])

[Alejandra_Petro]

@tsuji_koichi, @Jervz both Expressions work but just when I edit the parent row. If I add a child row without editing the parent row the expression does not work. Does that make sense?. is there any way for these expressions to work without editing the parent table or is there any way to take out the option “new” so the user knows he has to add the new entry by editing the parent row.

thanks a lot for your help!

[Jervz · Accepted solution]

I’m assuming you are not using a virtual column for the Parent’s Total Break which is good.
In that case, you need to create an action to update the parent’s record everytime you add or edit the child records.

so instead of putting the expression in the column, transfer that to an action and put it in your form to trigger it.
You can check this sample i made for you.

https://www.appsheet.com/samples/Sample-App?appGuidString=3a3ac445-ca26-4ee4-b93f-cc804ced49d5

Cheers

[AlexM]

Why is it good not to use a VC?
I am and I was thinking that I am not having the value saved in the spreadsheet but I can’t see why that would be an issue.
I feel like I’m missing something

[Jervz]

It is really not bad to use VC if you are only expecting to have few records. No problem for now but in the future when you have like more than 5 thousand records, you will notice the app is getting slower and slower. Imagine recalculating that VC for 5-10 thousand times.

[AlexM]

I knew I’m missing something!

[WillowMobileSys]

@Jervz @AlexM

Keep in mind there is a trade off on syncs - time to read the column from data vs time to calculate the VC. Which is faster I don’t know.

I can say that in the computing world the slowest operation is read/write of data, for cloud based services you need to add transmit time of data from source to device. Because of this I tend to think that VC’s are actually faster - when performing standard calculations.

Where many developers get into trouble with poor performance, is that they create VC’s which need to perform SELECT()'s. SELECT()'s against a table MAY need to perform a scan of that entire table to identify the resulting rows. If that table grows, the SELECT() itself takes longer.

So, when a Sync is run, not only do times suffer as this main table grows but also as the table that the SELECT() is against grows.

I think this is why VC’s get a bad rap on Sync performance!!

[Alejandra_Petro]

Hi @Jervz, Thanks for your help.

I have taken a look to the sample you made and Im a bit confused with the “Create child 1” and “Create child 2” actions you created.

I noticed when I create a new parent then add a new child and hit save, the app adds another 2 childs. I do not want my app to create breaks automatically, the user must add this. so I have tried to create the actions you have implemented, except “Create child 1” and “Create child 2”, but It does not work.

Also, I do not want to add a view for the “Break_Fatigue” table. I want the users to add their breaks through the “Fatigue” table or view.

I feel I’m close to finding a solution to this problem with your help, so would you mind to explain a bit how you created these actions in order to get the right value on the [Total] column.

Thank you very much!

[Jervz]

Sorry for the confusion… That was not even relevant, i just used an old app but i forgot to remove those actions.

Here is a better one, hope this will solve your problem

https://www.appsheet.com/samples/Sample-App?appGuidString=d7eeb9da-ec7e-4188-9b82-e7f441b896c3

[Leo-Enoch]

Hi @Jervz

The app sample link does not work anymore. I think this is also something that is relevant to feature in the app I am building. would appreciate if you can share a sample of that again.

@Alejandra_Petro , appreciate if you can share how you manage to solve the problems

Thank you.

[Alejandra_Petro]

Yep, this was the solution to my problem! THANKS a lot for that. You are awesome!
I was missing one step but I figured it out at the end.

[Koichi_Tsuji]

Im now little bit confused by seeing all the comments after that. To see exactly what is happening and what you would like to achieve, i need to access and investigate your app itself, as i suspect some confusions are around there.

[WillowMobileSys]

@Alejandra_Petro What you are experiencing is a common issue among developers. I created a solution highlighted in the post included below.

Background

There are totals in the Parent Form that need to be updated when a child record is updated. You need to activate these calcs by accessing the Parent Form.

When you perform the “normal” edit path, you would:

1. launch the Parent Form
2. select the child to edit
3. save the child which takes you back to the Parent where the calculations can be activated
4. lastly save the Parent which performs a “group save” with any child changes as well as any Parent changes.

The Short Version of the Solution

But, when you edit from the Child directly, you do not access the Parent Form. My fix to this was to simulate the “normal” edit path and FORCE navigation to the Parent Form once the Child is saved. This will activate the Parent calcs. It does mean you have to save twice but you have to do that in the “normal” edit path as well.

If you are not sure what I mean, there is a “Form Saved” property that you can attach your own Action. This action can use the LINKTOFORM() function to navigate to the Parent.

There are some side effects you may want to be aware of so read the post below for more details.

The Long Version

What is best approach for Parent Updates in UI when making Child Add/Edit/Deletes? Questions

This post is a bit long but is an important topic probably for all of us, if not now then in the future. Several threads have discussed it but with no real conclusion on best approach. The reason, I have discovered, is that there are trade-offs depending on developer needs. The intent here is to gather all the info and issues with these particularly common use cases and discuss different workaround approaches until enhancements can be made to improve on these needs. This way we all can bene…

[Bellave_Jayaram]

You can use a data change workflow with actions to set the Total Break (and if there is an app formula there, remove it) in the Parent whenever a new row is added/updated/deleted in the Child.

[Alejandra_Petro]

Hi @Bellave_Jayaram, Thanks for your response.

would you mind to explain a little bit more about how to do it?
sorry if I’m too demanding but I really don’t have idea on how to get the actions right.