No announcement yet.

SUM Field

  • Filter
  • Time
  • Show
Clear All
new posts

  • SUM Field

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

    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 yurikuzn 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.
    Last edited by blueprint; 12-05-2019, 03:10 PM.

  • #2
    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?