Recalculate Formula by CRON-Job

[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?

[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]

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,
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]

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]

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

[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]

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.

[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?

[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.

[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.

[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]

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]

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 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]

Ok, I tried a lot, but the most doubt I have with the links, fields and entities. I guess there is my error anywhere. To get the point, I started with the first part only, to see, if it works, what it does not.
Here is a screenshot of my adapted formula:

[rabii]

Hey,

First of all this could should be used in teh item entity formula and not invoice. You have some mistakes and you need to get the correct link field / link multiple field for the relationship otherwise it won't work. the first condition should check if the invoiceId on the current item record was empty and has changed and it is not empty, meaning the item entity is linked to an invoice hence why you need to use the invoiceId field which is the link field of the relationship between invoice and item (i assume it is one to many relationship One invoice has many items), so you need to get the name of the invoice field on the item entity (it should be something like invoiceId / Or based on invoice name rechnungId) you need to get the correct name. you can find the field that represent the invoice on the item entity and get the correct name, do the same thing on invoice and find the name of the relationship of items on invoice if you share screenshot of the relationship names i am happy to implement it in the code. here is an example of what it should be, the first part:

// If the item has not been assigned to any record before
// !entity\attributeFetched('invoiceId') this should be the link of invoice on item (i assume that the invoice has many items so there fore it should be the link field of invoice on the item entity) - i would assume it should be something like invoiceId or rechnungId
if (!entity\attributeFetched('rechnungId') &&
    entity\isAttributeChanged('rechnungId') &&
    rechnungId != null
){
    // atelierrechnungPositionen this should be the name of the relationship . link multiple of the Item on the invoice - simiar to opportunties on account
    $source = record\findRelatedMany('Rechnung', rechnungId,
                    'atelierrechnungPositionen',
                    record\count('PositionenAtelierrechnung', 'rechnungId=', rechnungId),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Rechnung', rechnungId, 'summeNetto') + record\attribute('PositionenAtelierrechnung', array\at($source, $i), 'verkaufspreisNetto');
        
        $i = $i + 1;
    }
    
    record\update('Rechnung', rechnungId, 'summeNetto', $amount);
}​ 

I am using rechnungId which should represent the name of the invoice field on the item entity, find the correct name, also double check this (atelierrechnungPositionen) i think this should be plural but i am not good at all at german so i am not sure, anyway you just need to get the correct names of the fields and it will work.

REMEMBER THE CODE NEEDS TO BE USED IN THE ITEM ENTITY FORMULA AND NOT INVOICE, BECAUSE WE WANT TRACK CHANGES THAT HAPPEN TO ITEM RECORDS.

[shalmaxb]

rabii, thank you again, I tried my best to get it working, but still not.

Two screenshots, the relationship and my current formula.



How I understand the formula:

1. The formula looks, if there is any item in invoice or any item changed and that item in invoice is not empty
2. The formula looks, if in the invoice there are any items, when it is the first item at all, the record count starts at 0, after that with any new item adds 1 to the count, as long as the count is smaller than before (or: add one, whenever there is a new item).
3. The updated amount (in my case the net price = summeNetto in invoice) consists of the amount before adding the single price of the new item (single price = verkaufspreisNetto form item). This way the before net sum will be summed up with the new added item and updates as the new summeNetto.

Here the relationship between invoice and item

[rabii]

you always forget in the first condition invoiceId != null that is why it doesn't work because there is no field in your database called invoiceId you should change that to be atelierrechnungPositionenId != null instead.

[rabii]

what is the name of the entity invoice ? and what the name of the entity item ? from the sceenshot i can see that the item entity is called (Position Atelierverkauf)

[rabii]

Ok, you are missing few things, i guess you have issue with the names of the entities, the relationship names are clear now, but if you could check the name of entities (name not label) it would allow to fix, but ass seen from my screenshot you always keep a field name invoiceId in the code and that field doesn't exist that is why the system never trigger the code, please use the code below and get read of the code you shared above.

if (!entity\attributeFetched('atelierrechnungPositionenId') &&
    entity\isAttributeChanged('atelierrechnungPositionenId') &&
    atelierrechnungPositionenId != null
){
    $source = record\findRelatedMany('Rechnung', atelierrechnungPositionenId,
                    'positionenAtelierrechnung',
                    record\count('PositionenAtelierrechnung', 'atelierrechnungPositionenId=', atelierrechnungPositionenId),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Rechnung', atelierrechnungPositionenId, 'summeNetto') + record\attribute('PositionenAtelierrechnung', array\at($source, $i), 'verkaufspreisNetto');
        
        $i = $i + 1;
    }
    
    record\update('Rechnung', atelierrechnungPositionenId, 'summeNetto', $amount);
}​ 

I am assuming from your earlier info that (PositionenAtelierrechnung) is the correct name of the item entity and (Rechnung) is the correct name of the invoice entity correct?
If yes, please copy this code and use it instead of the other code and let me know if that works for you.

If it help i can do a zoom session to help sort this out.

[shalmaxb]

ok, I already had changed the invoiceId !=null

Name of invoice entity is "Rechnung"
Name of item entity is "PositionenAtelierrechnung"

Will test that a bit later.

[shalmaxb]

No result in testing with entities, in sandbox it also has no result

[rabii]

this code will not work in sandbox of course because there is a condition for UI to change then the code will be triggered. use the code in Item entity formula script and try to change the invoiceId of an item record (then see the invoice record amount changes).

[shalmaxb]

in sandbox I used the code without the starting conditions. As for my entities I use this code in the item entity. I think I understood the code completely.

[rabii]

Hey,

I have tested this and it works, however i think it will not work fine, the reason why is that the current item being linked to the invoice will be always missing and the reason why is that the formula is triggered as a before save script which means the current item is not yet linked to the invoice. the best way to solve this is to use similar code but in a afterSave Hook for the amount on the invoice, i guess that flow is always selecting multiple item on the invoice rather than linking an invoice to an item correct ? if that is true the you can simple use an afterSave hook for the amount and calculate the amount every time the items changes on the invoice. Could something like below:

<?php

namespace Espo\Custom\Hooks\Rechnung;

use Espo\Core\Hook\Hook\AfterSave;
use Espo\ORM\Entity;
use Espo\Core\ORM\Repository\Option\SaveOption;
use Espo\ORM\Repository\Option\SaveOptions;


class PositionenAtelierrechnung implements AfterSave
{

    public function afterSave(Entity $entity, SaveOptions $options): void
    {  
        if (!$options->get(SaveOption::SKIP_HOOKS)) {
            return;
        }

        if (!$entity->has('positionenAtelierrechnung')) {
            return;
        }

        $itemList = $entity->get('positionenAtelierrechnung');

        if (!is_array($itemList)) {
            return;
        }


        if (count($itemList)) {
            $amount = 0.0;
            foreach ($itemList as $item) {
                $amount += $item->verkaufspreisNetto;
            }
            $amount = round($amount, 2);
            $entity->set('summeNetto', $amount);
        }
    }
}​

Hope this helps

[shalmaxb]

It is impressive, how you don`t give up (what I would completely understand). This thread starts to evolving like a workshop, I really appreciate that. It is really difficult to learn the correct procedure, when you do not have the pre-requisited knowledge, which is hard to learn in a short time. With espoCRM and the help here in the forum I already advanced a lot in the last four years, but still I try to learn more. You especially are helping a lot.
I had been thinking about hook already, but never used it before. I will try with your example, perhaps I get forward. If not, it is not existential to my app. A solution would though bring advantages in user experience, as the user would not have to re-calculate all the time.

[rabii]

I understand we are all in on this together, i also get help for other staff so always happy to help.

Just create a folder under custom/Espo/Custom/Hooks/Rechnung (create the folders if they don't exist) and then create a php file called PositionenAtelierrechnung.php

Don't forget to disable any previous formula code you have tested.

Give it ago and let me know, i have tested this code and it works so it should work fine.

[shalmaxb]

rabii, great, now it works, but only as you described, when choosing an item (which has to be created before in items), from the linked field in invoice. But this had been necessary before this solution as well, I had to create the item and choose the invoice from the linked field, what now is obsolete.

Again, thank you a lot. I now will try to adapt this for other types of invoice and item type.

What not works is, when I delete a formerly added item, then the sum is kept in the former value.

[rabii]

Hey shalmaxb,

You need another Hook for the item entity so that when an item is deleted the invoice entity needs to be updated, so you can use the Hook below for the item entity, create a new file under Hooks > PositionenAtelierrechnung > RechnungAmount.php and use the code below, this code should work, however let me know if you face any issues:

<?php

namespace Espo\Custom\Hooks\PositionenAtelierrechnung;

use Espo\ORM\Entity;
use Espo\ORM\EntityManager;
use Espo\ORM\Repository\Option\RemoveOptions;
use Espo\Core\Hook\Hook\BeforeRemove;
use Espo\Core\Hook\Hook\AfterRemove;

class RechnungAmount implements
    BeforeRemove,
    AfterRemove
{

    public function __construct(private EntityManager $entityManager)
    {}

    public function beforeRemove(Entity $entity, RemoveOptions $options): void
    {  
        if (!$entity->get('atelierrechnungPositionenId')){
            return;
        }

        $atelierrechnungPositionen = $entity->get('atelierrechnungPositionenId');

        $atelierrechnungPositionen->removeLinkMultipleId('positionenAtelierrechnung', $entity->getId());

        $this->entityManager->saveEntity($atelierrechnungPositionen);
    }

    public function afterRemove(Entity $entity, RemoveOptions $options): void
    {  
        $atelierrechnungPositionen = $entity->get('atelierrechnungPositionenId');

        if (!$atelierrechnungPositionen->has('positionenAtelierrechnung')) {
            return;
        }

        $itemList = $atelierrechnungPositionen->get('positionenAtelierrechnung');

        if (!is_array($itemList)) {
            return;
        }

        if (count($itemList)) {
            
            $amount = 0.0;
            
            foreach ($itemList as $item) {
                $amount += $item->verkaufspreisNetto;
            }
            
            $amount = round($amount, 2);
            
            $atelierrechnungPositionen->set('summeNetto', $amount);
            
            $this->entityManager->saveEntity($atelierrechnungPositionen);
        }
    }
}​

Hope this help mate