Skip to main content
Topics#FormulasDomanda di Roger Wicki
Roger Wicki (Architonic AG) ha fatto una domanda in #Formulas
23 gen 2015, 10:08

Compiled Formula Size too big & workaround not satisfying

Dear Community

It so happened today that I needed further calculations on already precalculated (formula) fields. I ended up getting angry at 5182 compiled size characters and the way I now need to work around it. I ended up using a workflow rule to update a field writing the value I wanted to reference in the new formula field in a seperate new field.

The thing is: I have about 5 triggers running on that object. Updating a field on that object, just because the formula is some few characters too long is just retarded. I understand that there needs to be a limit on the formula size, but as so often, there's always a way around the system that's even worse for performance and I'm sure also for what Salesforce needs to handle through cloud computing power.

I have already optimized my formula fields as much as possible using hints from the guide found here:

https://help.salesforce.com/help/pdfs/en/salesforce_formula_size_tipsheet.pdf

I mean I could also pack the whole calculation into triggers but then again I need to make additional (unnecessary) update calls. We need many of the intermediate results of the whole calculation for other processes so I can't really handle the calculation in one step anyway.

I have a working solution now, but I'm far from happy with it...

Following are my formula and all related fields / formulas in overview:

Revenue_R1__c (Number 16,2) =
Revenue_Amount__c * (Opportunity.Betrag1Rate__c / Opportunity.Amount_Total__c)
// Opportunity.Betrag1Rate__c (Currency 16,2)
Revenue_Amount__c (Number 16,2) =
IF(
  isMDB__c,
  IF(
    ABS(Opportunity.Amount - Opportunity.MDB_Salesprices__c) < 0.01,
    Salesprice_Total__c - (Share__c * (Opportunity.SpecialDiscountFlat__c + Opportunity.Special_Discount_Amount__c)),
    Salesprice_Total__c
  ),
  Salesprice_Total__c - (Share__c * (Opportunity.SpecialDiscountFlat__c + Opportunity.Special_Discount_Amount__c)) - ((Share__c / IF(Opportunity.non_MDB_Share__c = 0,
  1,
  Opportunity.non_MDB_Share__c)) * (1 - Opportunity.non_MDB_Share__c) * (Opportunity.SpecialDiscountFlat__c + Opportunity.Special_Discount_Amount__c)) + (Share__c / Opportunity.non_MDB_Share__c) * Opportunity.VAT_Amount__c
)
// isMDB (Checkbox)
// Opportunity.MDB_Salesprices__c (RollUp / Currency 16,2)
// Opportunity.SpecialDiscountFlat__c (Currency 16,2)
Salesprice_Total__c (Currency 16,2) =
UnitPrice * Quantity
Share__c (Number 1,17) =
IF(
  Opportunity.Amount > 0.0000,
  Salesprice_Total__c / Opportunity.Amount,
  0
)
Opportunity.Special_Discount_Amount__c (Currency 16,2)
(Amount - BLANKVALUE(SpecialDiscountFlat__c, 0)) * BLANKVALUE(Special_Discount_Percentage__c, 0)
// Special_Discount_Percentage__c (Percent 3,2)
Opportunity.non_MDB_Share__c (Number 1,17) =
IF(
  Amount <> 0.0000,
  1.00000000000000000 - (BLANKVALUE(MDB_Salesprices__c,0) / Amount),
  NULL
)
Opportunity.VAT_Amount__c (Currency 16,2) =
NetAmount__c * BLANKVALUE(VAT__c,0)
NetAmount__c (Currency 16,2) =
Amount - ABS(BLANKVALUE(Special_Discount_Amount__c,0)) - BLANKVALUE(SpecialDiscountFlat__c,0)
VAT__c (Percent 3,2) =
IF( 
  AND( 
    CONTAINS($Setup.ArchitonicAG__c.VATCountryCodes__c, TEXT(Account.ShippingCountryIsoCode__c)), 
    Account.Id <> "001D000001BrK22" /* Architonic AG */ 
  ), 
  0.08, 
  NULL 
)
// Account.ShippingCountryIsoCode__c (Picklist ~240 entries of length 3 each)
// $Setup.ArchitonicAG__c.VATCountryCodes__c (Text 5)
Amount_Total__c (Currency 16,2) =
NetAmount__c + BLANKVALUE(VAT_Amount__c,0)

If someone still reads this: Congratulations! You could know now how our company has organised their sales. The same in words would be this:

The general idea behind the revenue field is the profit we make from this Product with regards to opportunity induced discounts (2 types) and the VAT which will be assigned according to their share of the total opportunity amount under additional consideration that a certain product group may not be assigned that share of the discounts, which instead should be redistributed over the remaining products, unless there are only products of this type. The Revenue_R1 field does the same but returns the value broken down to the first payment rate, of which we have 12 of.
2 risposte
  1. Roger Wicki (Architonic AG)
    30 gen 2015, 10:04

    ... I already wrote that:

    I ended up using a workflow rule to update a field writing the value I wanted to reference in the new formula field in a seperate new field.

    The thing is: I have about 5 triggers running on that object. Updating a field on that object, just because the formula is some few characters too long is just retarded.

    But nevermind. There was one additional point where I could still reduce compiled size. However, still I might have lots of triggers on the object I needed to have the workflow with a field update on...
Scrivi una risposta...
0/9000