Change Parent Record Field values, based on multiple related record field values.

[Brian147]

In my scoped application, I have a table (call it A) with a related list referencing another table (call it B). Table A has a field that should hold a total currency value. However, table B is storing the currency value that actually corresponds to the field on table A.

So, If i were to create two related table B records for table A, and each of them had a currency value of $2, I need to be able to throw the sum of those two values into A, so the A table would display $4.

Attached will be pictures better representing the problem, but the main issue is I can't find a way to successfully map my related record information over to my parent table, in this way.

Total Capital Commitment -> Table A Value

Capitalized Amount -> Related Record / Table B Value

[chrisperry]

Hi there,

You could add an after insert/update business rule on the child table to calculate the value and set it accordingly on the parent record. It's hard to give you exact code because I don't know your table names or field names, but something like the below should work:

var glideAgg = new GlideAggregate('child_table_name');
glideAgg.addQuery('parent_field_name', current.getValue('parent_field_name'));
glideAgg.addAggregate('SUM', 'capitalized_amount');
glideAgg.query();
if (glideAgg.next()) {
    gs.info(glideAgg.getAggregate('SUM', 'capitalized_amount'));
    var parentGr = current.parent_field_name.getRefRecord();
    parentGr.setValue('total_capital_commitment', glideAgg.getAggregate('SUM', 'capitalized_amount'));
    parentGr.update();
}

If this answer is helpful please mark correct and helpful!

Regards,

Christopher Perry

[Brian147]

The above answer may very well work too, but it didn't for me. I used the following code, and it worked successfully.

var updatedTotal = 0.00;
	
	var grLI = new GlideRecord('x_g_ngr_discretion_ccfa_line_items');
	grLI.addQuery('ccfa', current.ccfa.sys_id + '');
	grLI.query();
	while(grLI.next()){
		updatedTotal += parseInt(grLI.u_currency_3);
	}
	
	function formatNumber (num) {
		return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, "$1,");
	}
	
	
	var grC = new GlideRecord('x_g_ngr_discretion_ccfa');
	grC.addQuery('sys_id', current.ccfa + '');
	grC.query();
	if(grC.next()){
		grC.u_currency_6 = formatNumber(updatedTotal);
		grC.u_currency_1 = formatNumber(updatedTotal);
		grC.update();
	}

Here, grLI is referencing my child table, and grC is my parent table. Hope this helps if anyone comes along a similar issue.