+ Start a Discussion
Kenji775Kenji775 

Prevent/undo creation of records that cause rollup fields to exceed a set value

Hey all,

I've asked a question similar to this before but I think I can phrase it a little more simply this time. I need to create a trigger that is of course bulk capable that will prevent or rollback any record that causes a rollup field to exceed a value provided. 

 

We use campaigns to track attendance to an event. We have a type of object associated with these events called respondents. We only allow so many different kinds of respondents (males, females, over 18 under 60 etc) to these events. These rules are called quota, we support a maximum of 10 different quota. On the campaign there are fields such as "quota 1 name", "quota 1 required", "quota 1 actual". The "required" is an integer that defines how many of that quota we need, and the "actual" is the rollup that says how many of that kind of person we have recruited.

 

My first thought is just to insert/udpate all the respondents in the trigger data, then query for any campaigns that exceed their quota. Figure out by how much and delete that many respondents that fit that quota. But the problem with that is a person can fit more than one quota, so that doesn't work very well, also it's fairly messy. 

 

The other thought is just grab the current value of the rollup then do the math myself. The really hard part comes with make it bulk safe and dealing with updates/deletes. If i have an incoming list of 100 different respondents, going into 5 different campaigns, I need to break up the list and calculate quota per campaign (which I have working). If it is an insert, I just add 1 to each quota they represent and toss an error if it goes over the set value. But if it's an update I have to remove 1 from every quota they used to represent, then add 1 to the ones they do now. I have a feeling this will be the way I end up going but the complexity scares the Sh** out of me. Thoughts?

Best Answer chosen by Admin (Salesforce Developers) 
mtbclimbermtbclimber

If your requirement is inline with the subject of this post I might have good news for you - you may need NO code to accomplish this.

 

Here's the proposed solution, let me know if it breaks down and why:

 

Add a formula validation rule on the campaign object. Navigate to Setup | App Setup | Customize | Campaigns | Validation Rules.   Create a new rule and lets say your rollup summary field is called "Number of Children" and you want the max to be 10. Create a rule with a formula like this: 

 

 

Number_Of_Children__c > 10

Add your message to appear to the user when they save new/existing children that impact your respective rollup summaries and that's it.

 

When we calculate roll up summary formula fields like this we are actually updating the parent (campaign in this case) whenever a changes happens to the children. We do it in bulk for you and always run the triggers, validations, workflow, etc on the parent. 

 

If you want to get even fancier you could abstract the value from the formula out to a custom setting that you could expose for management to specific users via a visualforce page which could even vary by profile or user meaning you could allow a different maximum for users within different profiles or individually. Maybe not applicable here but interesting for other use cases perhaps.

 

The formula for that would look like this:

 

 

 

Number_Of_Children__c  >  $Setup.Campaign_Settings__c.Max_Children__c

 

 

 

Hope that helps.

 

Let me know if this solution doesn't work for you.

All Answers

mtbclimbermtbclimber

If your requirement is inline with the subject of this post I might have good news for you - you may need NO code to accomplish this.

 

Here's the proposed solution, let me know if it breaks down and why:

 

Add a formula validation rule on the campaign object. Navigate to Setup | App Setup | Customize | Campaigns | Validation Rules.   Create a new rule and lets say your rollup summary field is called "Number of Children" and you want the max to be 10. Create a rule with a formula like this: 

 

 

Number_Of_Children__c > 10

Add your message to appear to the user when they save new/existing children that impact your respective rollup summaries and that's it.

 

When we calculate roll up summary formula fields like this we are actually updating the parent (campaign in this case) whenever a changes happens to the children. We do it in bulk for you and always run the triggers, validations, workflow, etc on the parent. 

 

If you want to get even fancier you could abstract the value from the formula out to a custom setting that you could expose for management to specific users via a visualforce page which could even vary by profile or user meaning you could allow a different maximum for users within different profiles or individually. Maybe not applicable here but interesting for other use cases perhaps.

 

The formula for that would look like this:

 

 

 

Number_Of_Children__c  >  $Setup.Campaign_Settings__c.Max_Children__c

 

 

 

Hope that helps.

 

Let me know if this solution doesn't work for you.

This was selected as the best answer
Kenji775Kenji775

Thank you for the reply, if this does work that would be awesome. I remember consdering using validation rules before, but for some reason I thought it wouldn't work, though right now what reason that would be I don't know. Either way, this would be great. I'm already in about 200 lines of insane trigger code, and this would make life a lot easier if I could just scrap that. Thank you, I'll let you know how it goes.

Kenji775Kenji775

It works. I can't beleive I totally overlooked such a simple solution. Thank you for sparing me hours and hours of work and needless complexity. If you are ever in MN, near the Twin Cities, send me a private message, I'm buying you a beer.

 

I guess that is what happens when you spend too much time coding, you can forget there are easier solutions :P When the only tool you have is a hammer, every problem looks like a nail I suppose.

mtbclimbermtbclimber

Sure thing.  Not sure when I'll be in MN next but if I'm there I will definitely ping you.  Perhaps we could have that beer in SF this year,  coming to Dreamforce? If not you should definitely consider it (and today is the last day for the cheapest rate) - lots of code saving tips to learn and a great experience for community developers.  How much did this post save your company in terms of time & $? I'm saying there's more where that came from :)

 

Glad I could be of help.

Kenji775Kenji775

I will indeed be coming to dreamforce this year. I went last year and loved it (though we didn't get in till later so I missed the developer session at the beginning so I was a little bummed about that). But yes, I look forward to getting there again this year, and hopefully meeting up at some point. Just hit me up a little closer to the event and we can try and get something on our calendars. Thanks again!