1

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)
1
  • 1
    Formula fields cannot do rollup functionality. You need to look at tools like Declarative Lookup Rollup Summaries or Rollup Helper. Dec 14, 2020 at 0:15

2 Answers 2

0

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.

0

There are a couple ways to achieve your goal, but as noted by others, you cannot use a formula field.

One option, would be to use Declarative Lookup Rollup Summary (DLRS) (or a competitor package) to build the values for it.

Another solution would be to build a scheduled flow that populates the field(s) as needed.

If those two cannot work in your project then I would fall back to a Trigger or Scheduled Job to do the updates.

I generally default to DLRS on most projects, but it's a bit overkill for just one or two fields – for those a flow is likely to work fine in many cases. I try to avoid triggers when I can to keep the overall solution low-code and to make automations of that nature as easy to discover and maintain as possible for future admins (including the version of yourself 6 months from now who might have forgotten the details of what today's you does).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .