+ Start a Discussion
Luke GreenstreetLuke Greenstreet 

Merge Formula Field Values Using Trigger?

Hi,

I was recently asked to create three large formula fields in a custom object we call Loan App.

They would calculate a high value, medium value and low value based on several different variables and would only one of the fields would be populated at any given time.  All the formulas work fine but i have now been asked if i can get the value returned to show in a separate field for reporting. The combined character value is about 9k so there is no way i am able to use a formula field to return a value. 

I have read that triggers can be used to update fields but have only found examples where they are reading one value from another field and not multiples.

If i was going to write it in a formula i would do this:
IF(AND(Payment_Terms__c >=1,Loancalclow__C<> null) , loancalclow__C,
IF(AND(Payment_Terms__c >=1,Loancalclmed__C<> null) , loancalcmed__C,
IF(AND(Payment_Terms__c >=1,Loancalclhigh__C<> null) , loancalchigh__C,
0),0),0)

Can anyone help?

Thanks in advance
cl0s3rcl0s3r
You can perform compound if statements 'AND' = '&&'....
ex:
If(object,field_1 == 'String/Value ' && object.field_2 == 'String/Valkue' etc...){
object.Target = 'Update Value';
}

update object;
Ariel GorfinkelAriel Gorfinkel
Formulas do not cause trigger / workflow activation, as it is not considered an object update.
You need to create a trigger on the object that the formulas are referring its fields in their calculations.
When a field updates (and should cause a formula value change), you'll need to update all the Loan App objects that refers to it.
As the formula values are not updated until after insert / update of the referred object, you'll need to do it on a "after" event.
Your trigger will look something like:
trigger CalcObjectTrigger on CalcObject__c ( after update){
    CalcObject__c oldValue;
    List<Id> changedCalcObjects = new List<Id>();
    for (CalcObject__c curCalcObject : Trigger.new){
        oldValue = Trigger.oldMap.get(curCalcObject.Id);
        if (curCalcObject.Field__c != oldValue.Field__c){
            changedCalcObjects.add(curCalcObject.Id);
        }
    }
    if (!changedCalcObjects.isEmpty()){
        List<Loan_App__c> loanApps = [SELECT Loancalclow__c, Loancalclmed__c, Loancalclhigh__c FROM Loan_App__c 
                                      WHERE Lookup_Field__c In :changedCalcObjects];
        for (Loan_App__c curLoanApp : loanApps){
            curLoanApp.Calc_field = curLoanApp.Loancalclow__c != null ? curLoanApp.Loancalclow__c :
                curLoanApp.Loancalclmed__c != null ? curLoanApp.Loancalclmed__c :
                curLoanApp.Loancalclhigh__c != null ? curLoanApp.Loancalclhigh__c : 0;
        }
        Database.update(loanApps);
    }
}

You might need to cast Trigger.new and Trigger.oldMap.
You'll need to replace CalcObject with your object, and add the list of fields that affect the calculation to the first if clause.

Please let me know if this helps.