+ Start a Discussion
Salesforce2015Salesforce2015 

Summarize one field value based on pick-list value

Hi Experts,

I have custom Object Saving__c.
This object contains custom fields Status__c and Final_Amt__c.
Scenario:
We need to calculate All “Final_Amt__c” field values based on Status__c value “In-Progress” and display that value in custom field.
Note: All fields are in one custom object.

Please find below Image.

User-added image

Help will be appreciated, thanks in advance.

Thanks,
Manu
Arunkumar RArunkumar R
Hi Manu,

You can do calculation using trigger. Please take a look on below sample model and change it according to your logic.
 
trigger FinalAmountCalculationTrigger on Saving__c(before insert, before update)
{
	for(Saving__c currSave : Trigger.New)
	{
		if(Trigger.isInsert)
		{
			if(currSave.Status__c == 'In-Progress')
			{
				// do your logic and assign the value to final amt field.
				//currSave.Final_Amt__c = 
			}
			else if(currSave.Status__c == 'Other')
			{
			   // do your logic if status other
			}
		}
		else if(Trigger.isupdate)
		{
		if(currSave.Status__c != Trigger.oldMap.get(Id).Status__c)
		{
			if(currSave.Status__c == 'In-Progress')
			{
				// do your logic and assign the value to final amt field.
				//currSave.Final_Amt__c = 
			}
			else if(currSave.Status__c == 'Other')
			{
			   // do your logic if status other
			}
		
		}
			
		}
	}
}

 
Anirudh SinghAnirudh Singh
Hello Manu,

You can do this without using any code i.e. a Trigger, by using Process Builder.

Please find below the steps for calculating Final Amount using Process Builder:
1.  Go to Setup. Go to Create and expand "Workflow & Approvals".

2.  Now, click on "Process Builder". The Process Builder will be opened. Click on "New".

3.  Give Process Name such as "Calculating Final Amount". Press Tab, API Name will be auto-populated. Give some description if possible. The Process will be created. At the top, the heading of the page will be something like "Process Builder - Calculating Final Amount".

4.  Click on "Add Object".
"Choose Object and Specify When to Start the Process" panel will get open on the right hand side.
Now, in the "Choose Object and Specify When to Start the Process" panel, choose the object as "Saving" in the "Object" field (or the name of the object in your Salesforce organization). Select the "when a record is created or edited" under the "Start the process" field. Click "Save".
Notice, the "Add Object" box now shows "Saving" (or if you selected any other object, then that object name will be displayed).

5.  Now click on "Add Criteria".
"Define Criteria for this Action Group" panel will get open on the right hand side.
In the "Criteria Name" field, give the criteria name as "In-Progress Saving Records" (as we need to filter the In-Progress Saving records for Final Amount calculation).
In the "Criteria for Executing Actions" field, select "Conditions are met".
In the "Set Conditions", create two conditions:
a. Field=Status__c, Operator=Is changed, Type=Boolean, Value=True
b. Click "Add Row".
c. Field=Status__c, Operator=Equals, Type=Picklist, Value=In-Progress.
In the "Conditions" field, select "All of the conditions are met (AND)".
Click "Save".

6.  Now, on the True side of the "In-Progress Saving Records" decsion box, you will find "IMMEDIATE ACTIONS".
Click "Add Action".
"Select and Define Action" panel will be opened on the right hand side.
Select "Action" field value as "Update Records".
"Action Name" and "Record" fields will be displayed.
In "Action Name", give a name such as "Calculate Final Amount".
On clicking "Record" field, a new window will be opened named "Choose a Record to Update".
Click on "Saving__c" on the left hand side of the window.
Click "Choose".
"Criteria for Updating Records" and "Set new field values for the records you update" will be displayed.
Select "No criteria—just update the records!" in the "Criteria for Updating Records" field.
In the "Set new field values for the records you update" field, select Field="Final Amt", Type="Formula" and click on Value.
On clicking Value, a folmula editor will get opened. Create a formula something like [Saving__c].Principal_Amt__c +(( [Saving__c].Principal_Amt__c * [Saving__c].Interest_Rate__c ) /100)
And "Save".

7. Now, on the right hand corner click "Activate" button, a pop-up will appear. Click "Ok".
Now, whenever a "In-Progress" Saving record is created or edited, the Final Amount will be calculated based on the formula created.

Thanks and Regards,
Anirudh Singh
Arunkumar RArunkumar R
Hi Anirudh,

    I agree your process builder approach. Hope process builder won't works for bulk records.
Salesforce2015Salesforce2015
Hi Arun/Anirudh,

Thanks for your inputs.
But, I want to update one field Complete_Amt__c with sum of Final_Amt__c. 

Please find below example.
Assume i have 5 saving object records. each record having Final_Amt__c currency field i need to add 5 Final_Amt__c field values from 5 records and those 5 records field values sum put in to each record Complete_Amt__c currency field.

Thanks,
Manu
Anirudh SinghAnirudh Singh
Hello Arun,

Currently, Process Builder is in Beta Version and it doesn't work for bulk records. Since, each dml and soql is counted, so if the limit is reached, all the records are failed. Please refer to the following links:
https://success.salesforce.com/issues_view?id=a1p30000000T3sCAAS
https://success.salesforce.com/ideaView?id=08730000000DhBlAAK

I have myself tried running a process (Note: This Process used a dml i.e. a update for a field.) for 1000 records, but its throws an error: CANNOT_EXECUTE_FLOW_TRIGGER:The record couldn’t be saved because it failed to trigger a flow. <br>A flow trigger failed to execute the flow with version ID 301900000000Sjy. <br/> Contact your administrator for help.:--

Thanks and Regards,
Anirudh Singh
ManojjenaManojjena
Hi Mannu,

I have few doubt in your query .

If you have only one object Saving__c,Assume you have 10 records in the object ,out of 5 are in progress status .then final amout will display in all 10 records or will summation of all 5 inprogress records final amout or what ?

Please clarify ?

 
Anirudh SinghAnirudh Singh
Hello Mannu,

You can achieve your scenario using the below Trigger, but if the records are more than 50,000, then you must use a batch process and schedule it to run at certain time intervals.

Please find below the trigger:

trigger SavingTrigger on Saving__c(after insert, after update)
{
    if(RecordsUpdated.recordsUpdated==false)
    {
        Double finalAmount=0.0;
        
        //Calculating Final Amt in all the records with Status "In-Progress".
        for(Saving__c savingRecord: [SELECT Final_Amt__c FROM Saving__c WHERE Status__c='In-Progress'])
        {
            finalAmount+=savingRecord.Final_Amt__c;
        }
        
        List<Saving__c> savingRecordsToUpdateList=new List<Saving__c>();
        
        //Populating Final Amt in all the records with Status "In-Progress".
        //Remove the WHERE Clause if the value is to be populated in all the records irrespective of the Status.
        for(Saving__c savingRecord: [SELECT Id FROM Saving__c WHERE Status__c='In-Progress'])
        {
            savingRecord.Total_Final_Amt__c=finalAmount;
            savingRecordsToUpdateList.add(savingRecord);
        }
        
        if(savingRecordsToUpdateList.size()>0)
        {
            RecordsUpdated.recordsUpdated=true;
            update savingRecordsToUpdateList;
        }
    }
}

You need to use a class too, to prevent the trigger from running again after an update of a record. Please find below the class used in the trigger:

public class RecordsUpdated
{
    public static Boolean recordsUpdated=false;
}

Please let me know if this helps.

Thanks and Regards,
Anirudh Singh