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]

Try this approach

1. Create the Dashboard Navigate to Performance Analytics → Dashboards → New

2. Add Widget 1 — LR List

• Indicator: Your existing indicator on sn_lg_ops_request
• Breakdown: By LR number
• Visualization: Bar or List
• Enable "Use as interactive filter" on this widget

3. Add Widget 2 — Selected Options

• Indicator: Same indicator
• Breakdown: Your options breakdown (from the M2M table)
• Visualization: Bar or Pie

4. Link Them

• Both widgets must use the same indicator
• On Widget 1, right-click the widget header → Configure → check "Interactive filter"
• This tells Widget 2 to react when a user clicks an LR in Widget 1

[amrutapotda]

“How can we implement this for two list collector fields? Currently, it is done only for a single list collector field using both PA and a custom table.

[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

[amrutapotda]

@Naveen20  This worked, but have a question, while adding the report on pa dashboard, it is looking like breakdown 1 ,breakdown 2 and then clicking on respective breakdown we are getting the data of that particular list field, this is how ideally it should right?

[Naveen20 · Accepted solution]

Yes, that's exactly how it should work.
To summarize the expected flow:
Each breakdown widget represents one list collector field. Clicking into a specific option within that breakdown drills down to the LR records associated with it. That's the correct and expected behavior.

If you also want the reverse — click an LR first, then see its options — just add a third widget with the LR breakdown and enable interactive filtering on it, as we discussed earlier. But as a standalone setup, what you have now is the standard pattern for reporting multi-value fields separately in PA.