NotStorable Entity Field Record Count

[blueprint]

I would like to know how I could add a Field to an Entity who's value is a count of the number of child records.

For example, in Tickets I may create a field called TaskCount - the value I do not want to be stored in the database but I would like the value to be the COUNT of the number of related Task entities.

Is there an existing example of this in EspoCRM which I could use to help me?

[Maximus]

Hi.
The only proper way is to create a workflow.

the value I do not want to be stored in the database

This value will be stored in the DB cause it is handled by the TaskCount field.

[blueprint]

Hmm, this doesn't seem to be true or maybe I have not explained the question correctly.

The following code is exactly the solution I need, adding it to the relevant `entityDefs/entity.json`

"taskCount" {
"type": "float",
"min": 0,
"max": 100,
"readOnly": true,
"notStorable": true,
"select": "(SELECT COUNT(*) FROM X WHERE X.id = Y.id)"
}

Obviously, I've replaced the real table names with X and Y but this works just fine.

[Mitchell63]

Well u need to change the code..Code itself is not up to mark....So modify it to a bit and it should solve your problem myschoolbucks

[czcpf]

Hi blueprint, I recently worked through this myself. While your code works, the example below should also work and looks more like the examples using complex expressions:

"taskCount": {
     "type": "int",
     "notStorable": true,
     "readOnly": true,
     "select": {
          "select": "COUNT:(task.id)",
          "leftJoins": [
               [
                    "Task",
                    "task",
                    {
                         "task.ticketId:": "id",
                         "task.deleted": "0",
                         ...
                    }
               ]
          ]
     }
}