How to Filter Spare Parts by Consumable Location in Work Order Tasks?

[yana7]

ServiceNow:

• Table: Work Order Task = wm_task

• Table: Asset = alm_asset

• Table: Spare Parts = cmdb_model

Scenario:

On the "Work Order Task" table, I have a field called "Asset" that references the "alm_asset" table. The "alm_asset" table has a field called "Location" that references the "cmn_location" table.

Additionally, I have a field called "Spare Parts" on the "Work Order Task" table that references the "cmdb_model" table. I also have a field called "Location" on the "Work Order Task" table that references the "cmn_location" table.

Requirement:

I want to filter the "Spare Parts" field so that it only displays spare parts whose Model Category is "Consumables" and whose location matches the location found in the "alm_consumable" table.

javascript: new ReferenceQualifierAdvance().setFilter(function(current, source) {
// Get the location from the alm_consumable table
var consumableGR = new GlideRecord('alm_consumable');
consumableGR.query(); // Assuming you want to consider all consumables
// If you have a way to identify the relevant consumable, add a filter here

// Check if any consumable has a matching location
var locationMatch = false;
while (consumableGR.next()) {
if (current.location == consumableGR.location) {
locationMatch = true;
break; // Exit the loop once a match is found
}
}

// If no location match, filter out the spare part
if (!locationMatch) {
return false;
}

// Check if the spare part is a consumable (same as before)
var sparePartGR = new GlideRecord('cmdb_model');
sparePartGR.get(source.sys_id);

var modelCategoryGR = new GlideRecord('cmdb_model_category');
if (modelCategoryGR.get(sparePartGR.model_category)) {
return modelCategoryGR.name == 'Consumables';
} else {
return false;
}
}).getReference();

This is my form :

[Maddysunil]

@yana7 

I have made few corrections also apply few logs to debug

javascript: new ReferenceQualifierAdvance().setFilter(function(current, source) {
    // Get the location from the alm_consumable table
    var consumableGR = new GlideRecord('alm_consumable');
    consumableGR.addQuery('location', current.location); // Filter consumables by location
    consumableGR.query(); // Execute the query

    // If no matching consumable found, filter out the spare part
    if (!consumableGR.hasNext()) {
        return false;
    }

    // Get the spare part record
    var sparePartGR = new GlideRecord('cmdb_model');
    sparePartGR.get(source.sys_id);

    // Check if the spare part is a consumable
    var modelCategoryGR = new GlideRecord('cmdb_model_category');
    if (modelCategoryGR.get(sparePartGR.model_category)) {
        return modelCategoryGR.name == 'Consumables';
    } else {
        return false;
    }
}).getReference();

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[yana7]

The spare parts list hasn't been filtered yet

@Maddysunil 

Dictionary Field Spareparts

[Maddysunil]

@yana7 

Please apply some logs to debug the issue

[yana7]

@Maddysunil 

Evaluator: com.glide.script.RhinoEcmaError: "javascript" is not defined. script : Line(1) column(0) ==> 1: javascript: new ReferenceQualifierAdvance().setFilter(function(current, source) { 2: // Get the location from the alm_consumable table 3: var consumableGR = new GlideRecord('alm_consumable'); 4: consumableGR.addQuery('location', current.location); // Filter consumables by location