Storing Count of related records

[Susan Davidson]

I have three tables.

An AEM table - this references a device model

A device model table - with a related list showing the assets associated with the model

An asset table - shows model

I am trying to get a count of the total number of assets that are "active" (state is 3 or 1) for a Model

Then pass that count to the AEM table so i can run a scheduled job where if the count of active assets is 0 it will deactivate the AEM record

I created a calculated field on the Device model table

(function calculatedFieldValue(current) {
var AssetCount = 0;
var gr = new GlideAggregate ('alm_asset');
gr.addEncodedQuery('install_statusIN1,3');
gr.addQuery('model.ref_sn_ent_medical_device_model.sys_id',current.sys_id);
gr.addAggregate("COUNT");
gr.query();
while(gr.next()){
	AssetCount=gr.getAggregate("COUNT");
}

	return AssetCount;
})(current);

this works fine.. it shows the number of active assets on the device model table per model when i click into the record.

If i then "save" the record it will show in the list view. If i don't save the record it doesnt.. just displays on the form.

I have 7000 models so going through and saving each one daily isn't an option. 

I can display the calculated field with no issues on the aem table by dotwalking but it's blank unless i go into the model and hit save.

I tried also doing a business rule to get the count, save to scratchpad and on load load it to the form.. also loads fine.. but doesn't save/show in list view and therefore can't be queried without saving each record.

What am i missing - i've been staring at this too long and going in circles.

Is there a way to get a count of the related assets on a model and store that count in a field (without having to open the record for each model) so i can then dot walk it to aem and use it to do my scheduled job?

[HrishabhKumar · Accepted solution]

Hi @Susan Davidson ,

To achieve the goal of getting a count of the total number of active assets for a model and then passing that count to the AEM table without manually saving each record, you can use a combination of a scheduled job and a script to update the records automatically. Here are the steps:

Step 1: Create a field on the Device Model Table

Add a custom field to the Device Model table to store the count of active assets. Let's name it u_active_asset_count.

Step 2: Scheduled Script to Update the Active Asset Count

Create a scheduled job that will periodically update the u_active_asset_count field on the Device Model table.

1. Navigate to System Definition > Scheduled Jobs.
2. Click New to create a new scheduled job.
3. Use this script:

(function() {
    var deviceModelGR = new GlideRecord('your_device_model_table'); // Replace with your actual device model table name
    deviceModelGR.query();
    while (deviceModelGR.next()) {
        var activeAssetCount = 0;
        var assetGR = new GlideAggregate('alm_asset');
        assetGR.addEncodedQuery('install_statusIN1,3'); // Active asset states
        assetGR.addQuery('model', deviceModelGR.sys_id);
        assetGR.addAggregate('COUNT');
        assetGR.query();
        if (assetGR.next()) {
            activeAssetCount = assetGR.getAggregate('COUNT');
        }
        deviceModelGR.u_active_asset_count = activeAssetCount;
        deviceModelGR.update(); // Save the count to the Device Model record
    }
})();

Step 3: Use the Field on the AEM Table

Once the u_active_asset_count field is updated, you can dot-walk this field to the AEM table and use it in your scheduled job for deactivation.

1. On the AEM table, create a reference field to the Device Model table if you don't have it already.
2. Create a scheduled job to check the active asset count and deactivate the AEM record if the count is 0.

(function() {
    var aemGR = new GlideRecord('your_aem_table'); // Replace with your actual AEM table name
    aemGR.query();
    while (aemGR.next()) {
        if (aemGR.device_model.u_active_asset_count == 0) { // Assuming you have a reference to device model
            aemGR.active = false; // Assuming there's an 'active' field to deactivate the record
            aemGR.update();
        }
    }
})();

Step 4: Schedule Both Jobs

Ensure both jobs are scheduled to run at appropriate intervals. The job that updates the u_active_asset_count should run first, followed by the job that deactivates the AEM records based on this count.

Thanks,

Hope this helps.

If my response proves helpful please mark it helpful and accept it as solution to close this thread.

[Susan Davidson]

Thank you - i had tried a scheduled script but i must have had a bad line of code somewhere cause whereas mine didn't work yours did! I appreciate you.