Calculating the sum

[Sarah Powell]

Hello all,

I am working on a new application for a department outside of IT. One of the requirements is that I add the "Amount" field on the child ticket (Table B) to the "Amount" field on the parent ticket (Table A). There can be more than one child ticket but only one parent. My logic is as follows:

Run on:   Table B

If "Parent" field is not empty & "Amount" field is not empty

Add "Amount" value from Table B to "Amount" value on Table A

Update Table A "Amount" field with the new sum value

I have tried writing a business rule to accomplish this but I am not having much luck. I was able to use the following script to add two fields from Table B and display the information in a InfoMessage (see image) but not update the field with the sum value.

function onAfter(current, previous) {

                              var amount1 = current.dec_amt_1;

                              var amount2 = current.dec_amt_2;

                              var amount3 = current.dec_amt_3;

                              gs.addInfoMessage('Amount 1: ' + amount1);

                              gs.addInfoMessage('Amount 2: ' + amount2);

                              gs.addInfoMessage('Amount 3: ' + amount3);

                              if (amount2 != ''){

                                                              var newAmount = amount1 + amount2;

                                                              gs.addInfoMessage('Amount is: ' + newAmount);

                              }

}

When I try working with both tables, nothing happens at all.

Can anyone provide some guidance on how to accomplish this?

Thanks in advance,
Sarah

[joshua_bice]

You'll need to add something like this to your business rule:

var gr = new GlideRecord('YOUR TABLE');

gr.get(current.parent);

//Get your amount for both current and previous

if(current.parent == previous.parent){

        gr.amount = gr.amount - //previous amount + current amount;

}

else{

        var oldParent = new GlideRecord('YOUR TABLE');

        oldParent.get(previous.parent);

        oldParent.amount -= //previous amount;
        gr.amount += //current amount;

        oldParent.update();

}

gr.update();

[Deepak Ingale1]

Hello Sarah,

If I understand you correctly, you want the sum of all child fields to the parent field?

if it is so, then do the GlideRecord operation on child table

var gr = new GlideRecord('parent table');

gr.addQuery('reference field of your parent',current.sys_id);

gr.query();

while(gr.next());

{

      var ParentFieldCurrentValue = parseInt(gr.parentField);

      var ParentFieldNewValue = ParentFieldCurrentValue + parseInt(current.FieldName);

  gr.parentField = ParentFieldNewValue;

gr.update();

}

[Sarah Powell]

Thanks for the quick response!!! I tried the script you provided and added a message so I could tell if the rule was executing...the message displays but the field is not updated.

Parent table:   x_9658_corp_sec_cases

Parent field:   amount / Type:   decimal

Child table:   x_9658_corp_sec_fraud_incident

Child field:   dec_amt_1 / Type: decimal

Run on:   x_9658_corp_sec_fraud_incident

Script:

function onAfter(current, previous) {

  var gr = new GlideRecord('x_9658_corp_sec_cases');

  gr.addQuery('amount', current.sys_id);

  gr.query();

  while(gr.next())

  {

  var ParentFieldCurrentValue = parseInt(gr.amount);

  var ParentFieldNewValue = ParentFieldCurrentValue + parseInt(current.dec_amt_1);

  gr.amount = ParentFieldNewValue;

  gr.update();

  }

}

Am I missing something?

[Deepak Ingale1]

can you try with code submitted by Joshua

I have done wrong at line...

gr.addQuery('amount', current.sys_id);

try his code and check