How Can I Subtract Values From Two Different Fields In Two Different Objects And Populate A Custom Field In Apex Trigger?

- 1 answer

My plans were to populate a new custom field "Quantity Left" in Invoice__c by subtracting "Quantity_Used__c" in another object "Invoice_Log__c" from the total quantity "Quantity__c". I wanted this to trigger every time another item was used and the Quantity_Used__c field was updated.

I'm very new to Apex so I'm not sure if I am even starting off on the right foot and I didn't know how to populate the new field with the calculation.

trigger InvoiceTrigger on Invoice__c(after update) {
    Integer I;
    for (Invoice__c p : {
        I = p.Quantity__c;
        List<Invoice_Log__c> a = [select Quantity_Used__c from Invoice_Log__c];
        Integer j = I - a;
        system.debug('Quantity left: ' + j);


Invoice_Log__c is related to Invoice__c, right? If the relationship is "master-detail" you might not even need code for this. If it's just a "lookup" think if you want to convert it to M-D. You'll get "rollup summary" fields (which we're going to use), cascade delete (after you delete invoice you wouldn't want to leave free floating line items, right), sharing rules that will automatically go "if you can see invoice - you can see all line items" and other goodies.

So the easy way to do it would be something like

  1. Make sure there's Invoice__c, Parent__c or similar field like that on Invoice_Log__c that points to your Invoice__c object. And that it's Master-Detail.
  2. On Invoice__c object create new field, call it QuantityUsed__c. Type should be "rollup summary". As calculation pick SUM() of line items' Quantity__c.
  3. On Invoice__c object create another field, QuantityLeft__c. Type formula, number. Write the formula as Quantity__c - QuantityUsed__c.

Boom, no code needed. SF will calculate it correctly on create/edit/delete/restore from recycle bin.

If you want to do it code way (for example if there's very good reason there's no relation or it can't be master-detail... there are obscure cases like each sales rep seeing only their little part of the whole order, receiving just a bit of commission... it's weak but I've seen it happen) - it's work. Ideally you'd cover inserts, updates, deletes and undeletes. It can be trickier than you think. Update should handle changing quantity or reparenting the line from one invoice to another ;) And even if - I'd still ask you to consider formula field for final calculation because what when somebody edits the Qty on the parent?

Bit naive but would get the job done (as long as you don't have crazy amount of line items and doing bulk edits) would be trigger on Invoice_Log__c (because that's what being edited, not the Invoice__c).

trigger InvoiceLogTrigger on InvoiceLog__c (after update){
    Set<Id> invoiceIds = new Set<Id>();
    for(InvoiceLog__c il :{
        InvoiceLog__c old = trigger.oldMap.get(il.Id);
        if(il.Quantity__c != old.Quantity__c || il.Invoice__c != old.Invoice__c){
        List<Invoice__c> invoices = [SELECT Id, Quantity__c,
            (SELECT QuantityUsed__c FROM InvoiceLogs__r WHERE QuantityUsed__c != null)
            FROM Invoice__c
            WHERE Id IN :invoiceIds];
        for(Invoice__c i : invoices){
            Integer total = 0;
            for(InvoiceLog__c il : i.InvoiceLogs__r){
                total += il.QuantityUsed__c;
            i.QuantityLeft__c = i.Quantity__c - total; // as I said, I'd just save total and do final calculation as formula
        update invoices;