Multi Select field should be report in a such way that each option selected is reported separately

[amrutapotda]

• @Adam Stout , I followed the solution you provided using Performance Analytics, as it was recommended not to use a custom table.
• Step 1: I created an Indicator Source on the Legal Request table, since we have a record producer on this table.
  • The facts table selected was Legal Request on indiactor source.
  • This catalog item includes a List Collector variable, and we want to report those values separately.
• Step 2: I created an Indicator and selected the above-created Indicator Source.
• Step 3: I created an automated breakdown and tagged it to the indicator.
• Step 4: In the breakdown:
  • I tagged a Breakdown Source that I created.
  • Selected the List Collector table as the facts table in the breakdown source.
  • Selected sys_id as the field in that.
  • Added a condition for the specific question from which the options need to be selected.
• Step 5: I created a Breakdown Mapping on the Legal Request table and added a script (as shown in the attached screenshot).
• After completing all the setup, I ran the job:
  • There are no errors during execution, it shows as collected.
  • However, in the Indicator Scores, all values appear in red.
• The setup works correctly for a single-value selection, but it does not work for multiple values.
• Could you please guide me on:
  • Where I might be going wrong, or
  • What needs to be changed to make it work for multi-value list collectors?
  • As shown in the screen shot, the commented code is for multiple value, and the uncommented code is for single value.

[Naveen20 · Accepted solution]

 

Red scores in PA mean the breakdown mapping returned values that couldn't be resolved against the Breakdown Source. This usually comes down to one of these issues:

1. Breakdown Source Facts Table Mismatch

You mentioned you "selected the List Collector table as the facts table." The facts table in the Breakdown Source should be the target table that the List Collector references (e.g., cmn_department, sc_cat_item, whatever table the options come from) — not the question_answer table or item_option_new table. The sys_ids your script returns must exist as records in that target table.

2. Values May Have Whitespace

question_answer.value for list collectors stores comma-separated sys_ids, but sometimes with spaces. The split won't trim them:

javascript

// Problem: " sys_id_here" (leading space) won't match
var arr = value.split(',');

// Fix: trim each value
var arr = value.toString().split(',');
for (var i = 0; i < arr.length; i++) {
    result.push(arr[i].trim());
}

3. The answer Array Must Contain sys_ids From the Breakdown Source Table

Verify that qa.value actually stores sys_ids and not display values. For list collectors on record producers, the stored value is usually sys_ids — but confirm by checking a question_answer record directly. If it stores display values, you'll need to look up the sys_id.

 

Try this fix

javascript

var result = [];
var qa = new GlideRecord('question_answer');
qa.addQuery('table_sys_id', current.sys_id);
qa.addQuery('table_name', 'sn_lg_ops_request');
qa.query();

while (qa.next()) {
    if (qa.question.name == 'your_variable_name') {
        var value = qa.getValue('value');
        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    result.push(trimmed);
                }
            }
        }
        break; // found the question, no need to continue
    }
}

answer = result;

[Naveen20 · Accepted solution]

Try these steps  
1. Create the table u_lr_option_mapping with three fields:

• u_legal_request → Reference to sn_lg_ops_request
• u_selected_option → Reference to your options/target table
• u_question_name → String (helps if you add more list collectors later)

2. Business Rule on sn_lg_ops_request — After Insert (and After Update if selections can change):

javascript

(function executeRule(current, previous) {
    // On update, clear old mappings first
    if (current.operation() == 'update') {
        var del = new GlideRecord('u_lr_option_mapping');
        del.addQuery('u_legal_request', current.sys_id);
        del.addQuery('u_question_name', 'your_variable_name');
        del.deleteMultiple();
    }

    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', current.sys_id);
    qa.addQuery('table_name', 'sn_lg_ops_request');
    qa.addQuery('question.name', 'your_variable_name');
    qa.query();

    if (qa.next()) {
        var value = qa.getValue('value');
        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    var mapping = new GlideRecord('u_lr_option_mapping');
                    mapping.initialize();
                    mapping.u_legal_request = current.sys_id;
                    mapping.u_selected_option = trimmed;
                    mapping.u_question_name = 'your_variable_name';
                    mapping.insert();
                }
            }
        }
    }
})(current, previous);

3. Backfill existing records — run once in background scripts:

javascript

var gr = new GlideRecord('sn_lg_ops_request');
gr.query();
while (gr.next()) {
    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', gr.sys_id);
    qa.addQuery('table_name', 'sn_lg_ops_request');
    qa.addQuery('question.name', 'your_variable_name');
    qa.query();
    if (qa.next()) {
        var value = qa.getValue('value');
        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    var m = new GlideRecord('u_lr_option_mapping');
                    m.initialize();
                    m.u_legal_request = gr.sys_id;
                    m.u_selected_option = trimmed;
                    m.u_question_name = 'your_variable_name';
                    m.insert();
                }
            }
        }
    }
}

4. Reporting — Build a single report on u_lr_option_mapping:

• Group by u_legal_request to see each LR
• Clicking an LR row drills into its selected options via the reference field
• You can also add u_lr_option_mapping as a Related List on the LR form for on-record visibility

[Naveen20 · Accepted solution]

Do these changes

1. Update the BR

(function executeRule(current, previous) {
    // Define both list collector variable names
    var variables = ['your_first_variable', 'your_second_variable'];

    // On update, clear old mappings
    if (current.operation() == 'update') {
        var del = new GlideRecord('u_lr_option_mapping');
        del.addQuery('u_legal_request', current.sys_id);
        del.deleteMultiple();
    }

    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', current.sys_id);
    qa.addQuery('table_name', 'sn_lg_ops_request');
    qa.addQuery('question.name', 'IN', variables.join(','));
    qa.query();

    while (qa.next()) {
        var value = qa.getValue('value');
        var qName = qa.question.name.toString();

        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    var mapping = new GlideRecord('u_lr_option_mapping');
                    mapping.initialize();
                    mapping.u_legal_request = current.sys_id;
                    mapping.u_selected_option = trimmed;
                    mapping.u_question_name = qName;
                    mapping.insert();
                }
            }
        }
    }
})(current, previous);

2. Update the Backfill Script

Same logic — just add the IN query and loop:

var variables = ['your_first_variable', 'your_second_variable'];

var gr = new GlideRecord('sn_lg_ops_request');
gr.query();
while (gr.next()) {
    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', gr.sys_id);
    qa.addQuery('table_name', 'sn_lg_ops_request');
    qa.addQuery('question.name', 'IN', variables.join(','));
    qa.query();

    while (qa.next()) {
        var value = qa.getValue('value');
        var qName = qa.question.name.toString();

        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    var m = new GlideRecord('u_lr_option_mapping');
                    m.initialize();
                    m.u_legal_request = gr.sys_id;
                    m.u_selected_option = trimmed;
                    m.u_question_name = qName;
                    m.insert();
                }
            }
        }
    }
}

3. PA Breakdown Mapping Script

Create two separate breakdowns — one per list collector. Each breakdown mapping script filters by u_question_name:

Breakdown 1 — First Variable:

var result = [];
var m = new GlideRecord('u_lr_option_mapping');
m.addQuery('u_legal_request', current.sys_id);
m.addQuery('u_question_name', 'your_first_variable');
m.query();
while (m.next()) {
    result.push(m.getValue('u_selected_option'));
}
answer = result;

Breakdown 2 — Second Variable:

var result = [];
var m = new GlideRecord('u_lr_option_mapping');
m.addQuery('u_legal_request', current.sys_id);
m.addQuery('u_question_name', 'your_second_variable');
m.query();
while (m.next()) {
    result.push(m.getValue('u_selected_option'));
}
answer = result;

Dashboard to be setup

[Naveen20 · Accepted solution]

 

Red scores in PA mean the breakdown mapping returned values that couldn't be resolved against the Breakdown Source. This usually comes down to one of these issues:

1. Breakdown Source Facts Table Mismatch

You mentioned you "selected the List Collector table as the facts table." The facts table in the Breakdown Source should be the target table that the List Collector references (e.g., cmn_department, sc_cat_item, whatever table the options come from) — not the question_answer table or item_option_new table. The sys_ids your script returns must exist as records in that target table.

2. Values May Have Whitespace

question_answer.value for list collectors stores comma-separated sys_ids, but sometimes with spaces. The split won't trim them:

javascript

// Problem: " sys_id_here" (leading space) won't match
var arr = value.split(',');

// Fix: trim each value
var arr = value.toString().split(',');
for (var i = 0; i < arr.length; i++) {
    result.push(arr[i].trim());
}

3. The answer Array Must Contain sys_ids From the Breakdown Source Table

Verify that qa.value actually stores sys_ids and not display values. For list collectors on record producers, the stored value is usually sys_ids — but confirm by checking a question_answer record directly. If it stores display values, you'll need to look up the sys_id.

 

Try this fix

javascript

var result = [];
var qa = new GlideRecord('question_answer');
qa.addQuery('table_sys_id', current.sys_id);
qa.addQuery('table_name', 'sn_lg_ops_request');
qa.query();

while (qa.next()) {
    if (qa.question.name == 'your_variable_name') {
        var value = qa.getValue('value');
        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    result.push(trimmed);
                }
            }
        }
        break; // found the question, no need to continue
    }
}

answer = result;

[amrutapotda]

@Naveen20  Thank you, it worked. 

Also need help on this scenario also, I am retrieving values from the question_answer table for a specific question. However, when a catalog request is submitted from the ESC portal, an LR request is created. I need to include this LR information in my report as well.

Specifically, I want to display the LR number, and when the LR number is clicked, it should show the options selected from the list collector variable. At the moment, I can generate a report showing this data separately, but I need to establish a relationship between the LR and the selected options.

In short, I want to show which options were selected for each LR, and I am looking for guidance on how to link these two reports or tables,whether through any dependency, or another approach.

[Naveen20 · Accepted solution]

Try these steps  
1. Create the table u_lr_option_mapping with three fields:

• u_legal_request → Reference to sn_lg_ops_request
• u_selected_option → Reference to your options/target table
• u_question_name → String (helps if you add more list collectors later)

2. Business Rule on sn_lg_ops_request — After Insert (and After Update if selections can change):

javascript

(function executeRule(current, previous) {
    // On update, clear old mappings first
    if (current.operation() == 'update') {
        var del = new GlideRecord('u_lr_option_mapping');
        del.addQuery('u_legal_request', current.sys_id);
        del.addQuery('u_question_name', 'your_variable_name');
        del.deleteMultiple();
    }

    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', current.sys_id);
    qa.addQuery('table_name', 'sn_lg_ops_request');
    qa.addQuery('question.name', 'your_variable_name');
    qa.query();

    if (qa.next()) {
        var value = qa.getValue('value');
        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    var mapping = new GlideRecord('u_lr_option_mapping');
                    mapping.initialize();
                    mapping.u_legal_request = current.sys_id;
                    mapping.u_selected_option = trimmed;
                    mapping.u_question_name = 'your_variable_name';
                    mapping.insert();
                }
            }
        }
    }
})(current, previous);

3. Backfill existing records — run once in background scripts:

javascript

var gr = new GlideRecord('sn_lg_ops_request');
gr.query();
while (gr.next()) {
    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', gr.sys_id);
    qa.addQuery('table_name', 'sn_lg_ops_request');
    qa.addQuery('question.name', 'your_variable_name');
    qa.query();
    if (qa.next()) {
        var value = qa.getValue('value');
        if (value) {
            var arr = value.split(',');
            for (var i = 0; i < arr.length; i++) {
                var trimmed = arr[i].trim();
                if (trimmed) {
                    var m = new GlideRecord('u_lr_option_mapping');
                    m.initialize();
                    m.u_legal_request = gr.sys_id;
                    m.u_selected_option = trimmed;
                    m.u_question_name = 'your_variable_name';
                    m.insert();
                }
            }
        }
    }
}

4. Reporting — Build a single report on u_lr_option_mapping:

• Group by u_legal_request to see each LR
• Clicking an LR row drills into its selected options via the reference field
• You can also add u_lr_option_mapping as a Related List on the LR form for on-record visibility

[amrutapotda]

I need to display two reports together in a single view—for example, showing the Legal Request number with the corresponding options, or vice versa.