Report filter for comparing values of 2 float fields

[Vincent]

There is an entity having 2 float fields, A & B

how can i set the filter of the report to show record which value of field A is greater than that of field B?

I have tried add field filter, but it cannot compare values of 2 fields in same entity

Thanks.

[tanya]

Hello,
you can not compare two fields, but you can create third field, nonStorable and readOnly. It will contain the value "a subtract b". Use this field in the report

custom/Espo/Custom/Resources/metadata/entityDefs/{YourEntityName}.json

{

    "fields": {

        ....

        "aSubB": {
            "type": "float",
            "readOnly": true,
            "notStorable": true,
            "select": "account.a - account.b",
            "where": {
                "=": "(account.a - account.b) = {value}",
                "<": "(account.a - account.b) < {value}",
                ">": "(account.a - account.b) > {value}",
                "<=": "(account.a - account.b) <= {value}",
                ">=": "(account.a - account.b) >= {value}",
                "<>": "(account.a - account.b) <> {value}"
            },
            "orderBy": "aSubB {direction}",
            "view": "views/fields/float"
        }
    }
}

Instead of `account` set your table name.

Regards,
Tanya

[telecastg]

Could you tell me where I can learn more about the syntax for query to select values for a calculated field like the example above ?

[blueprint]

telecastg did you ever discover any further details about the syntax?

[telecastg]

Unfortunately, no. Never received any response form the Espo team and couldn't find any documentation.... seems to be a custom formula logic...

[blueprint]

Unfortunately, no. Never received any response form the Espo team and couldn't find any documentation.... seems to be a custom formula logic...

Hmm, 'tis a bit disappointing - both the lack of response and lack of documentation.

I'm using the same "select", "where" and "orderBy" fields however these do not seem to work on "bool" field types. Could do with knowing why and syntax documentation would probably tell me why.

Out of interest, have you implemented any back-end logic to do calculations and filtering, rather than using the above "front-end" methods?

[telecastg]

What I have done is define the field as read only in entityDefs, and then write the calculation or filtering logic in a beforeSave or afterSave hook.

I tried to decipher the custom query syntax looking at various metadata files but could not figure it out. Coming from a PHP background, I have found hooks as the easiest way to do custom logic without having to decipher the undocumented/custom "front end" syntax.

[blueprint]

That’s great for calculations done during saving but unfortunately I’m needing to run calculations on the fly - like when the entity records are loaded.

I haven’t yet found a “load” type PHP function which would allow me to do what you’re doing in the hooks.

As an example, I’ve since created an Inventory Management module which keeps track of stock levels. I need to calculate stock levels based on data from other tables each time a record is loaded.

[telecastg]

Not a very elegant solution but perhaps you could create a dummy table column in the stock item table and update it each time there is a change in the related/reference tables, implementing this logic in afterSave hooks for each related/reference table.

A possible "front end" alternative would involve creating a custom type field to hold the inventory level for each item and calculate its value in a custom field view file at loading time, but this would require you to access the reference table values from the stock item custom field view file.

In other words, you would need to be able to access from a model.attribute scope a related collection of models and their model.attribute values, and I haven't looked into how to do it or if it's even possible to do.

If you figure it out PLEASE share :-)

[blueprint]

Not a very elegant solution but perhaps you could create a dummy table column in the stock item table and update it each time there is a change in the related/reference tables, implementing this logic in afterSave hooks for each related/reference table.

I've considered this but it kinda doesn't feel right. There must be a way of doing this type of calculation. There are plenty of "notStorable" fields within EspoCRM so I just need to find one which is populated using logic once a record is loaded from the database.

Will keep you posted in case I find anything which may prove to be useful.

[blueprint]

Hmm, maybe this will help me: loadAdditonalFields (see link)

[telecastg]

I looked at the Note entity custom "loadAttachments" function and believe that you can implement a "preLoad" hook by:

1) Define the retrieval function (use ORM or sql to define the query) in your backend entity definition file (eg: custom/Espo/Custom/Entities/ourEntity.php) - See application/Espo/Entities/Note.php "loadAttachments" function

2) Call the retrieval function inside a custom implementation of the core "getEntity" function in your entity services file (eg: custom/Espo/Custom/Services/YourEntity.php) - See application/Espo/Services/Note.php "getEntity" function

[blueprint]

Thanks for that.

I've tried implementing the `getEntity` function in the custom Service file to test by setting the field to true as follows:

public function getEntity($id = null)
{
    $entity = parent::getEntity($id);

    $entity->set('testFlag', true);

    return $entity;
}

When I do a rebuild and bring up the list view for my entity, the 'testFlag' column is unchecked for everything.

If I use the `loadAdditionalFields` from the previous attached post, I get marginally more success however, the results are different between list and detail views so you need to implement two functions to ensure both views are consistent. Without implementing the `loadAdditionalFieldsForList` function, going into the detail view for a record may well show the checked value but when going back to the list shows it unchecked.

public function loadAdditionalFields(Entity $entity)
{
    parent::loadAdditionalFields($entity);

    $entity->set('testFlag', true);
}


public function loadAdditionalFieldsForList(Entity $entity)
{
    parent::loadAdditionalFieldsForList($entity);

    $entity->set('testFlag', true);
}

However, I'm still not able to filter on this value in the list view - a 500 error is returned as the field isn't an actual column in the database.

I think this is where Tanya would have come in useful but I think she must have been abducted by Aliens!

[telecastg]

I managed to implement a sort of back-end "preLoad" hook for calculated fields which works for both detail and list views, here it is:

1) Define field in entityDefs metadata eg: custom/Espo/Custom/Resources/metadata/entityDefs/MyEntity.json

        "testFlag": {
            "notNull": true,
            "type": "bool",
            "notStorable": true,
            "readOnly": true,
            "isCustom": true 
        }

2) Define function which will calculate the field value in the entity back-end definition file eg: custom/Espo/Custom/Entities/MyEntity.php

    public function computeTestFlag() {
        $this->set("testFlag", true);
    }

3) Override data retrieval functions in the custom entity services file eg: custom/Espo/Custom/Services/MyEntity.php

    public function getEntity($id = null)
    {
        $entity = parent::getEntity($id);
        if (!empty($id)) {
            $entity->computeTestFlag();
        }
        return $entity;
    }

    public function find($params)
    {
        $disableCount = false;
        if (
            $this->listCountQueryDisabled
            ||
            $this->getMetadata()->get(['entityDefs', $this->entityType, 'collection', 'countDisabled'])
        ) {
            $disableCount = true;
        }

        $maxSize = 0;
        if ($disableCount) {
           if (!empty($params['maxSize'])) {
               $maxSize = $params['maxSize'];
               $params['maxSize'] = $params['maxSize'] + 1;
           }
        }

        $selectParams = $this->getSelectParams($params);

        $selectParams['maxTextColumnsLength'] = $this->getMaxSelectTextAttributeLength();

        $selectAttributeList = $this->getSelectAttributeList($params);
        if ($selectAttributeList) {
            $selectParams['select'] = $selectAttributeList;
        } else {
            $selectParams['skipTextColumns'] = $this->isSkipSelectTextAttributes();
        }

        $collection = $this->getRepository()->find($selectParams);

        foreach ($collection as $e) {
            $this->loadAdditionalFieldsForList($e);
            if (!empty($params['loadAdditionalFields'])) {
                $this->loadAdditionalFields($e);
            }
            if (!empty($selectAttributeList)) {
                $this->loadLinkMultipleFieldsForList($e, $selectAttributeList);
            }
            $e->computeTestFlag();
            $this->prepareEntityForOutput($e);
        }

        if (!$disableCount) {
            $total = $this->getRepository()->count($selectParams);
        } else {
            if ($maxSize && count($collection) > $maxSize) {
                $total = -1;
                unset($collection[count($collection) - 1]);
            } else {
                $total = -2;
            }
        }

        return [
            'total' => $total,
            'collection' => $collection,
        ];
    }

Note for anyone who finds this beneficial: Please share solutions to your questions when you find them. This forum is full of member questions, many unanswered and very few member solutions

[blueprint]

telecastg this is great - thanks for putting the effort in to create this solution.

A question (sorry to ask) but given your implementation of `testFlag` - is it filterable via the Search Filters or does this also come up with a 500 error?

Totally agree re the sharing of solutions - people need to be encouraged to share their solutions.

It would also be nice to see some more "advanced" developer documentation given the scope of EspoCRM.

[telecastg]

You're welcome blueprint I am not familiar with the exact functionality of the Search Filters so I can't tell you if the 'testFlag' field is searchable via this facility.