Update Related records when the current record is updated.

[Akshay Kanerkar]

Hi Folks,
I have an Invoice table and Invoice line table, the fields on the invoice line table are (Payor, Line amount, Invoice) and the fields on the Invoice table are (Payor, Total amount), there are multiple invoice lines associated with the single invoice, the common field between both the table is the payor(which is referenced to payor table), so when I create an invoice line based on the payor selected, the invoice will be automatically created and linked to the Invoice field on the Invoice line table which is referenced to the Invoice, so I want to populate the Total amount field on the Invoice table with the Line amount field value on the Invoice lines associated with the Invoice record. eg. I will create the 2 invoice lines A & B with the line amounts of 10 and 20 respectively, so based on the payor selected the invoice gets created(Assume it INV001) and these two invoice lines(A & B) will be linked to the single Invoice(INV001), so the Total Amount field on the invoice(INV001) should be set as 30(Line amount on A +Line amount on B), if I change the payor for Invoice line A, a new invoice will be created as (INV002), the Total amount of invoice(INV001) should show as 20(Invoice line B) because this is the only one Invoice line it has. Another invoice(INV002) having field total amount should show as 10(Line amount on B). both previous invoice total amount and current invoice total amount should be updated as total from related invoice lines.

[Jupiter W]

If I'm understanding correctly, you want to update the Invoice table record based on the Invoice Line records associated with the Invoice record. A quick and direct way to achieve this is by creating an Advanced-After Business Rule on the Invoice Line table. This rule should do the following:

1. Query all Invoice Line records associated with the same Invoice.
2. Compute the sum of all their line amounts.
3. Update the Total Amount field of the associated Invoice record with this sum.

This approach works well if there aren't too many Invoice Line records associated to the Invoice Record and if the Invoice line record's amount is not constantly updated as this would introduce potential performance concerns. 

[Akshay Kanerkar]

@Jupiter W 
Thank you for responding, what if I want to deduct the line amount from the previous Invoice? Can you check the reply I gave to @Maddysunil 

Below is the script I tried as per your suggestion but no luck.
After update business rule.
var totalAmt = 0;
var invLine = new GlideRecord('invoice_line');
invLine.addQuery('invoice', current.invoice);
invLine.query();
while(invLine.next()){
totalAmt += parseFloat(invLine.line_amount); 
}
var inv = new GlideRecord('invoice');
if(inv.get(current.invoice)){
inv.total_amount = totalAmt;
inv.update();
}

[Maddysunil]

@Akshay Kanerkar 

I think you can create business Rule which will trigger whenever a new Invoice Line record is created or updated. It will call a Script Include to recalculate the Total Amount on the associated Invoice record. Script Include will contain the logic to calculate the Total Amount for an Invoice based on its associated Invoice Lines.

Business Rule: On After insert/update on Invoice Line table

new YourScriptIncludeName.updateInvoiceTotal(current.invoice.toString());

Script Include:

// Function to update Total Amount on associated Invoice
    updateInvoiceTotal: function(invoiceID) {
        var invoiceGR = new GlideRecord('your_invoice_table_name');
        if (invoiceGR.get(invoiceID)) {
            var totalAmount = 0;

            // Query Invoice Lines associated with the Invoice
            var lineGR = new GlideRecord('your_invoice_line_table_name');
            lineGR.addQuery('invoice', invoiceID);
            lineGR.query();

            // Calculate Total Amount from Invoice Lines
            while (lineGR.next()) {
                totalAmount += lineGR.getValue('line_amount');
            }

            // Update Total Amount on Invoice
            invoiceGR.setValue('total_amount', totalAmount);
            invoiceGR.update();
        }
    },

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Akshay Kanerkar]

@Maddysunil 
Appreciate your quick response, the script you provided is updating the previous record(already present on the form as Invoice INV001), not the one that will change as soon as I change the payor. (INV002). The Total amount of both Invoice (INV001 & INV 002) should be updated with the respective line amounts.