SUM Field

[blueprint]

I would like to know what the best method is to have a field whose content is populated from a SUM database query.

Say, for example, I use the Case entity and introduce a CaseHours entity which keeps track of time spent on a case. Multiple CaseHours entries could be created against a Case.

Currently, if I want to see the total hours spent against a Case, I do the following inside the `entityDefs` file `case.json`:

"select: (SELECT SUM(hours) FROM case_hours WHERE case_hours.case_id = case.id)"

And, although this works, I am not able to SORT by Case Hours as a 500 error is generated.

I have considered creating a new type of field which could do the SUM client-side but this doesn't seem right. The ultimate place to do this would be in the back-end PHP however, I am not really certain if this is the right place either.

Maximus or yuri Are you able to offer any suggestions to how I may approach this, please? Are there any existing EspoCRM fields which operate like this?

Thank you.

[blueprint]

Ok, so I've re-arranged the way the field is defined in the entityDefs and I can now sort by this field.

I'm still not sure it is the right way but for now it may be ok.

What I would like to know is that given I have this `computed` field, how can I use the value of this field in another field? For example, if I need to have a field which computes the cost of the SUM(hours), can this be done in the entityDef file using the `select` statements?