Asset Count Calculation Real Time

[dvelloriy]

Hello All,

We have a custom table "u_carton". During the Asset Receiving process, assets are loaded into the Carton.

There are different computers, peripherals associated to each carton.

Requirement is to create a custom field "Asset count" on carton table which will give the exact number of assets associated to a carton. (Carton appears on the asset record as Parent).

I am assuming we need to create a BR on asset table(alm_hardware) which will increment/decrement asset count value based on the data coming and going to/from asset table.

Can someone please advise and provide a script to configure this logic without much impact on performance?

Thanks in advance.

[SanjivMeher]

I would use a daily scripted scheduled job to query the u_carton table and the corresponding assets and update the count, which will not impact performance. For ex

var carton = new GlideRecord('u_carton');
carton.query();
while (carton.next())
{
// Get all associated assets
var ast = new GlideAggregate(alm_hardware);
ast.addQuery('parent',carton.getValue('sys_id'));
ast.addAggregate('COUNT');
ast.query();

if (ast.next())
{
carton.count = ast.getAggregate('COUNT');
}
carton.update();
}

[dvelloriy]

Hi Sanjiv, Thanks but the requirement is to make this run in real time. So if a new asset is created and assigned to a carton, it should increment the counter in real time.

Similarly when an asset is removed from carton, it should decrement.

We have around 3 M+ records in alm_asset table. Is there a way to effectively do this without impacting performance?

[surajchacherkar]

Hi @dvelloriy , you can use scheduled job (runs daily) which will not affect on performance.

    var cartonGr = new GlideRecord('u_carton');
    cartonGr.query();

    while (cartonGr.next()) {
        var assetCount = 0;

        // Query assets associated with the carton
        var assetGr = new GlideRecord('alm_hardware');
        assetGr.addQuery('parent', cartonGr.sys_id);
        assetGr.query();

        while (assetGr.next()) {
            assetCount++;
        }

        // Update Asset count field on carton record
        cartonGr.u_asset_count = assetCount;
        cartonGr.update();
    }

If my response helped you, please click on "Accept as solution" and mark it as helpful.

Thanks

Suraj!

[dvelloriy]

Hi Suraj, Thanks but the requirement is to make this run in real time. So if a new asset is created and assigned to a carton, it should increment the counter in real time.

Similarly when an asset is removed from carton, it should decrement.

We have around 3 M+ records in alm_asset table. Is there a way to effectively do this without impacting performance?