Recalculate Formula by CRON-Job

[rabii]

you are welcome, just make sure to change names of entities / link / fields to the proper ones, let me know if you face any issues. will sort this out together.

[shalmaxb]

Hi, rabii, that looks great, I gonna test it still today and give feedback.You are really a great and patient help (not only for me) and you always put so much work in your proposals. I am very grateful for that.

[rabii]

you can still use formula in a reversed way, meaning apply the formula sum calculation also on the item entity, when an item (invoiceId) is changed and not null then recalculate the sum of the items of the given invoiceId (record), something like below:

// If the item has not been assigned to any record before
if (!entity\attributeFetched('invoiceId') &&
    entity\isAttributeChanged('invoiceId') &&
    invoiceId != null
){
    $source = record\findRelatedMany('Invoice', invoiceId,
                    'invoiceItems',
                    record\count('InvoiceItem', 'invoiceId=', invoiceId),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Invoice', invoiceId, 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
        
        $i = $i + 1;
    }
    
    record\update('Invoice', invoiceId, 'amount', $amount);
}

// If the item has been assigned before and reassigned to another record
if (entity\attributeFetched('invoiceId') &&
    entity\isAttributeChanged('invoiceId') &&
    invoiceId != null
){
    (
        $source = record\findRelatedMany('Invoice', entity\attributeFetched('invoiceId'),
                        'invoiceItems',
                        record\count('InvoiceItem', 'invoiceId=', entity\attributeFetched('invoiceId')),
                        'createdAt',
                        'desc'
                    );
        $i = 0;
        
        while ($i < array\length($source)){
            
            $amount = record\attribute('Invoice', entity\attributeFetched('invoiceId'), 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
            
            $i = $i + 1;
        }
        
        record\update('Invoice', entity\attributeFetched('invoiceId'), 'amount', $amount);
    );
    
    (
        $source = record\findRelatedMany('Invoice', invoiceId,
                        'invoiceItems',
                        record\count('InvoiceItem', 'invoiceId=', invoiceId),
                        'createdAt',
                        'desc'
                    );
        $i = 0;
        
        while ($i < array\length($source)){
            
            $amount = record\attribute('Invoice', invoiceId, 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
            
            $i = $i + 1;
        }
        
        record\update('Invoice', invoiceId, 'amount', $amount);
    );
    
}

// If the item has been unassigned from a previous record
if (entity\attributeFetched('invoiceId') &&
    entity\isAttributeChanged('invoiceId') &&
    invoiceId == null
){
    $source = record\findRelatedMany('Invoice', entity\attributeFetched('invoiceId'),
                    'invoiceItems',
                    record\count('InvoiceItem', 'invoiceId=', entity\attributeFetched('invoiceId')),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Invoice', entity\attributeFetched('invoiceId'), 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
        
        $i = $i + 1;
    }
    
    record\update('Invoice', entity\attributeFetched('invoiceId'), 'amount', $amount);
}​​ 

I would probably use the code above in the iteminvoice entity (Item) this code will try and try the invoiceId changes and based on each condition the system will recalculate the amount field on the invoice. One scenario that is not covered here is when an item is deleted then it should recalculate and for that you would use a hook (beforeDelete). I think such logic would work because the calculation should only accure when a change is made to an invoice / invoiceitem.

Hope this helps.

[shalmaxb]

rabii, so far I did not come up with a solution by formula. As I am not skilled in coding, I always try to solve any problem by formula or sometimes by copying code provided here.
Very plain spoken, I need a solution, that imitates the behaviour of the action menu item "recalculate Formula", only this by not having to do anything manually.
In my case I have two entities like invoice and items. The invoice calculates the sum of prices by formula. When I add a new item it does not add the price of that item without trigering the formula for sum calculation again manually.

[rabii]

Hey if you already have formulas they should be triggered once a condition is meet, why would you need a Job for this. i think you have just to add a condition in the formula to catch change and apply calculation or you can use Hooks. Not sure what is your use case but i think you could manage all of this using formula Or Hooks.

[espcrm]

I think it too hard with just CRON, this is a job for Workflow only. I'm still exploring alternative solution somewhere on my long list of "To-Do" and I keep my eye on these kind of thread. Thanks you for making it and item for a solution.

[shalmaxb]

I gave up to try this for my purpose. Reasons: Working regularily on an entity, it would be necessary to run the job in short sequences, what means, that the server would literally run all the time.
CRON itself has a minimum execute time of one minute, which would not be short enough for my purpose.

I will see what else could provide a solution for my problem:

recalculate without manual action, triggered by modification of a record, even when from a related entity.

My skills in coding are quite limited, but would this be possible by hooks or dynamic handlers?

[item]

it's more complex than that

Sample :
contact->countRelated('Call');

the job work for Contact...
Add a call to contact => contact->modifiedAt = still same as previous. no change.
Need "Hook" too, afterRelate, afterUnrelate.

not simple to catch all "possibility" . .. but you have asked how recalculate by Jobs, the post is OK

[shalmaxb]

I have quite a few entities with formulas. But it is no problem to write a separate recalculateJob for each of them.

[shalmaxb]

I guess I could use the modifiedAt field, because a recalculation would only happen, if something has changed. Well the where clause could look like this?

'modifiedAt' => dateTime('my dateTime format'),

or

'modifiedAt' => 'desc' (to fetch the last modified dateTime?)

[shalmaxb]

Meanwhile I got it working in general, but there are still some points to improve.

The code from item is correct and works. So far I do not use the parameters in the where-clause. What would be these parameters, when I would like to have the job working only when the record is modified?

Another problem is, that the detail view shows the recalculated values only after a browser refresh. How can I achieve, that the values get updated as soon as the job had been executed?

When all is completely working, I will write a little tutorial, how to implement that function to recalculate by cron.

[item]

Hi,
how many entity have calculed/formula field ?
as formula is beforeSave ... you need loop each record and save.

create a dateTime field "lastDateTimeRecalculate" in each entity who need recalculate, so you can use this field in where clause. you just need to find your logic "when and how many time recalculate, how find witch record to recalculate and why"

if you have sample : Account and Contact with formula..
you can do this in run function.

public function run() : void
{
    $entitiesToRecalculate = ['Account', 'Contact'];

    foreach($entitiesToRecalculate as $entityType){
        $entities = $this->em->getRDBRepository($entityType)
                ->where([
                    'lastDateTimeRecalculate<' =>  date('Y-m-01'), //
                ])
                ->find();

        foreach($entities as $entity)
        {
            $entity->set([ 'lastDateTimeRecalculate' => now() ]);
            $this->em->saveEntity($entity, [SaveOption::SILENT => true]);
        }
    }
}​ 

There are too with "formulaManager()->run..." .. but never play with.

[shalmaxb]

item, thanks. I don`t understand too much, but will try by trial and error. Do you mean, I will have to create a job for every particular entity? And in the where clause, I guess I will have to declare my calculations, right?

for you in french: Je ne comprends pas trop, mais je vais essayer par essais et erreurs. Voulez-vous dire que je devrai créer un emploi pour chaque entité particulière? Et dans la clause Where, je suppose que je devrai déclarer mes calculs, non ?

[item]

Hi,
nobody have respond.. pfff

it's simple :

fileName must RecalculateJob.php
path to save file custom\Espo\Custom\Jobs\ RecalculateJob.php
Entity => your Entity.. change his name.
where => change the where clause.
check param for save too at https://docs.espocrm.com/development/orm/#store-entity (not really understand each, try different and feedback)
Not sure, maybe you need modify one field for "out-of-box recalcul" fire ..

What do this... find all record from Entity where .... loop and save each record.

<?php
namespace Espo\Custom\Jobs;

use Espo\Core\Job\JobDataLess;
use Espo\Core\ORM\EntityManager;
use Espo\Core\ORM\Repository\Option\SaveOption;
use Espo\Core\Utils\Log;

class RecalculateJob implements JobDataLess
{
    public function __construct(
        private EntityManager $em,
        private Log $log
    ){}

    public function run() : void
    {
        $entities = $this->em->getRDBRepository('Entity')
                    ->where([
                        'status' => 'New',
                        'date<' =>  date('Y-m-01'),
                    ])
                    ->find();


        foreach($entities as $entity)
        {
            $this->em->saveEntity($entity, [SaveOption::SILENT => true]);
        }
    }
}​

custom/Espo/Custom/Resources/metadata/app/scheduledJobs.json

{ "RecalculateJob": { "jobClassName": "Espo\\Custom\\Jobs\\RecalculateJob" } } 

After this, create the a new Job in Admin section. .you can then select 'RecalculateJob'

[shalmaxb]

This question had been discussed a few years ago. As it obviously never lead to a working example and some code-base modifications had been passed I would like to ask this question again.

Formerly it had been stated, that it coud be possible to recalculate formulas by CRON-Job by creating a custom Job, which would be executed by CRON as described here: https://docs.espocrm.com/development/scheduled-job/
In the file for a custom job, there is the section where it says: "//Write your logic here"

That is where I need help. I guess there is already a logic for recalculate, as it is a built in function in espoCRM. Could anybody please help me with this part? What would I have to write here?