There are 2 fields MaxAge and MinAge on Master object(Parent). I have to check If ANY age between MinAge and MaxAge is not covered by the child records, then update Status of the parent to Inactive.

Example :

Parent : Min = 12 , Max =17.

We have 2 child records ageFrom-ageTo -> 12-14 and 15-17 , this covers all the range between 12-17.

Now suppose the child record data is : ageFrom-ageTo -> 12-13 and 16-17 , so I have to detect the GAP, 14-15 and Change Status.

This should happen on Create, update and delete.

public with sharing class ageGapAnalysisHelper {
public static void ageGapAnalysis(List<Price_Plan__c> ppRec){
    List<String> servCatList = new List<String>();    
    Set<Decimal> minAgeList = new Set<Decimal>(); 
    Set<Decimal> maxAgelList = new Set<Decimal>(); 
    Map<Id, List<Price_Plan__c>> existingPPs = new Map<Id, List<Price_Plan__c>>();

    for(Price_Plan__c pp : ppRec) { 
    for(Price_Plan__c e : [select id, Service_Catalogue__c, Age_From__c, Age_To__c, Service_Catalogue__r.CatcondMinAge__c,Service_Catalogue__r.CatCondMaxAge__c,            Service_Catalogue__r.Status__c from Price_Plan__c where Service_Catalogue__c in :servCatList AND Id NOT IN :Trigger.New]) {                       
                                            if (!existingPPs.containsKey(e.Service_Catalogue__c)) {
                                                existingPPs.put(e.Service_Catalogue__c, new List<Price_Plan__c>());
                                            List<Price_Plan__c> ppList = existingPPs.get(e.Service_Catalogue__c);
                                            existingPPs.put(e.Service_Catalogue__c, ppList);


    for(Price_Plan__c pp : ppRec) {
        if (existingPPs.containsKey(pp.Service_Catalogue__c)) {
            for (Price_Plan__c e : existingPPs.get(pp.Service_Catalogue__c)) {
            //logic in if
                if () 

                   pp.Service_Catalogue__r.Status__c = 'Inactive'; // update Service Catalogue status to Inactive


I am getting a map of Map<Parent Id, List<child Rec>>, and comparing the existing with the new one. Need to add logic in if.

  • Is this a Master Detail relationship? Have you tried to do this using formula field/rollup summary? Apr 14 '17 at 6:54
  • Yes its master detail. If we had to check within a range it was fine. But the scenario is different. We have to check for different ranges within a range.
    – wizard
    Apr 14 '17 at 6:58

I think you can do this using some custom fields.

In your child object, create a formula field to calculate the Gap.

Field- Gap

Formula: (Age_To__c - Age_From__c)

Next , Create a rollup summary field on th Parent object which will count the number of child.

Field: CountOfChildren__c

Rollup: Count Of Children.

Next, create another rollup summary field on the Parent object which will sum the Gap field from the children.

Field: SumOfGap__c

Rollup: Sum Of Gap.

Now, if your status field is a formula field, you can simply update the formula to:

   (CatCondMaxAge__c - CatCondMinAge__c) - (SumOfGap__c + (CountOfChildren__c -1)) == 0, 'Active', 'Inactive' 

OR you can do a similar thing using workflow as well if your field is not a formula field.

If the difference between the Gap in Sum of children's gap and parent's gap is 0, means there is no Gap.

Here is a scenario analysis:

enter image description here

I have seen a comment of yours saying that there is validation rule avoiding the overlapping of max and min ages. So I have taken that into consideration.

  • 1
    thanks for your help. But the problem here is it depends on the division. Example MinAge =12 and MaxAge=17. And We have 2 child records ageFrom-ageTo -> First Scenario : 12-14 and 15-17 differnce is 4, But if we divide in 3(3 child records) ex. 12-13, 14-15, 16-17 the difference is 3
    – wizard
    Apr 14 '17 at 9:55
  • That's a very good point. I have updated my answer with another rollup summary field - CountOfChild. Apr 14 '17 at 10:10
  • 1
    thanks a ton Santanu. You made it such an easy one. I wudve banged my head on the trigger. Thanks again :)
    – wizard
    Apr 14 '17 at 13:54
  • Would this method work if there were child records with overlapping age ranges (say Child 1 - Age_From:12, Age_To:14, and Child 2 - Age_From:13, Age_To: 15 for a Parent 1 - CatCondMinAge:12, CatCondMaxAge:17)?
    – Derek F
    Apr 14 '17 at 13:59
  • Hi Derek, in my case I have a validation to check overlapping scenarios. but thanks for your help it can be used as solution where overlapping is involved.
    – wizard
    Apr 14 '17 at 14:39

Probably the simplest way I can think of to achieve this would be to make use of a Set<Integer> (if you're only ever working on a single parent) or Map<Id, Set<Integer>> (if there is the possibility of working on more than one parent at a time).

Since the Map<Id, Set<Integer>> will work with a single parent as well, I'll just use that in my examples.

The basic idea is this:

  • For each parent, take its CatCondMinAge__c and CatCondMaxAge, and expand that range to an actual list of integers
  • Place the parent's integer list into the map
  • For each child of the parent, take its Age_From__c and Age_To__c, and expand that range to another list of integers
  • Pull the parent range from the map (the child record will always have its parent's Id)
  • Remove the child's range from the parent's range
  • After you have iterated over each child, any parent record whose corresponding Set<Integer> isn't empty contains a gap and needs to be made inactive

It actually looks like you were on this track yourself. The big difference between our approaches is that I'm using a Set<Integer> instead of a List<Price_Plan__c>.

An implementation of the algorithm I described above:

// I'll be assuming that this is being called from a trigger, and that you already
//   have logic to handle passing the appropriate Price_Plan__c records into this method
public static void ageGapAnalysis(List<Price_Plan__c> ppRec){
    // Gather the Service_Catalog__c Ids, same as your existing code
    Set<Id> serviceCatalogIdSet = new Set<Id>();
    for(Price_Plan__c pp :ppRec){

    // Query the Service_Catalog__c records, and build the ranges map
    Map<Id, Set<Integer>> serviceCatalogIdToAgeRange = new Map<Id, Set<Integer>>();

    Set<Integer> tempRange;
    for(Service_Catalog__c catalog :[SELECT Id, CatCondMinAge__c, CatCondMaxAge__c FROM Service_Catalog__c WHERE Id IN :serviceCatalogIdSet]){
        tempRange = new Set<Integer>();

        // A good, old-fashioned for loop.
        // I don't know of any other way to generate the range that we need
        for(Integer i = catalog.CatCondMinAge__c; i <= catalog.CatCondMaxAge__c; i++){

        serviceCatalogIdToAgeRange.put(catalog.Id, tempRange);

    // Now we need to query all of the child records.
    // I'm not sure if this query would still pull child records that are in the process
    //   of being deleted.
    // You may have to adjust the logic here a bit.
    // If this query does pull records that are being deleted, you could change
    //   this method to accept 2 arguments (list of recs coming from trigger, list
    //   of recs being deleted), put this query into a Map instead of a list,
    //   and remove the list of records being deleted from the map.
    // I'd prefer that method over using 'Id NOT IN <Ids>', because using 'NOT IN'
    //   can easily make a query non-selective.
    List<Price_Plan__c> allInvolvedPricePlans = [SELECT Id, Service_Catalog__c, Age_From__c, Age_To__c FROM Price_Plan__c WHERE Service_Catalog__c IN :serviceCatalogIdSet];

    for(Price_Plan__c pp :allInvolvedPricePlans){
        tempRange = new Set<Integer>();

        for(Integer i = pp.Age_From__c; i <= pp.Age_To__c; i++){

        // here's the important part.
            // I don't expect the above check to be required, but better safe than 
            //   sorry.

    // One final loop to identify the Service_Catalog__c records that need to be made
    //   incactive.
    List<Service_Catalog__c> serviceCatalogsToDeactivate = new List<Service_Catalog__c>();
    for(Id serviceCatalogId :serviceCatalogIdsToAgeRange.keySet()){
        // If the age range for this service catalog isn't empty, then we
        //   know for a fact that the children don't cover the entire range
            serviceCatalogsToDeactivate.add(new Service_Catalog__c(
                Id = serviceCatalogId,
                Status__c = 'Inactive'

    update serviceCatalogsToDeactivate;

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.