Calculation: Asset Count in a Carton

[dvelloriy]

Hi All,

We have a receiving process for assets in SN. When assets are created, they are usually put in a carton.

We have custom carton table which has the related carton attributes and associated hardware assets related list.

On asset side, we have a parent field which is related carton record.

Requirement is to create a new field "Asset Count" on carton table and calculate based on the number of assets associated to a carton.

I have below script, using it in async BR on insert and update of asset (alm_asset)

Condition: when parent changes

Its not working in below scenarios:

1. when we are updating Parent on asset record to blank value

2. when Changing parent to new carton, it's not updating the asset count on old carton(it should decrement its value). It's just updating new carton.

var assetCount = 0;

//Get all associated assets

var astGr = new GlideRecord('alm_asset');

astGr.addQuery('parent.sys_id',current.parent);

astGr.query();

//gs.info("Records in table2: " + astGr.getRowCount());

while (astGr.next())

{

assetCount++;

}

//gs.info("The value of AssetCount is"+assetCount);

var cartonGr = new GlideRecord('x_hamp_carton');

cartonGr.addQuery('sys_id',current.parent);

cartonGr.query();

//gs.info("Records in table: " + palletGr.getRowCount());

while (cartonGr.next())

{

    //Update Asset Count on pallet record

cartonGr.asset_count = assetCount;

cartonGr.update();

}

[AshishKM]

Hi @dvelloriy 

Based on given code, sharing my understanding, the "x_hamp_carton" table records are considered as parent and alm_asset table records has x_hamp_carton record as parent. 

and x_hamp_carton table column asset_count which is count of alm_assets record per parent record, so at parent level we know how many assets are mapped with this carton record. 

I think the reason is BR when to run, try with after update or insert instead of async.

Try with below updated code and share the result.

var assetCount = 0;
//Get all associated assets
var astGr = new GlideRecord('alm_asset');
astGr.addQuery('parent.sys_id',current.parent);
astGr.query();
//gs.info("Records in table2: " + astGr.getRowCount());

// use the getRowCount for record count 
assetCount =astGr.getRowCount(); 
//gs.info("The value of AssetCount is"+assetCount);
var cartonGr = new GlideRecord('x_hamp_carton');
cartonGr.addQuery('sys_id',current.parent);
cartonGr.query();
//gs.info("Records in table: " + palletGr.getRowCount());
// use if becuase there will be only one record with matched sys_id
if(cartonGr.next()){
    //Update Asset Count on pallet record
	cartonGr.asset_count = assetCount;
	cartonGr.update();
}

[dvelloriy]

Hi Ashish, Yes but this script will handle just current Parent (carton) record.

If i am changing the Parent value on Asset record from Lets say A to B, then updates should happen on both cartons A and B. 

Asset count on B should be incremented by 1.

Asset count on A should be decremented by 1.

Also if i am changing the parent from B to blank value, then Asset count on B should be decremented by 1.

How can we handle all these scenrios? Please advise.

[AshishKM]

Try with below updated code .. add some gs.log and share the output .. 

var assetCount = 0;
//Get all associated assets
var astGr = new GlideRecord('alm_asset');
astGr.addQuery('parent.sys_id',current.parent);
astGr.query();
//gs.info("Records in table2: " + astGr.getRowCount());

// use the getRowCount for record count 
assetCount =astGr.getRowCount(); 
//gs.info("The value of AssetCount is"+assetCount);
var cartonGr = new GlideRecord('x_hamp_carton');
cartonGr.addQuery('sys_id',current.parent);
cartonGr.query();
//gs.info("Records in table: " + palletGr.getRowCount());
// use if becuase there will be only one record with matched sys_id
if(cartonGr.next()){
    //Update Asset Count on pallet record
	cartonGr.asset_count = assetCount;
	cartonGr.update();
}
// check if asset record has parent already mapped
if(previous.parentISNOTEMPTY){
	var astParntGR = new GlideRecord('alm_asset');
   	astParntGR.addQuery('parent.sys_id',previous.parent);
	astParntGR.query();
	// total count before updating the parent column in alm_asset 
	var preCount = astParntGR.getRowCount();
	var preCartonGr = new GlideRecord('x_hamp_carton');
		preCartonGr.addQuery('sys_id',previous.parent);
		preCartonGr.query();
		if(preCartonGr.ext()){
			preCartonGr.asset_count =preCartonGr.asset_count -1;
			preCartonGr.update();
		}
	
}

[Community Alums · Accepted solution]

Hi @dvelloriy ,

The below script will handle all your scenarios, please validate-

(function executeRule(current, previous /*null when async*/) {
    function updateAssetCount(cartonId) {
        var assetCount = 0;

        var astGr = new GlideRecord('alm_asset');
        astGr.addQuery('parent', cartonId);
        astGr.query();

        assetCount = astGr.getRowCount();

        var cartonGr = new GlideRecord('x_hamp_carton');
        if (cartonGr.get(cartonId)) {
            cartonGr.asset_count = assetCount;
            cartonGr.update();
        }
    }

    // Decrement count on previous parent carton if exists
    if (previous.parent) {
        updateAssetCount(previous.parent);
    }

    // Increment count on current parent carton if exists and is not blank
    if (current.parent) {
        updateAssetCount(current.parent);
    }

})(current, previous);

If my response has resolved your query, please consider giving it a thumbs up ‌‌ and marking it as the correct answer‌‌!

Thanks & Regards,

Sanjay Kumar