+ Start a Discussion

Write a running total formula over multiple fields? Are there variables or something similar in SF?

Hi there,


I am trying to recreate a running total in Salesforce  from our old database system.  We have a custom object in SF with the following fields:

Producer1      Tyler             Producer 1 income $10k

Producer2      Carla           Producer 2 Income  $5k

Referral           Brian           Referral Income $2k


Each field has a picklist of users that can earn those particular income streams.  Those same people play different roles in each transaction so I need to run a report to include ALL the income a person gets. I'm not sure how to write a formula to loop to each field and add the amounts up in a report. Below is how I wrote it before as a crystal variable, but not sure how to loop around to do the same thing in SF. Are there variables or something similar in SF? Below I had to write this formula for each person to get their grand totals, but if there is a better/easier way to do this I am open to it!!! I just need to figure out a solution ASAP!


 := 0;
if {Referral To} = "Tyler" then TB + {referral amount}
else if {Producer 2} = "Tyler" then TB + {producer 2 income}
else if {Producer} = "Tyler" then TB+ {producer income}


This formula starts at zero and looks at a filtered group of records marked as "paid" and loops to each record looking where Tyler earned an income as producer, producer 1, and referral and gives me a grand total. We then use that grand total as that person's income and have leaderboards, etc. used with that total amount.


Any help would be greatly appreciated. I'm stuck! Thanks so much!



You *Might* be able to do this as a Matrix report, but I think you need a separate report for each of the 3 different types of income.


Otherwise, this is beyond what a Salesforce formula is able to do. You're going to need to delve into VisualForce / Apex to get at this information.