Update formula field after linking a record

[jtubee]

Hi,

I created a formula field 'totalQuotes' that counts the number of records linked ('quotes'):

totalQuotes = entity\countRelated('quotes'),

It works OK when I create a new record or save one after a modification. But I need to update the value of the field 'totalQuotes' every time a related record is added in the bottom panel and therefore there is no need to save the target record.

I thought to create an update button and do it manual but... is there a valid method to do this automatically?

Thank you for your time.

[rabii]

Probably you might need to define a readLoaderClassNameList & listLoaderClassNameList if the field will be displayed on the list view and record detail view. Here i a example below, you need to create the file and directories if they don't exist.

<?php

namespace Espo\Custom\Classes\FieldProcessing\YourEntity;

use Espo\Custom\Entities\YourEntity; // Define path to your entity here Or use path of existing entity if you are linking to existing entity in core crm
use Espo\ORM\Entity;

use Espo\Core\FieldProcessing\Loader;
use Espo\Core\FieldProcessing\Loader\Params;
use Espo\Core\ORM\EntityManager;

/**
 * @implements Loader<YourEntity>
 */
class TotalQuotesLoader implements Loader
{
    /** @var string[] */
    private string $link = 'quotes';

    public function __construct(private EntityManager $entityManager)
    {}

    public function process(Entity $entity, Params $params): void
    {
        if (
            $params->hasSelect() &&
            !in_array('totalQuotes', $params->getSelect() ?? [])
        ) {
            return;
        }

        $count = 0;

        $count += $this->entityManager
            ->getRDBRepository(YourEntity::ENTITY_TYPE)
            ->getRelation($entity, $this->link)
            ->count();

        $entity->set('totalQuotes', $count);
    }
}​

And then register the class under in your recordDefs

{
    "readLoaderClassNameList": [
        "Espo\\Custom\\Classes\\FieldProcessing\\YourEntity\\TotalQuotesLoader"
    ],
    "listLoaderClassNameList": [
        "Espo\\Custom\\Classes\\FieldProcessing\\YourEntity\\TotalQuotesLoader"
    ]
}​​ 

This will always update the count of totalQuotes even when linking or unlinking records.

I hope this helps

[jtubee]

Thank you, It works!

Besides, I tried to do the same with a sum instead a count function and It works too.

Great!

[rabii]

glad you got it sorted now

[macistda]

Thanks rabii for that solution. Could be useful on other place

Maybe for this case jtubee can use Espo integrated functions.

Advanced Pack 3.2.0 Feb 28 2024

Workflows:

• Signal parameters for @relate, @unrelate, @eventAccepted signals. The ability to access a foreign ID in formula.
• Showing explanation text for Scheduling.
• Ability to specify a confirmation text for manual-trigger workflows.
• Fixed issue that stream notes and notifications were by-passed for Apply Assignment Rule.

BPM:

• Refreshing a flowchart with WebSocket on the Process detail view.