function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Benjamin CulverBenjamin Culver 

Track Opp Product Price increase year over year and lifetime total

Hello, all

I have added a percentage 'yearly increase' field to my Opp Product.

I am looking for a way to calculate the lifetime total of the product after x years (x being the 'term' field) if the price is increased by 'yearly increase' every year and how to track each year's price. This needs to work for any given term

for example.
  • product price = $100
  • yearly increase = 3%
  • term = 5​​​​​​​
    • year 1 price = 100
    • year 2 price = 103
    • year 3 price = 106.09
    • year 4 price = 109.27
    • year 5 price = 112.55
    • lifetime total = 530.91
If I could use a for loop in the formula editor I could calculate the lifetime total, but I can't figure out how to do that in the formula editor. Is there a way to do that or to write the for loop in an apex class and use that to populate the lifetime total field? I have no clue how to track each year's price espically when the term will vary.

Thank you for your time and help.
Best Answer chosen by Benjamin Culver
Dushyant SonwarDushyant Sonwar
Benjamin,

Apologies from my end. Actually lookup field is not allowed , so this solution will not work and vf page cannot be embedded in opportunity Line item.

But we can add lightning component :
 
<aura:component controller="wrapperClassController" implements="force:hasRecordId,flexipage:availableForAllPageTypes">

    <aura:attribute name="recordId" type="String"/>
     <aura:attribute name="totalAmount" type="Decimal"/>
	<aura:handler name="init" value="{!this}" action="{!c.doInit}"/>
    <aura:handler event="force:refreshView" action="{!c.doInit}" />
    <aura:attribute name="lstData" type="List"/>
	<lightning:card title="My Yearly Product Increase"> 
    <table class="slds-table slds-table_cell-buffer slds-table_bordered">
			<thead>
				<tr class="slds-line-height_reset">
					<th class="slds-text-title_caps" scope="col">
						<div class="slds-truncate" title="Year">Year</div>
					</th>
					<th class="slds-text-title_caps" scope="col">
						<div class="slds-truncate" title="Price">Price</div>
					</th>
				</tr>
			</thead>
			<tbody>
        <aura:iteration items="{!v.lstData}" var="item">
         <tr>
            <td>{!item.name}</td> 
             <td> {!item.productPrice} </td>
          </tr>
    </aura:iteration>
                <tr>
            <td>lifetime total</td> 
             <td> {!v.totalAmount} </td>
          </tr>
        </tbody>
        </table>
    </lightning:card>
</aura:component>

Controller JS
({
	doInit: function(cmp) {
       var action = cmp.get("c.generateData");
        action.setParams({ recordId : cmp.get("v.recordId") });

        // Create a callback that is executed after 
        // the server-side action returns
        action.setCallback(this, function(response) {
            var state = response.getState();
            if (state === "SUCCESS") {
                // Alert the user with the value returned 
                // from the server
                var lstdata =  response.getReturnValue();
                var totalAmount = 0;
                for(var i=0 ; i<lstdata.length ; i++){
                    totalAmount +=lstdata[i].productPrice;
                }
                cmp.set('v.totalAmount' , totalAmount);
				cmp.set('v.lstData' , response.getReturnValue());
                // You would typically fire a event here to trigger 
                // client-side notification that the server-side 
                // action is complete
            }
            else if (state === "INCOMPLETE") {
                // do something
            }
            else if (state === "ERROR") {
                var errors = response.getError();
                if (errors) {
                    if (errors[0] && errors[0].message) {
                        console.log("Error message: " + 
                                 errors[0].message);
                    }
                } else {
                    console.log("Unknown error");
                }
            }
        });

        // optionally set storable, abortable, background flag here

        // A client-side action could cause multiple events, 
        // which could trigger other events and 
        // other server-side action calls.
        // $A.enqueueAction adds the server-side action to the queue.
        $A.enqueueAction(action);
    }
})

Apex Class
 
public class wrapperClassController {

 @AuraEnabled 
    public static list<wrapper> generateData(String recordId){

        list<OpportunityLineItem> lstOppItem = [Select Product_Price__c,yearly_increase__c,term__c from opportunityLineItem where id = :recordId];
        
        list<Wrapper> lstWrapper = new list<Wrapper>();
        
        if(lstOppItem.size() > 0){
            Decimal productPrice = lstOppItem[0].Product_Price__c== null ? 0 : lstOppItem[0].Product_Price__c;
            Decimal yearIncrease = lstOppItem[0].yearly_increase__c == null ? 0 : lstOppItem[0].yearly_increase__c;
            Decimal term = lstOppItem[0].Term__c == null ? 0 : lstOppItem[0].Term__c;
            
            wrapper wrp = new wrapper();
            wrp.productPrice = productPrice ;
            wrp.name = 'Year 1';
            lstWrapper.add(wrp);
            for(integer i = 1 ; i<term  ; i++){
                wrapper wrpObj = new wrapper();
                wrpObj.name = 'Year '+ (i+ 1);
                wrpObj.productPrice = productPrice + (productPrice * (yearIncrease  /100));
                productPrice = wrpObj.productPrice;
                
                lstWrapper.add(wrpObj);
            }

        }
        return lstWrapper ;
    }

//Main Wrapper Class STEP 1
    public class wrapper{
       @AuraEnabled //Annotation when using for lightning component
          public Decimal productPrice;
       @AuraEnabled 
          public String name;
    }   
}

After adding the lightnig component it will look something like this below
User-added image

Hope this helps!
 

All Answers

Dushyant SonwarDushyant Sonwar
Hi Benjamin,

You can create a Term custom Object , it will be a lookup to (Opportunity Product).
create fields like Price__c
Then create a apex trigger on OpportunityProduct

I am assuming the fields for opportunitylineitem as i don't know the exact api names.
 
trigger OpportunityLineItemTrigger on OpportunityLineItem (After Insert , After Update) {
	
	Set<String> setOfOppIds = new Set<String>();
	for(OpportunityLineItem opplineitem : Trigger.new){
	    if(Trigger.isInsert){
			if(opplineitem.ProductPrice__c != null && opplineitem.yearly_increase__c != null && opplineitem.Term__c != null){
				setOfOppIds.add(opplineitem.Id);
			}
		}
		if(Trigger.isUpdate){
			//checking if there is any change in 3 fields i.e term, year increase , amount / price
			if(opplineitem.ProductPrice__c != Trigger.oldMap.get(opplineitem.Id).ProductPrice__c && opplineitem.yearly_increase__c != 		Trigger.oldMap.get(opplineitem.Id).yearly_increase__c && opplineitem.Term__c != Trigger.oldMap.get(opplineitem.Id).yearly_increase__c){
										
				setOfOppIds.add(opplineitem.Id);
				
			}
		}
	}
	//query Term Object and delete those records
	
	if(setOfOppIds.size() > 0){
		delete [Select id from term__c where oppLineItem__c in:setOfOppIds];
	}
	
	list<Term__c> lstTerm = new list<Term__c>();
	
	for(OpportunityLineItem opplineitem : Trigger.new){
		
	    if(setOfOppIds.contains(opplineitem.Id)){
		
			Decimal prodprice = opplineitem.ProductPrice__c == null ? 0 : opplineitem.ProductPrice__c;
			Decimal yearIncrease = opplineitem.yearly_increase__c == null ? 0 : opplineitem.yearly_increase__c;
			Decimal term = opplineitem.Term__c == null ? 0 : opplineitem.Term__c;
			if(prodprice != null && yearIncrease != null && term != null){
				
                Term__c termObj = new Term__c(Name = 'Year '+ i);
				termObj.Price__c = opplineitem.ProductPrice__c;
				lstTerm.add(termObj);
				Integer productPrice = opplineitem.ProductPrice__c;
				for(Integer i = 1 ; i<opplineitem.Term__c ; i++){
					Term__c termObj = new Term__c(Name = 'Year '+ (i+1));
					
					termObj.Price__c = productPrice + (productPrice *(opplineitem.yearly_increase__c /100));
					productPrice = termObj.Price__c;
					lstTerm.add(termObj);
				}
			}
		}
	}
	
	if(lstTerm.size() > 0){
		insert lstTerm;
	}
}

If you get an error due to Integer decimal thing using Integer.valueOF, then you need to type cast like this below
for(Integer i = 1 ; i< Integer.valueOf(opplineitem.Term__c) ; i++){

  Let me know if you have trouble implementing this.
Dushyant SonwarDushyant Sonwar
For showing the Lifetime total , you have to make following adjustments.
 
trigger OpportunityLineItemTrigger on OpportunityLineItem (After Insert , After Update) {
	
	Set<String> setOfOppIds = new Set<String>();
	for(OpportunityLineItem opplineitem : Trigger.new){
	    if(Trigger.isInsert){
			if(opplineitem.ProductPrice__c != null && opplineitem.yearly_increase__c != null && opplineitem.Term__c != null){
				setOfOppIds.add(opplineitem.Id);
			}
		}
		if(Trigger.isUpdate){
			//checking if there is any change in 3 fields i.e term, year increase , amount / price
			if(opplineitem.ProductPrice__c != Trigger.oldMap.get(opplineitem.Id).ProductPrice__c && opplineitem.yearly_increase__c != 		Trigger.oldMap.get(opplineitem.Id).yearly_increase__c && opplineitem.Term__c != Trigger.oldMap.get(opplineitem.Id).yearly_increase__c){
										
				setOfOppIds.add(opplineitem.Id);
				
			}
		}
	}
	//query Term Object and delete those records
	
	if(setOfOppIds.size() > 0){
		delete [Select id from term__c where oppLineItem__c in:setOfOppIds];
	}
	
	list<Term__c> lstTerm = new list<Term__c>();
	list<OpportunityLineItem> lstoppLineItem = new list<OpportunityLineItem>();
	
	for(OpportunityLineItem opplineitem : Trigger.new){
		
	    if(setOfOppIds.contains(opplineitem.Id)){
			OpportunityLineItem oppLineITemObj = new OpportunityLineItem(Id = opplineitem.Id);
			
			Decimal prodprice = opplineitem.ProductPrice__c == null ? 0 : opplineitem.ProductPrice__c;
			Decimal yearIncrease = opplineitem.yearly_increase__c == null ? 0 : opplineitem.yearly_increase__c;
			Decimal term = opplineitem.Term__c == null ? 0 : opplineitem.Term__c;
			Decimal totalamount = 0;
			if(prodprice != null && yearIncrease != null && term != null){
				Term__c termObj = new Term__c(Name = 'Year '+ i);
				termObj.Price__c = opplineitem.ProductPrice__c;
				lstTerm.add(termObj);
				totalamount+= termObj.Price__c;
				Decimal productPrice = opplineitem.ProductPrice__c;
				for(Integer i = 1 ; i<opplineitem.Term__c ; i++){
					Term__c termObj = new Term__c(Name = 'Year '+ (i+1));
					
					termObj.Price__c = productPrice + (productPrice *(opplineitem.yearly_increase__c /100));
					productPrice = termObj.Price__c;
					lstTerm.add(termObj);
					totalamount+= termObj.Price__c;
				}
				
			}
			oppLineITemObj.lifetime_total__c = totalamount;
			lstoppLineItem.add(oppLineITemObj);
		}
	}
	
	if(lstTerm.size() > 0){
		insert lstTerm;
	}
	
	if(lstoppLineItem.size() > 0){
		update lstoppLineItem;
	}
}

 
Benjamin CulverBenjamin Culver
Dushyant,

Thank you very much. I did not expect such an answer so quickly.

I was trying to implement and test this in a playground. I created my Term Object and was trying to create the needed lookup field. After searching online I have discovered that a lookup to OpportunityProduct is not supported and many people are hoping for a change. With that lookup being central to this working this solution wont work.

Looking at your code, if that lookup were allowed, I think you nailed it. Again, thank you very much for your time.

 
Dushyant SonwarDushyant Sonwar
Benjamin,

Apologies from my end. Actually lookup field is not allowed , so this solution will not work and vf page cannot be embedded in opportunity Line item.

But we can add lightning component :
 
<aura:component controller="wrapperClassController" implements="force:hasRecordId,flexipage:availableForAllPageTypes">

    <aura:attribute name="recordId" type="String"/>
     <aura:attribute name="totalAmount" type="Decimal"/>
	<aura:handler name="init" value="{!this}" action="{!c.doInit}"/>
    <aura:handler event="force:refreshView" action="{!c.doInit}" />
    <aura:attribute name="lstData" type="List"/>
	<lightning:card title="My Yearly Product Increase"> 
    <table class="slds-table slds-table_cell-buffer slds-table_bordered">
			<thead>
				<tr class="slds-line-height_reset">
					<th class="slds-text-title_caps" scope="col">
						<div class="slds-truncate" title="Year">Year</div>
					</th>
					<th class="slds-text-title_caps" scope="col">
						<div class="slds-truncate" title="Price">Price</div>
					</th>
				</tr>
			</thead>
			<tbody>
        <aura:iteration items="{!v.lstData}" var="item">
         <tr>
            <td>{!item.name}</td> 
             <td> {!item.productPrice} </td>
          </tr>
    </aura:iteration>
                <tr>
            <td>lifetime total</td> 
             <td> {!v.totalAmount} </td>
          </tr>
        </tbody>
        </table>
    </lightning:card>
</aura:component>

Controller JS
({
	doInit: function(cmp) {
       var action = cmp.get("c.generateData");
        action.setParams({ recordId : cmp.get("v.recordId") });

        // Create a callback that is executed after 
        // the server-side action returns
        action.setCallback(this, function(response) {
            var state = response.getState();
            if (state === "SUCCESS") {
                // Alert the user with the value returned 
                // from the server
                var lstdata =  response.getReturnValue();
                var totalAmount = 0;
                for(var i=0 ; i<lstdata.length ; i++){
                    totalAmount +=lstdata[i].productPrice;
                }
                cmp.set('v.totalAmount' , totalAmount);
				cmp.set('v.lstData' , response.getReturnValue());
                // You would typically fire a event here to trigger 
                // client-side notification that the server-side 
                // action is complete
            }
            else if (state === "INCOMPLETE") {
                // do something
            }
            else if (state === "ERROR") {
                var errors = response.getError();
                if (errors) {
                    if (errors[0] && errors[0].message) {
                        console.log("Error message: " + 
                                 errors[0].message);
                    }
                } else {
                    console.log("Unknown error");
                }
            }
        });

        // optionally set storable, abortable, background flag here

        // A client-side action could cause multiple events, 
        // which could trigger other events and 
        // other server-side action calls.
        // $A.enqueueAction adds the server-side action to the queue.
        $A.enqueueAction(action);
    }
})

Apex Class
 
public class wrapperClassController {

 @AuraEnabled 
    public static list<wrapper> generateData(String recordId){

        list<OpportunityLineItem> lstOppItem = [Select Product_Price__c,yearly_increase__c,term__c from opportunityLineItem where id = :recordId];
        
        list<Wrapper> lstWrapper = new list<Wrapper>();
        
        if(lstOppItem.size() > 0){
            Decimal productPrice = lstOppItem[0].Product_Price__c== null ? 0 : lstOppItem[0].Product_Price__c;
            Decimal yearIncrease = lstOppItem[0].yearly_increase__c == null ? 0 : lstOppItem[0].yearly_increase__c;
            Decimal term = lstOppItem[0].Term__c == null ? 0 : lstOppItem[0].Term__c;
            
            wrapper wrp = new wrapper();
            wrp.productPrice = productPrice ;
            wrp.name = 'Year 1';
            lstWrapper.add(wrp);
            for(integer i = 1 ; i<term  ; i++){
                wrapper wrpObj = new wrapper();
                wrpObj.name = 'Year '+ (i+ 1);
                wrpObj.productPrice = productPrice + (productPrice * (yearIncrease  /100));
                productPrice = wrpObj.productPrice;
                
                lstWrapper.add(wrpObj);
            }

        }
        return lstWrapper ;
    }

//Main Wrapper Class STEP 1
    public class wrapper{
       @AuraEnabled //Annotation when using for lightning component
          public Decimal productPrice;
       @AuraEnabled 
          public String name;
    }   
}

After adding the lightnig component it will look something like this below
User-added image

Hope this helps!
 
This was selected as the best answer
Benjamin CulverBenjamin Culver
This is incredible. It's perfect. Thank you very much