+ Start a Discussion
chandra prakash 58chandra prakash 58 

validate sum of child record based upon parent record

I have two custom objecrt 1. stock_register__c 2. stock_regirser_line__c
i have master detail relation field quantity__c with stock_register__c(parent object) and  stock_regirser_line__c (chield)

I need to write a before insert and before update trigger to restrict the  sum of quantiy__in stock_regirser_line__c can not be more that  allocated_quanity__c in the parent object(stock_register__c)

Please help!!
thanks in advance!!
Best Answer chosen by chandra prakash 58
Abdul KhatriAbdul Khatri
Hi Chandra,

Please let me know if the below code fulfill your requirements.

Apex Trigger
trigger StockRegisterLineTrigger on stock_register_line__c (before insert, before update) {

    Map<Id, stock_register_line__c> currentQuantityMap = new Map<Id, stock_register_line__c>();
    Map<Id, Decimal> totalQuantityMap = new Map<Id, Decimal>();
    Map<Id, Decimal> incomingQuantityMap = new Map<Id, Decimal>();
    
    for(stock_register_line__c srl : trigger.new)
    {
        if(incomingQuantityMap.containsKey(srl.stock_register__c)){
            Decimal tempQuantity = incomingQuantityMap.get(srl.stock_register__c) + (srl.Quantity__c == null ? 0 : srl.Quantity__c);
            incomingQuantityMap.put(srl.stock_register__c, tempQuantity);
        }else{
            incomingQuantityMap.put(srl.stock_register__c, srl.Quantity__c == null ? 0 : srl.Quantity__c);
        }       
        currentQuantityMap.put(srl.stock_register__c, srl);
    }
    
    for (stock_register__c stockReg : [SELECT Id, allocated_quanity__c, (SELECT Quantity__c FROM stock_register_lines__r)  
                                            	FROM stock_register__c
                                           		WHERE Id = :currentQuantityMap.keySet()])
    {
        decimal currentQuantity = 0.0;
        decimal allocatedQuantity = stockReg.allocated_quanity__c == null ? 0 : stockReg.allocated_quanity__c;
        decimal incomingQuantity = incomingQuantityMap.get(stockReg.Id);
        if(stockReg.stock_register_lines__r != null && !stockReg.stock_register_lines__r.isEmpty())
        {
            for(stock_register_line__c srlRec : stockReg.stock_register_lines__r)
            {	
                if(srlRec.Id == null || srlRec.Id != currentQuantityMap.get(stockReg.Id).Id)              
                	currentQuantity =+ (srlRec.Quantity__c == null ? 0 : srlRec.Quantity__c);
            }
        } system.debug('currentQuantity : ' + currentQuantity);
        if(allocatedQuantity < (currentQuantity + incomingQuantity))
            currentQuantityMap.get(stockReg.Id).addError('Total stock register line quantity cannot be greater than allocated quatity');
            
    }

}

Test Class with !00% coverage
@isTest
public class StockRegisterLineTriggerTest {

    @IsTest
    static void testBeforeInsertError()
    {
        stock_register__c srToInsert = new stock_register__c();
        srToInsert.Name = 'test1';
        srToInsert.allocated_quanity__c = 200.0;
        insert srToInsert;

		List<stock_register_line__c> srlToInserList = new List<stock_register_line__c>();
        
        stock_register_line__c srlToInsert = new stock_register_line__c();
        srlToInsert.Name = 'testline1';
        srlToInsert.stock_register__c = srToInsert.Id;
        srlToInsert.Quantity__c = 100.0;
        srlToInserList.add(srlToInsert);

        stock_register_line__c srlToInsert2 = new stock_register_line__c();
        srlToInsert2.Name = 'testline1';
        srlToInsert2.stock_register__c = srToInsert.Id;
        srlToInsert2.Quantity__c = 150.0;
        srlToInserList.add(srlToInsert2);        
        
        try{
        	insert srlToInserList;
        }catch(Exception ex){
            system.debug(ex.getMessage());
            system.assert(ex.getMessage().contains('Total stock register line quantity cannot be greater than allocated quatity'));
        }
    }
    
    @IsTest    
    static void testBeforeUpdateError()
    {
        stock_register__c srToInsert = new stock_register__c();
        srToInsert.Name = 'test1';
        srToInsert.allocated_quanity__c = 200.0;
        insert srToInsert;

		List<stock_register_line__c> srlToInserList = new List<stock_register_line__c>();
        
        stock_register_line__c srlToInsert = new stock_register_line__c();
        srlToInsert.Name = 'testline1';
        srlToInsert.stock_register__c = srToInsert.Id;
        srlToInsert.Quantity__c = 100.0;
        srlToInserList.add(srlToInsert);

        stock_register_line__c srlToInsert2 = new stock_register_line__c();
        srlToInsert2.Name = 'testline1';
        srlToInsert2.stock_register__c = srToInsert.Id;
        srlToInsert2.Quantity__c = 100.0;
        srlToInserList.add(srlToInsert2);        

        insert srlToInserList;
        
        try{
        
            stock_register_line__c srlToUpdate = [SELECT Id, Quantity__c FROM stock_register_line__c WHERE Id = :srlToInsert.Id LIMIT 1];
            srlToUpdate.Quantity__c = 150;
            update srlToUpdate;

        }catch(Exception ex){
            system.debug(ex.getMessage());
            system.assert(ex.getMessage().contains('Total stock register line quantity cannot be greater than allocated quatity'));
        }
    }    
}


 

All Answers

AbhishekAbhishek (Salesforce Developers) 
I think you can't create a parent record formula that references child records since the formula won't know which child record to use. 


For reference, you can check below,

https://developer.salesforce.com/forums/?id=9060G000000UaBLQA0

https://salesforce.stackexchange.com/questions/211883/validation-on-the-cumulative-sum-of-a-field-on-child-records#:~:text=Probably%20the%20easiest%20way%20to,the%20update%20to%20the%20parent.


If it helps you and closes your query by marking it as the best answer so that it can help others in the future.

Thanks.
Abdul KhatriAbdul Khatri
Hi Chandra,

Please let me know if the below code fulfill your requirements.

Apex Trigger
trigger StockRegisterLineTrigger on stock_register_line__c (before insert, before update) {

    Map<Id, stock_register_line__c> currentQuantityMap = new Map<Id, stock_register_line__c>();
    Map<Id, Decimal> totalQuantityMap = new Map<Id, Decimal>();
    Map<Id, Decimal> incomingQuantityMap = new Map<Id, Decimal>();
    
    for(stock_register_line__c srl : trigger.new)
    {
        if(incomingQuantityMap.containsKey(srl.stock_register__c)){
            Decimal tempQuantity = incomingQuantityMap.get(srl.stock_register__c) + (srl.Quantity__c == null ? 0 : srl.Quantity__c);
            incomingQuantityMap.put(srl.stock_register__c, tempQuantity);
        }else{
            incomingQuantityMap.put(srl.stock_register__c, srl.Quantity__c == null ? 0 : srl.Quantity__c);
        }       
        currentQuantityMap.put(srl.stock_register__c, srl);
    }
    
    for (stock_register__c stockReg : [SELECT Id, allocated_quanity__c, (SELECT Quantity__c FROM stock_register_lines__r)  
                                            	FROM stock_register__c
                                           		WHERE Id = :currentQuantityMap.keySet()])
    {
        decimal currentQuantity = 0.0;
        decimal allocatedQuantity = stockReg.allocated_quanity__c == null ? 0 : stockReg.allocated_quanity__c;
        decimal incomingQuantity = incomingQuantityMap.get(stockReg.Id);
        if(stockReg.stock_register_lines__r != null && !stockReg.stock_register_lines__r.isEmpty())
        {
            for(stock_register_line__c srlRec : stockReg.stock_register_lines__r)
            {	
                if(srlRec.Id == null || srlRec.Id != currentQuantityMap.get(stockReg.Id).Id)              
                	currentQuantity =+ (srlRec.Quantity__c == null ? 0 : srlRec.Quantity__c);
            }
        } system.debug('currentQuantity : ' + currentQuantity);
        if(allocatedQuantity < (currentQuantity + incomingQuantity))
            currentQuantityMap.get(stockReg.Id).addError('Total stock register line quantity cannot be greater than allocated quatity');
            
    }

}

Test Class with !00% coverage
@isTest
public class StockRegisterLineTriggerTest {

    @IsTest
    static void testBeforeInsertError()
    {
        stock_register__c srToInsert = new stock_register__c();
        srToInsert.Name = 'test1';
        srToInsert.allocated_quanity__c = 200.0;
        insert srToInsert;

		List<stock_register_line__c> srlToInserList = new List<stock_register_line__c>();
        
        stock_register_line__c srlToInsert = new stock_register_line__c();
        srlToInsert.Name = 'testline1';
        srlToInsert.stock_register__c = srToInsert.Id;
        srlToInsert.Quantity__c = 100.0;
        srlToInserList.add(srlToInsert);

        stock_register_line__c srlToInsert2 = new stock_register_line__c();
        srlToInsert2.Name = 'testline1';
        srlToInsert2.stock_register__c = srToInsert.Id;
        srlToInsert2.Quantity__c = 150.0;
        srlToInserList.add(srlToInsert2);        
        
        try{
        	insert srlToInserList;
        }catch(Exception ex){
            system.debug(ex.getMessage());
            system.assert(ex.getMessage().contains('Total stock register line quantity cannot be greater than allocated quatity'));
        }
    }
    
    @IsTest    
    static void testBeforeUpdateError()
    {
        stock_register__c srToInsert = new stock_register__c();
        srToInsert.Name = 'test1';
        srToInsert.allocated_quanity__c = 200.0;
        insert srToInsert;

		List<stock_register_line__c> srlToInserList = new List<stock_register_line__c>();
        
        stock_register_line__c srlToInsert = new stock_register_line__c();
        srlToInsert.Name = 'testline1';
        srlToInsert.stock_register__c = srToInsert.Id;
        srlToInsert.Quantity__c = 100.0;
        srlToInserList.add(srlToInsert);

        stock_register_line__c srlToInsert2 = new stock_register_line__c();
        srlToInsert2.Name = 'testline1';
        srlToInsert2.stock_register__c = srToInsert.Id;
        srlToInsert2.Quantity__c = 100.0;
        srlToInserList.add(srlToInsert2);        

        insert srlToInserList;
        
        try{
        
            stock_register_line__c srlToUpdate = [SELECT Id, Quantity__c FROM stock_register_line__c WHERE Id = :srlToInsert.Id LIMIT 1];
            srlToUpdate.Quantity__c = 150;
            update srlToUpdate;

        }catch(Exception ex){
            system.debug(ex.getMessage());
            system.assert(ex.getMessage().contains('Total stock register line quantity cannot be greater than allocated quatity'));
        }
    }    
}


 
This was selected as the best answer
chandra prakash 58chandra prakash 58
trigger StockRegisterLineTrigger on Stock_Register_ine__c (before insert, before update) {

    Map<Id, Stock_Register_ine__c> currentQuantityMap = new Map<Id, Stock_Register_ine__c>();
    Map<Id, Decimal> totalQuantityMap = new Map<Id, Decimal>();
    Map<Id, Decimal> incomingQuantityMap = new Map<Id, Decimal>();
    
    for(Stock_Register_ine__c srl : trigger.new)
    {
        if(incomingQuantityMap.containsKey(srl.Stock_Register1__c)){
            Decimal tempQuantity = incomingQuantityMap.get(srl.Stock_Register1__c) + (srl.Allocation_quantity_for_this_month__c == null ? 0 : srl.Allocation_quantity_for_this_month__c);
            incomingQuantityMap.put(srl.Stock_Register1__c, tempQuantity);
        }else{
            incomingQuantityMap.put(srl.Stock_Register1__c, srl.Quantity__c == null ? 0 : srl.Quantity__c);
        }       
        currentQuantityMap.put(srl.Stock_Register1__c, srl);
    }
    
    for (Stock_Register__c stockReg : [SELECT Id, Actual_Quantity_For_Allocation__c, (SELECT Allocation_quantity_for_this_month__c FROM Stock_Register_ine__r)  
                                                FROM Stock_Register__c
                                                   WHERE Id = :currentQuantityMap.keySet()])
    {
        decimal currentQuantity = 0.0;
        decimal allocatedQuantity = stockReg.allocated_quanity__c == null ? 0 : stockReg.allocated_quanity__c;
        decimal incomingQuantity = incomingQuantityMap.get(stockReg.Id);
        if(stockReg.Stock_Register_ine__r != null && !stockReg.Stock_Register_ine__r.isEmpty())
        {
            for(Stock_Register_ine__c srlRec : stockReg.Stock_Register_ine__c)
            {    
                if(srlRec.Id == null || srlRec.Id != currentQuantityMap.get(stockReg.Id).Id)              
                    currentQuantity =+ (srlRec.Quantity__c == null ? 0 : srlRec.Quantity__c);
            }
        } system.debug('currentQuantity : ' + currentQuantity);
        if(allocatedQuantity < (currentQuantity + incomingQuantity))
            currentQuantityMap.get(stockReg.Id).addError('Total stock register line quantity cannot be greater than allocated quatity');
            
    }

}


**********************************************************************************************************

  for (Stock_Register__c stockReg : [SELECT Id, Actual_Quantity_For_Allocation__c, (SELECT Allocation_quantity_for_this_month__c FROM Stock_Register_ine__r)  
                                                FROM Stock_Register__c
                                                   WHERE Id = :currentQuantityMap.keySet()])



i am getting the error in this line :


error:


Allocation_quantity_for_this_month__c FROM Stock_Register_ine__r)  
                                           ^
ERROR at Row:1:Column:98
Didn't understand relationship 'Stock_Register_ine__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Abdul KhatriAbdul Khatri
Chandra,

Please check the master detail field on stock_register_line sobject, what is the name? replace that here with __r

User-added image
chandra prakash 58chandra prakash 58
StockRegisterLineTrigger: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Stock_Register_ine__c.Quantity__c Trigger.StockRegisterLineTrigger: line 36, column 1


User-added image
chandra prakash 58chandra prakash 58
Thanks, Abdul!!
code successfully saved but still, I am getting some error.
If possible please connect 8840330440. .
It will greater full for me .
Abdul KhatriAbdul Khatri
chandra,

Will call you.

You need to change fields with your names. Anyway, I have done for you, hope the below code will work
 
trigger StockRegisterLineTrigger on Stock_Register_ine__c (before insert, before update) {

    Map<Id, Stock_Register_ine__c> currentQuantityMap = new Map<Id, Stock_Register_ine__c>();
    Map<Id, Decimal> totalQuantityMap = new Map<Id, Decimal>();
    Map<Id, Decimal> incomingQuantityMap = new Map<Id, Decimal>();
    
    for(Stock_Register_ine__c srl : trigger.new)
    {
        if(incomingQuantityMap.containsKey(srl.stock_register__c)){
            Decimal tempQuantity = incomingQuantityMap.get(srl.stock_register__c) + (srl.Allocation_quantity_for_this_month__c == null ? 0 : srl.Allocation_quantity_for_this_month__c);
            incomingQuantityMap.put(srl.stock_register__c, tempQuantity);
        }else{
            incomingQuantityMap.put(srl.stock_register__c, srl.Allocation_quantity_for_this_month__c == null ? 0 : srl.Allocation_quantity_for_this_month__c);
        }       
        currentQuantityMap.put(srl.stock_register__c, srl);
    }
    
    for (stock_register__c stockReg : [SELECT Id, Actual_Quantity_For_Allocation__c, (SELECT Allocation_quantity_for_this_month__c FROM Stock_Register_ine__r)  
                                            	FROM stock_register__c
                                           		WHERE Id = :currentQuantityMap.keySet()])
    {
        decimal currentQuantity = 0.0;
        decimal allocatedQuantity = stockReg.Actual_Quantity_For_Allocation__c == null ? 0 : stockReg.Actual_Quantity_For_Allocation__c;
        decimal incomingQuantity = incomingQuantityMap.get(stockReg.Id);
        if(stockReg.Stock_Register_ine__r != null && !stockReg.Stock_Register_ine__r.isEmpty())
        {
            for(Stock_Register_ine__c srlRec : stockReg.Stock_Register_ine__r)
            {	
                if(srlRec.Id == null || srlRec.Id != currentQuantityMap.get(stockReg.Id).Id)              
                	currentQuantity =+ (srlRec.Allocation_quantity_for_this_month__c == null ? 0 : srlRec.Allocation_quantity_for_this_month__c);
            }
        } system.debug('currentQuantity : ' + currentQuantity);
        if(allocatedQuantity < (currentQuantity + incomingQuantity))
            currentQuantityMap.get(stockReg.Id).addError('Total stock register line quantity cannot be greater than allocated quatity');
            
    }

}
Change the test class accordingly
@isTest
public class StockRegisterLineTriggerTest {

    @IsTest
    static void testBeforeInsertError()
    {
        stock_register__c srToInsert = new stock_register__c();
        srToInsert.Name = 'test1';
        srToInsert.Actual_Quantity_For_Allocation__c = 200.0;
        insert srToInsert;

        List<Stock_Register_ine__c> srlToInserList = new List<Stock_Register_ine__c>();
        
        Stock_Register_ine__c srlToInsert = new Stock_Register_ine__c();
        srlToInsert.Name = 'testline1';
        srlToInsert.stock_register__c = srToInsert.Id;
        srlToInsert.Allocation_quantity_for_this_month__c = 100.0;
        srlToInserList.add(srlToInsert);

        Stock_Register_ine__c srlToInsert2 = new Stock_Register_ine__c();
        srlToInsert2.Name = 'testline1';
        srlToInsert2.stock_register__c = srToInsert.Id;
        srlToInsert2.Allocation_quantity_for_this_month__c = 150.0;
        srlToInserList.add(srlToInsert2);        
        
        try{
            insert srlToInserList;
        }catch(Exception ex){
            system.debug(ex.getMessage());
            system.assert(ex.getMessage().contains('Total stock register line quantity cannot be greater than allocated quatity'));
        }
    }
    
    @IsTest    
    static void testBeforeUpdateError()
    {
        stock_register__c srToInsert = new stock_register__c();
        srToInsert.Name = 'test1';
        srToInsert.Actual_Quantity_For_Allocation__c = 200.0;
        insert srToInsert;

        List<Stock_Register_ine__c> srlToInserList = new List<Stock_Register_ine__c>();
        
        Stock_Register_ine__c srlToInsert = new Stock_Register_ine__c();
        srlToInsert.Name = 'testline1';
        srlToInsert.stock_register__c = srToInsert.Id;
        srlToInsert.Allocation_quantity_for_this_month__c = 100.0;
        srlToInserList.add(srlToInsert);

        Stock_Register_ine__c srlToInsert2 = new Stock_Register_ine__c();
        srlToInsert2.Name = 'testline1';
        srlToInsert2.stock_register__c = srToInsert.Id;
        srlToInsert2.Allocation_quantity_for_this_month__c = 100.0;
        srlToInserList.add(srlToInsert2);        

        insert srlToInserList;
        
        try{
        
            Stock_Register_ine__c srlToUpdate = [SELECT Id, Allocation_quantity_for_this_month__c FROM Stock_Register_ine__c WHERE Id = :srlToInsert.Id LIMIT 1];
            srlToUpdate.Allocation_quantity_for_this_month__c = 150;
            update srlToUpdate;

        }catch(Exception ex){
            system.debug(ex.getMessage());
            system.assert(ex.getMessage().contains('Total stock register line quantity cannot be greater than allocated quatity'));
        }
    }    
}


 
chandra prakash 58chandra prakash 58
Thanks a lot. Dear now work done.