Loop over one-to-many relation in formula

[xador]

Hello,

We are trying to add a formula in the Sale pack, for the Quote entity.

We want to adapt the calculation of taxes, in order to :
- deal with countries in which multiple taxes apply (ex : Canada)
- deal with different names of taxes for the different countries we work with

We want to implement the following solution :

- in QuoteItem : rename field taxRate to taxRate1 and add taxRate2 (float)
- in Quote : add text fields taxName1 and taxName2
- in Quote : add the formula that would make a loop on items in order to calculate the taxAmount1 and taxAmount2
- Adapt the quote template in order to display taxName1, taxName2, taxAmount1 and taxAmount2

For the formula part of the solution, we are stuck finding the right way to loop over quote items in order to do the tax calculation.

Here is the global shape of the formula we are trying to add :

$items = record\findRelatedMany('Quote', id, 'items', 100);
$count = array\length($items);
ifThen(
    $count > 0,
    (
        $totalTax1 = 0;
        $totalTax2 = 0;
        $i=0;
        while(
            $i < $count,
            (
                $itemId = array\at($items, $i);
                // TODO : get quote item with id $itemId ?
                // $totalTax1 = $totalTax1 + $item.amount * $item.taxRate1/100;
                // $totalTax2 = $totalTax2 + $item.amount * $item.taxRate2/100;
                $i = $i+1;
            )    
        )
    )
    //taxAmount = $totalTax1;
    //taxAmount2 = $totalTax2;
);​

Can someone help us ?

Thank you in advance

[lazovic]

Hi xador,

I will also ask you to post the solution if you find it yourself. Thank you.

[rabii]

Try this

$items = record\findRelatedMany('Quote', id, 'items', 100);
$count = array\length($items);
ifThen(
    $count > 0,
    (
        $totalTax1 = 0;
        $totalTax2 = 0;
        $i=0;
        while(
            $i < $count,
            (
                // TODO : get quote item with id $itemId ?
                $itemId = record\findOne('QuoteItem', 'createdAt', 'desc', 'quoteId=', id, 'id=', array\at($items, $i));

                $amount = record\attribute('QuoteItem', $itemId, 'amount');
                $taxRate1 = record\attribute('QuoteItem', $itemId, 'taxRate');
                $taxRate2 = record\attribute('QuoteItem', $itemId, 'taxRate2');
                
                $totalTax1 = $totalTax1 + ($amount * $taxRate1/100);
                $totalTax2 = $totalTax2 + ($amount * $taxRate2/100);
                $i = $i+1;
            )    
        )
    )
    taxAmount = $totalTax1;
    taxAmount2 = $totalTax2;
);​ 

[esforim]

xador, once you implement rabii code can you post screenshot of the result look?

My brain can't comprehend but I have a feeling this is a formula I'm trying to do here: https://forum.espocrm.com/forum/gene...1632#post91632

[xador]

Hello everyone,

Thank you for your answers.

At the end, it appeared that we over-complexified the current need. Having taxRate1 and taxRate2 at the level of the Quote is sufficient for us for the moment.

The solution we have chosen is to put this formula in QuoteItems to init taxRate1 and taxRate2 from the corresponding attributes of Quote :

/* init tax rates */
ifThen(
    taxRate1 == -1, // -1 is the default value = at init
    (
        taxRate1 = quote.taxRate1;
        taxRate2 = quote.taxRate2;
    )
);
amountTax1 = amount * taxRate1 / 100;
amountTax2 = amount * taxRate2 / 100;

...and a formula in Quote to update the items in case of a rates change :

taxAmount1 = amount * taxRate1 / 100;
taxAmount2 = amount * taxRate2 / 100;
grandTotalAmount = amount + taxAmount1 + taxAmount2 + shippingCost;

$items = record\findRelatedMany('Quote', id, 'items', 100);
$count = array\length($items);
ifThen(
    $count > 0,
    (
        $totalTax1 = 0;
        $totalTax2 = 0;
        $i=0;
        while(
            $i < $count,
            (
                $itemId = array\at($items, $i);
                record\update('QuoteItem', $itemId, 'taxRate1', taxRate1);
                record\update('QuoteItem', $itemId, 'taxRate2', taxRate2);
                $i = $i+1;
            )    
        );
    )
);
​

If someone needs different taxRate1/taxRate2 per item, this formula in Quote could compute the total :

$items = record\findRelatedMany('Quote', id, 'items', 100);
$count = array\length($items);
ifThen(
    $count > 0,
    (
        $totalTax1 = 0;
        $totalTax2 = 0;
        $i=0;
        while(
            $i < $count,
            (
                $itemId = array\at($items, $i);
                $amount = record\attribute('QuoteItem', $itemId, 'amount');
                $amountTax1 = record\attribute('QuoteItem', $itemId, 'amountTax1');
                $amountTax2 = record\attribute('QuoteItem', $itemId, 'amountTax2');
                $totalTax1 = $totalTax1 + $amountTax1;
                $totalTax2 = $totalTax2 + $amountTax2;
                $i = $i+1;
            )    
        );
        taxAmount = $totalTax1;
        taxAmount2 = $totalTax2;
        grandTotalAmount = amount + taxAmount + taxAmount2 + shippingCost;
    )
);​

[esforim]

I'm not sure your true code is more complex then this or not and you might have filter it. But these sort of calculation you need to have a "stopper" field as well. Basically to stop it from running again in the future.

(For people to read in future)
In my case I use the status=='Closed' as my stopper, this way if I make a small edit in the future it doesn't change the calculation. With tax (at least in my country) it change each year. So it no longer apply.

But this is "Sale Tax" so it might be quite static and never change, we got the same thing and it been like that for decade. But other tax, every year slightly increase.