Opportunity Formula Script not running when adding new Opportunity Item [Sales Pack]

[Unreal51North]

Hello,

We are running Espo with the Sales Pack and the Advanced Pack. We added a formula script in Opportunity to calculate some custom currency fields based on OpportunityItem fields. Unfortunately, the script does not run when adding a new item in the opportunity. However, it will run when doing any other modification to the Opportunity OR to the Opportunity Items. For example: changing a quantity, changing a price or even just editing and saving the items without doing any changes. For some reason, adding an item isn't triggering the script. Are we doing something wrong?

Thanks

[yuri]

It triggers for me when adding an item. It should trigger.

> even just editing and saving the items without doing any changes

This should not trigger as the UI should explicitly bypass saving.

[Unreal51North]

It triggers for me when adding an item. It should trigger.

> even just editing and saving the items without doing any changes

This should not trigger as the UI should explicitly bypass saving.

Interesting! We're running Espo v9.0.4 with Sales Pack v2.6.10 (and Advanced Pack 3.8.0 - probably irrelevant). Could it be version-specific?

[lazovic]

Hi Unreal51North,

Could you please show the formula script you use? Is it before-save script or is it API before-save script?

[Unreal51North]

Hi Unreal51North,

Could you please show the formula script you use? Is it before-save script or is it API before-save script?

We have this one in Opportunity Item [Before Save Custom Script]:

cOriginalCurrency = record\attribute('Product', productId, 'listPriceCurrency');
cOriginalBrand = record\attribute('Product', productId, 'brandName');

And this one in Opportunity [Before Save Custom Script]:

cBrand1Total = 0;
cBrand2Total = 0;
cBrand3Total = 0;
cBrand4Total = 0;
$count = array\length(itemList);
$index = 0;
while($index < $count,
  ifThen(
    object\get(array\at(itemList, $index), 'cOriginalBrand') == 'Brand1',
    cBrand1Total = cBrand1Total + object\get(array\at(itemList, $index), 'amount')
  );
  ifThen(
    object\get(array\at(itemList, $index), 'cOriginalBrand') == 'Brand2',
    cBrand2Total = cBrand2Total + object\get(array\at(itemList, $index), 'amount')
  );
  ifThen(
    object\get(array\at(itemList, $index), 'cOriginalBrand') == 'Brand3',
    cBrand3Total = cBrand3Total + object\get(array\at(itemList, $index), 'amount')
  );
  ifThen(
    object\get(array\at(itemList, $index), 'cOriginalBrand') == 'Brand4',
    cBrand4Total = cBrand4Total + object\get(array\at(itemList, $index), 'amount')
  );
  $index = $index + 1
);
cBrand1TotalCurrency = 'CAD';
cBrand2TotalCurrency = 'USD';
cBrand3TotalCurrency = 'CAD';
cBrand4TotalCurrency = 'CAD';

Our goal is to calculate the total amount per brand within each opportunity and store it in a separate currency field for each brand. This allows us to generate reports showing opportunity totals by brand, even when multiple brands are included in the same opportunity.

Some brands are in USD, others in CAD — but we don’t convert between currencies. We display the totals in the original currency used by each brand (manufacturer).

These fields are also reused in quotes. In our case, we don't display a grand total in quotes — just one subtotal per brand, in its respective currency.

Hope this makes sense!

Many thanks!

[lazovic]

Unreal51North,

To achieve this goal, follow these steps:

1. Go to Administration > Entity Manager > Opportunity > Formula and delete all formulas.
2. Go to Administration > Entity Manager > Opportunity > Fields and create a Boolean field called recalculateTotals.
3. Go to Administration > Entity Manager > Opportunity Item > Formula and delete all formula scripts; replace them with the following ones:
   

   cOriginalCurrency = record\attribute('Product', productId, 'listPriceCurrency');
   	cOriginalBrand = record\attribute('Product', productId, 'brandName');
   	
   	$needRecalculate = entity\isNew() || entity\isAttributeChanged('amount') || entity\isAttributeChanged('cOriginalBrand') || deleted;
   	
   	if ($needRecalculate) {
   	  record\update('Opportunity', opportunityId, 'recalculateTotals', true);
   	}

4. Create a workflow as shown in the screenshots:

Workfow formula script:

cBrand1Total = 0;
cBrand2Total = 0;
cBrand3Total = 0;
cBrand4Total = 0;

cBrand1TotalCurrency = 'CAD';
cBrand2TotalCurrency = 'USD';
cBrand3TotalCurrency = 'CAD';
cBrand4TotalCurrency = 'CAD';

$brands = list('Brand1', 'Brand2', 'Brand3', 'Brand4');
$i = 0;

while ($i < array\length($brands)) {
    $brand = array\at($brands, $i);
    $items = record\findRelatedMany('Opportunity', id, 'items', 1000, null, null, 'cOriginalBrand=', $brand);
    $total = 0;
    $j = 0;

    while ($j < array\length($items)) {
        $itemId = array\at($items, $j);
        $amount = record\attribute('OpportunityItem', $itemId, 'amount');
        $total = $total + $amount;
        $j = $j + 1;
    }

    if ($brand == 'Brand1') {
        cBrand1Total = $total;
    }
    if ($brand == 'Brand2') {
        cBrand2Total = $total;
    }
    if ($brand == 'Brand3') {
        cBrand3Total = $total;
    }
    if ($brand == 'Brand4') {
        cBrand4Total = $total;
    }

    $i = $i + 1;
}

cRecalculateTotals = false;

[Unreal51North]

Just wanted to say a huge thank you — this solution works perfectly!
The logic is clean, efficient, and exactly what we needed.
You’ve not only solved our immediate issue, but also helped us implement something that’s scalable long-term.
I really appreciate your time and support — you're awesome!

[lazovic]

Thank you for your kind words. If you need help with this solution or any improvements, please let me know.