Sum values from child table

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 :slight_smile:

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.

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.

1 Like

[Fatigue]=[_THISROW].[Fatigue_ID]

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

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

2 Likes

@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! :slight_smile:

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. :grinning:

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

Cheers :grin:

1 Like

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 :sweat_smile:

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.

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

1 Like

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.

I knew I’m missing something! :slight_smile:

@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!!

3 Likes

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!

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.

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 :slightly_smiling_face:

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

2 Likes

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.

:smiley:

3 Likes