Recalculate Formula by CRON-Job

[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

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

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]

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.

[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

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

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

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

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

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

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.

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

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]

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: