List and Refence field Comparison

[daggupati]

Hi,

Is there way to compare List and Reference field values in GlideAggregate query it self.

We had list type field called “Products” in that we had multiple selections. Ex: Monitor, Computer, Docking station, Keyboard etc

We had Refence field called “Product” and it is single selection. Ex: Computer

Both fields are in different tables but we combine them using Database views. 

So in the script (GlideAggregate) we want to validate Product field value is part of Products field values. That way we will get count. I tried ga.addQuery(‘product’,’CONTAINS’,’products’); but it doesn’t work.

Any suggestions

[Astik Thombare]

Hi @daggupati ,

To achieve the comparison of a list field and a reference field within a GlideAggregate query in ServiceNow, you will need to use a bit of a workaround since GlideAggregate does not support direct comparisons involving list fields and reference fields in the way you're attempting.

Approach:

1. Preprocess the Data:

   • Since you need to check if the reference field value is part of the list field values, you might need to preprocess the data before running the glideAggregate  query.

2. Use a Script Include or Background Script:

   • Create a script include or use a background script to preprocess and collect the data.

3. Perform the Aggregation in Memory:

   • After collecting the data, perform the necessary aggregation in memory.

Here's an example script to illustrate this approach:

// Define the tables and fields
var productsTable = 'your_products_table';
var singleProductTable = 'your_single_product_table';
var productsField = 'products';  // List field
var productField = 'product';    // Reference field

// Create GlideRecord objects for both tables
var productsGR = new GlideRecord(productsTable);
productsGR.query();

var counts = {}; // To store the counts

while (productsGR.next()) {
    var products = productsGR.getValue(productsField).split(',');

    var singleProductGR = new GlideRecord(singleProductTable);
    singleProductGR.query();

    while (singleProductGR.next()) {
        var product = singleProductGR.getValue(productField);

        if (products.indexOf(product) !== -1) {
            if (!counts[product]) {
                counts[product] = 0;
            }
            counts[product]++;
        }
    }
}

// Output the counts
for (var key in counts) {
    gs.info(key + ': ' + counts[key]);
}

Explanation:

1. Initialization:

   • Define the tables and fields you will be working with.
   • Create GlideRecordobjects for both tables.

2. Query the List Field Table:

   • Query the table containing the list field.
   • Iterate through the results and split the list field values.

3. Query the Reference Field Table:

   • For each list field entry, query the table containing the reference field.
   • Check if the reference field value is part of the list field values.

4. Aggregation:

   • Maintain a count of matching reference field values in a dictionary (counts).

5. Output:

   • Output the counts using gs.info()

This approach leverages GlideRecord to query the necessary records and then processes the data in memory to achieve the desired aggregation. The counts are stored in a dictionary which you can use to output or further manipulate as needed.

Note:

• Performance Consideration: Depending on the number of records, this approach might be slow. Consider optimizing your queries or processing in batches.
• Script Includes: For production use, you might want to move this logic into a Script Include for better reusability and maintenance.

Please mark my answer as correct if it helps to resolve your issue 

Thanks,

Astik 

[daggupati]

Hi Astik,

Thank you for the response. We have millions of the records and it will take more time to complete and performance as well even though if we doing batches.

[SN_Learn]

Hi @daggupati ,

I assume that products variable is storing the reference ids

ga.addQuery('product', 'IN', products);

Also, provide the whole code to check further

Please Mark My Response as Correct/Helpful based on Impact

[daggupati]

Hi Sn_Lean,

Thank you for the response. I tried IN as well it is not working.