How to validate aggregate on child records

Question

I have an object, lets call it Bill__c, that has a related list containing Contacts. In this relation each of the Contacts can be assigned a percentage of the total fee due on the Bill__c.

I'd like to write some validation so that the total percentage on the Bill is always 100.

However, users will need the ability to "temporarily" create child records where the percentage on the parent is not 100.

Ie I enter the first Contact and assign 50%. This relation should be saved without error. However an error appears on the parent record indicating a total percentage error.

I then enter a second Contact and assign 50%. This relation should be saved without error and the error clears from the parent record.

Is there a way to do this?

Answer 1

There are a few different approached you could take, but you're going to have to compromise a bit.

My first suggestion is to define a "time" where the validation rule takes place. For example, on Bill__c you have a submit button that only 'submits' the record if the percentages add up to 100%. This way all the children can be configured and followed up with a simple press of a button to indicate from here on out, there should be no variation from 100%.

My second recommendation (which I don't like as much because it requires more code to maintain) is to make a Visualforce page to edit multiple children at once. Instead of adding them individually and configuring percentages there, you can provide a page to edit them all at once so your validation rule will always apply.

Obviously you'll need the knowledge to create pages, roll-up summaries, buttons, etc for either or both of these solutions.

Answer 2

The only way you could do what you're asking is to "turn off" validation while creating the child records.

One alternative would be to have other child records display at the same time, while allowing the validation to either auto adjust them to 100% during creation or proportionalize them in some manner according to a predetermined algorithm; which might work best if the number of expected children has been specified in advance. I say the latter because you could specify the final percentages you want to have for each one if you know the number of them and do it in that order. If you don't, you'll get what I'll describe next.

The first child created would automatically be set to 100%. When you create the 2nd child and set it to say 40% during creation, the validation could automatically adjust the 1st child to 60%. You could set up a similar approach where when a 3rd child is created and a proportion specified for it, the remainder is adjusted according to the proportions already set for them. In the current example. If the 3rd one was created with 30%, that would leave 70% available to split between 1 and 2. 40% of 70, or 28% would be assigned to child 1 and 60% or 70, or 42% would be assigned to child 2.

This type of thing becomes inconvenient for a user when the numbers automatically adjust like this later. Every time you change one value, all the rest automatically adjust themselves proportionately; causing a user to need to make several adjustments to get the results they would like to have rather than just entering what they want.

So, the last alternative would be to have all the children to the side, including the new one that's being created, with a sum of the percentage totals below them at the bottom. Then, allow the user to adjust them as each one is being created, but require that they total to 100%.

I don't think there are any "optimal" solutions for what you want to do. If you allow the validation to be turned off, its unlikely it will ever be turned back on once all the children have been created.