I have a custom object called jobs which are related to the master object accounts. Within the jobs object, I have a revenue field and an invoice_date field. On the accounts page layout, I want to display the total revenue for each related job per month of invoice for the current year and the previous year. I want the months/years to be dynamic so I, nor my predecessor, don't have to change them every year. I am currently doing this with the roll-up summaries, but, as I mentioned, I have to change them every year. I have established a lookup relationship within accounts with jobs, as a master-detail relationship doesn't seem to be enough, but cannot seem to get any kind of data from jobs. In addition, there doesn't seem to be a SUM method in formula fields.

Is there any way to sum all data from a specified field (revenue) filtered by another specified field (invoice_date) in a related object (jobs) within a formula field on an object (accounts)?

Ideally, the formula might look like this, except this doesn't work:

if(jobs__c.invoice_date >= DATE(YEAR(TODAY())-1,1,1) AND jobs__c.invoice_date <= DATE(YEAR(TODAY())-1,1,1), SUM(jobs__c.revenue), 0)
  • Formula fields cannot do rollup functionality. You need to look at tools like Declarative Lookup Rollup Summaries or Rollup Helper. – David Cheng Dec 14 '20 at 0:15

First I am not sure this can ever be achieved via Formula. Second the logic which you have written wont work because :

  • You said Account and Jobs have lookup relationship, and you want to roll up Jobs details to account, which means Account will have List, so a formula field on Account can never access the list of Jobs related to it.

So its best to write a trigger on insert, update, delete and undelete on Job object and then get calculate accordingly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.