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

[Tanaji Patil]

Hi Sarah,

Here is how is achieved your requirement.

I wrote 2 BR on the ' x_9658_corp_sec_fraud_incident' table. Tried to merge into a single but unfortunately didnt succeeded.

1. On Insert and update

When - after Insert or Update

Script -

function onAfter(current, previous) {

var current_parent = current.parent;

var previous_parent = previous.parent;

var sum = 0, gr, gr1, parent = current_parent;

if((current.operation() == 'insert' && current_parent.nil()) || (current.operation() == 'update' && current_parent.nil() && previous_parent.nil()))

  return;

else if(current.operation() == 'update' && (current_parent.nil() || previous_parent.nil()) || (current_parent == previous_parent)){

  if(current_parent.nil())

    parent = previous_parent;

}

else if(current.operation() == 'update' && !current_parent.nil() && !previous_parent.nil() && current_parent != previous_parent){

  gr = GlideRecord('x_9658_corp_sec_fraud_incident');

  gr.addQuery('parent',previous_parent);

  gr.query();

  while(gr.next())

    sum = sum + gr.dec_amt_1;

  gr1 = GlideRecord('x_9658_corp_sec_cases');

  gr1.get(previous_parent);

  gr1.amount = sum;

  gr1.update();

}

sum = 0;

gr = GlideRecord('x_9658_corp_sec_fraud_incident');

gr.addQuery('parent',parent);

gr.query();

while(gr.next())

  sum = sum + gr.dec_amt_1;

gr1 = GlideRecord('x_9658_corp_sec_cases');

gr1.get(parent);

gr1.amount = sum;

gr1.update();

}

2. On delete

When - after Delete

Script -

function onAfter(current, previous) {

var current_parent = current.parent;

  var sum = 0, gr;

gr = GlideRecord('x_9658_corp_sec_cases');

gr.get(current_parent);

sum = gr.amount - current.dec_amt_1;

gr.amount = sum;

gr.update();

}

Please make the 'amount' field readonly.

Hope this solves your problem.

Thanks,

Tanaji

[Sarah Powell]

Tanaji,

Thanks for the help. I've created the two business rules with your code above. I added a message to each one so that I could verify which one executed. The 'after insert or update' is executing when I change the dec_amt_1 value on the x_9658_corp_sec_fraud_incident table but all I see is the message. The amount field on the x_9658_corp_sec_cases table is not being populated with any data. I have another project I need to work on but hopefully I can get back to this this afternoon.

Thanks again,

Sarah

[Tanaji Patil]

Hi Sarah,

I guess the parent field 'amount' and child field 'dec_amt_1' are custom fields and they are not OOB. I dont see u_ or x_ prefixed to their names.

Please can you verify the names again. I guess the names might be 'u_amount'/'x_amount' and 'u_dec_amt_1'/'x_dec_amt_1'.

If these are the field names please try replacing 'amount' and 'dec_amt_1' with the appropriate field names.

Thanks,

Tanaji

[Tanaji Patil]

Hi Sarah,

Did this worked for you? If yes please can you mark the correct answer.

Thanks,

Tanaji

[Sarah Powell]

Tanaji,

Sorry for the delay. Yes, I tried your solution but I have not been able to get it to work yet. I had another project come up that needed my attention so I had to stop working on this. I plan to get back to it early next week. I will update you with my findings then.

Thanks,
Sarah