Currency fields and formulas

[MatLudlam]

Hi, I have a rather complicated situation which I will try and simplify. Consider an opportunity, that has the following fields, all currencies:

• amount - what I sell for, one of EUR, GBP or USD
• support - what I sell for, one of EUR, GBP or USD. ALWAYS the same currency as amount
• orderTotal - a calculated currency field that is amount + support
• buyPrice - what I buy for (the total), one of EUR, GBP or USD, but not always the same as Amount
• margin - always in GBP (system base currency), and the "amount" in GBP less the "buyPrice" in GBP.

So within Entity Manager, on the Opportunity I have defined a formula which says:

orderTotal = amount + support;
orderTotalCurrency = amountCurrency;
margin = orderTotalConverted - buyPriceConverted;
marginCurrency = 'GBP';

So here is the issue, it seems that orderTotalConverted is only assigned a value once the script has completed, and has the value of 0 otherwise (or probably the previous value, I have not tested).

Right now I have to trigger several record writes to get the formula to run several times, each time the value works down the chain.

Is there any way the value of the "Converted" variable could be evaluated after ever line?

Or is there a better way of doing this? Would linked workflows solve the problem?

For reference the full formula currently looks like this:

$tempMonth = datetime\month(closeDate);
$qtr = ($tempMonth - 1)/3;
$strQtr = string\substring($qtr, 0, 1);
$numQtr = $strQtr + 1;
closeQuarter = string\concatenate(datetime\year(closeDate),"Q",$numQtr);
s4OrderTotal = amount + support + services + subscription;
s4OrderTotalCurrency = amountCurrency;
margin = s4OrderTotalConverted - buyPriceConverted;
marginCurrency = 'GBP';
ifThenElse(forecastStatus=='Pipeline', s4WeightedMargin=0);
ifThenElse(forecastStatus=='Possible', s4WeightedMargin=(marginConverted* 0.25));
ifThenElse(forecastStatus=='Probable', s4WeightedMargin=(marginConverted* 0.5));
ifThenElse(forecastStatus=='Commit',   s4WeightedMargin=(marginConverted* 0.90));
ifThenElse(forecastStatus=='Closed',   s4WeightedMargin=(marginConverted));

[MatLudlam]

Hi tanya, I would really appreciate your thoughts and feedback on this one.

Thanks.

[MatLudlam]

Hi tanya, could you have a quick look at this on please? I would love your advice.

Thanks.

[item]

Hi,
formula is (in my knowledge) a "beforeSave" hook.
you can write all in beforeSave hook ..
sorry my English is not top for understand all ..
if you will all result of all line, maybe, put a text field (stepField for sample) and then do in hook :

$stepField = firstResult .'\n';
$stepField .= secondeResult .'\n';

so ..
I don't know if we can use "variable" in formula ..
Regards

[yuri]

Hi,

Variables are supported.

Currency converted is pre-calculated before formula is run.

https://github.com/espocrm/espocrm/b...verted.php#L37
https://github.com/espocrm/espocrm/b...ormula.php#L37


Maybe it's reasonable to utilize workflows in your case.

[MatLudlam]

Hi yuri, thanks for the response.

I switched to Workflows and still have the same problem. It seems the the currencyVariableConverted field is only built at the very beginning and not updated between sequential workflows.

Right now I have the following setup in workflows:

Opportunity: Generic - a workflow that triggers the following sequential Workflows in order

1. Opportunity: Update Total - assigns a value to the OrderTotal field in local currency
2. Opportunity: Update Margin - calculates the Opportunity Margin (in base currency) by referencing the OrderTotalConverted field

The value of OrderTotalConverted is not updated between steps 1 and 2.

I looked at the code snippets you referenced, but I am not really sure what I am looking at, I don't know the difference between a "beforeSaveCustomScript" (what I think I use) and "beforeSaveScriptList" (not sure where these are). My feeling is that "getFormulaManager()" needs to be updated such that whenever an assignment is made to a Currency filed that it triggers a re-evaluation of the "Converted" value immediately before moving on to the next formula. As stated above I have now moved to Workflows so I don't think I am currently running through this code.

If that is not going to happen then, on the Workflow front I think the "CurrencyConverted.beforeSave()" needs to be run between every Workflow.

I am currently on the ESPO SaaS platform so happy to show you what I have or re-build it on a sand box.

Thanks.

[yuri]

Hi Mat,

beforeSave can't be trigger after each workflow action. It can break everything. We can't afford such changes.

You can try making sequential workflows.

Or revise your formula.

You could have something like:
s4OrderTotalConverted = amountConverted + supportConverted + servicesConverted + subscriptionConverted;

[MatLudlam]

Ok, thanks for the advice, I have now avoided using the "*Converted" items within my script and it is all working.

Thinking through the above, I can 100% see where you are coming from and associated issues.

My thoughts are:

1. The script engine should re-calculate the Converted fields as the parent variable has its value changed; or
2. A new script function is added to force the re-calculation of a Converted field

Thanks for the continued help, I really love the product.

[yuri]

> 1. The script engine should re-calculate the Converted fields as the parent variable has its value changed; or
We can't afford this change.

> 2 A new script function is added to force the re-calculation of a Converted field
We can't afford this change too.