Calculated field "SELECT" differs from "detail" to "list" views

[ManicMiner]

Hi,

I created a calculated field to SUM hours...

The field definition is as follows (tables and field names changed for better comprehension)..

"totalTime": {
"type": "int",
"readOnly": true,
"notStorable": true,
"view": "custom:views/fields/totalTime",
"select": {
"sql": "SUM(B.time)",
"leftJoins": [
[
"Table-A",
"A",
{
"A.id:": "id",
"A.deleted": false
}
],
[
"Table-B",
"B",
{
"B.id:": "A.id",
"B.deleted": false
}
]
]
}
},

* Now, when the field is displayed in a "DETAIL" view, all works as expected. The "id" on the right side of the first "leftJoin" is replaced with the entity id value, and the query is retrieving the correct value.

* But when the field is used in a "LIST" view, seems like the "id" field is not replaced for every occurrence in the list, and no values are retrieved --empty list--

Is there something I misunderstood ?

Any help or suggestion will be welcomed.

TIA

[telecastg]

I believe that this implementation is only called when a record (entity) is requested (detail view) , not when the whole collection (list view) is requested.

If you are more familiar with PHP than with the custom metadata "query" syntax like me, check the last part of this article for a back end solution

https://forum.espocrm.com/forum/general/34663-report-filter-for-comparing-values-of-2-float-fields

[ManicMiner]

Sorry "telecats", but I must disregards with you .... the implementation is called, but because the "id" field is not replaced with "entity".id, you get a whole "select", where the SUM is the total sum of all records where the join condition is satisfied; because there isn't any "id=<code>", all records satisfy the condsition ... this is a fact, it's tested

Regarding overriding entity methods, i'm my honest oppinion, is a good practice when calculations involves several entity's fields or even, you need to add som other entities data; but if implement a new "field" type, adding calculating capabilities, could be redundant add code at entity level and at field level ...

[telecastg]

No problem, if you found a better solution to your question please share it here so everyone benefits.