function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Karin McWilliams 1Karin McWilliams 1 

Workflow field updates won't recalculate totals on Opportunity Products

HI all - 

I have Opportunity Products configured via workflow + field update so based on Billing Frequency (Monthly, Quarterly, Yearly, One Time) my Standard Field Sales Price (aka UnitPrice) will calculate. I have tried this in Process Builder and it didn't work well either.
We have Subscription and Non-Subscription line items on Opportunity Products line items and customer may be billed differently for each line item.
Below is a view of Products I've chosen based on a 24 month contract:
User-added image
I created a custom field called "Unit Price" to hold the original amount input
I then wrote the following formulas to update the standard field called Sales Price (aka UnitPrice):

SUBSCRIPTION Update Field Sales Price:

IF(CONTAINS(Billing_Frequency__c, "Monthly"), Unit_Price__c *1, 
IF(CONTAINS(Billing_Frequency__c, "Quarterly"), Unit_Price__c *3, 
IF(CONTAINS(Billing_Frequency__c, "Yearly"), Unit_Price__c *12, 
IF(CONTAINS(Billing_Frequency__c, "One Time"), Unit_Price__c *1, null))))

NON-SUBSCRIPTION Field Update Sales Price:

IF(CONTAINS(Billing_Frequency__c, "Monthly"), 
Unit_Price__c * Quantity / Contract_Terms__c, 
IF(CONTAINS(Billing_Frequency__c, "Quarterly"), 
Unit_Price__c * Quantity / Contract_Terms__c *3, 
IF(CONTAINS(Billing_Frequency__c, "Yearly"), 
Unit_Price__c * Quantity / Contract_Terms__c * 12, 
IF(CONTAINS(Billing_Frequency__c, "One Time"), 
Unit_Price__c *1, null))))

Then, for the Total Contract Amount I create these two formula for updating the fields:

SUBSCRIPTION Field Update Total Contract Value:
TotalPrice / Billing_Frequency_Number__c * Contract_Terms__c

NON-SUBSCRIPTION Field Update Total Contract Value:
IF(CONTAINS(Billing_Frequency__c, "Monthly"), 
UnitPrice * Opportunity.Contract_Term_Number__c / 1, 
IF(CONTAINS(Billing_Frequency__c, "Quarterly"), 
UnitPrice * Opportunity.Contract_Term_Number__c / 3, 
IF(CONTAINS(Billing_Frequency__c, "Yearly"), 
UnitPrice * Opportunity.Contract_Term_Number__c / 12, 
IF(CONTAINS(Billing_Frequency__c, "One Time"), 
TotalPrice, null))))

My challenge is that the Contract Term updates just fine but the change doesn't trigger a recalculation. What am I doing wrong?
I have Evaluation Criteria as: Evaluate the rule when a record is created, and any time it's edited to subsequently meet criteria
I even checked the boxed on each Field Update that says: Re-evaluate Workflow Rules after Field Change
Even if I go into the Opportunity Product Line item record and make a change, re-calculations still don't happen. 

Thanks in advance to anyone who can help me with this!!
Raj R.Raj R.
Hi Karin,

With Workflow Rules I have had to resort to setting the Evaluation Criteria to "created, and every time it's edited"

Do the fields Billing_Frequency__c, Billing_Frequency_Number__c , Contract_Terms__c, and Unit Price all reside on the Opportunity Line Item object or are they all on seperate objects? I have done something similar using Formula fields where based on a picklist (assuming Billing_Frequency__c is a picklist), it would perform a calculation using all your if statements. This way if the picklist value changed, then the formula text field's value would also be updated. 

Alternatively this can be done using Process Builder but you may have to separate the process builders or through Apex Trigger. One process builder for the value for subscriptions and another one strictly related to non-subscriptions.
Karin McWilliams 1Karin McWilliams 1
Hi Raj - thanks for your reply - much appreciated.
All fields used reside on the Opportunity Line Item object. Contract Term is updated via another field originating on the Opportunity but that shouldn't affect it, correct? The Contract Term field is still being updated on the Opportuntiy line ite.
Originally, because have the Standard Sales Price (aka UnitPrice) updating according to Billing Term Frequency (Monthly, Quarterly, Yearly, One Time), I knew I had created a circular calculation. This is why I created a custom Unit Price field to house the original Unit Price chosen, then the calculations could be based on that field (as if they were starting the calculation over again when the Contract Term changed).

I'll try recreating these flows via Process Builder again. I just don't understand why, after choosing "every time it's created or edited", my workflows won't update the currency fields based on the calculations I've written. I'm stumped!
Raj R.Raj R.
I think the best bet here is to review the log files. Just open the Developer Console (DS) and perform the action that would trigger the Contract term field and have the Logs tab open in the DS. If the contract term is performed as you mentioned then it should show that in the log and right after that it should be able to tell you what happens next. Since you are doing workflow rules, i would look for "WF_CRITERIA_BEGIN" and there should be a "WF_CRITERIA_END". If this is true and it is still not triggering a recalculation then there is something going on within the Workflow rule. 

Another option to try is to have a Trigger that is executed. When the Contract term is updated, it runs an "After update" and you do something like this (you may need to modify it to bulkify). 
trigger MyTrigger on OpportunityLineItem (after update) {
    for(OpportunityLineItem item : Trigger.new) {
        if(item.Contract_Term__c != Trigger.oldMap.get(item.id).Contract_Term__c) {
            //value has changed
            String searchStr = item.Billing_Frequency__c;
            if(searchStr.containsIgnoreCase('Monthly')){
                item.Sales_Price__c = item.Unit_Price__c * 1;
            }
            else if(searchStr.containsIgnoreCase('Quartely')) {
                item.Sales_Price__c = item.Unit_Price__c * 3;
            }
            
            // do all conditions.
        }
    }
}