Can you create a breakdown off of a free text field?

[John VanBruggen]

Here is the scenario.   My client has a field in the locations column named 'District' which is populated with a district number.
This is just a standard text field, not a reference field (I wish it was).  

Is there a way to create a breakdown source for this free text field?

[pvet · Accepted solution]

Hi John,

This is certainly possible using a manual breakdown and some scripting.

First off you'll need to create a Manual Breakdown. You can populate the breakdown by running a scheduled job with "automatically run a script of your choosing" in which you'll query the cmn_location table (district col) and add each unique value (COUNT) to the Manual Breakdown. (select to run it each day just before the scores collection)

Next up you'll need to create a breakdown source that references the manual breakdown. The facts table should be [pa_manual_breakdowns] with the condition set to [Breakdown] [is] [<name of your manual breakdown>]  

Then you'll to create a PA Script that will relate each record of the record set of the indicator, to one of the breakdown elements.

For example, using the districts via caller from the incident table:

Source on [Incident], field: [Caller.Location.<district>]

var breakdown = '<sys id of manual breakdown>'

var value = current.caller_id.location.<district col name>.getDisplayValue();

var sysID = '';

if (value && value != '') {

var gr = new GlideRecord('pa_manual_breakdowns');

gr.addQuery('breakdown', breakdown);

gr.addQuery('value', value);

gr.query();

if (gr.next()) {

sysID = gr.getValue('sys_id');

}

}

sysID || '';

Now you'll need to create the breakdown. It's breakdown's source is the breakdown source you created earlier. After you save the breakdown you create the breakdown mapping with facts table on 'incident' and check 'scripted' > script referencing to the script above.

Last step is adding (relating) the indicators of your choice to the breakdown and running the (historic) scores collection.

Cheers,

Pim Vet

[John VanBruggen]

Awesome great.   Thanks to both of you guys.   You just opened a huge window for me.   This is actually a part of HR reporting.The client has two fields, Requested For and Third Party Requested For.     The location of the case is based on who the case is Requested for so it is going to be one of those two fields.   Using scripts, I can now get 1 district location breakdown based on the field that is populated.   To do this I obviously had to modify your script with a few if statements to validate which field we want to pull.  

Seriously, this is awesome.   THANKS!

[ZiadZ]

Here is a script i used to insert values from Alert Source to a breakdown i created:

// Query unique 'source' values from the em_alert table
var sources = [];
var alertGR = new GlideAggregate('em_alert');
alertGR.addAggregate('COUNT', 'source');
alertGR.groupBy('source');
alertGR.query();

while (alertGR.next()) {
    var source = alertGR.getValue('source');
    if (source) { // Ensure the source value is not null or empty
        sources.push(source);
    }
}

// Add unique sources to the manual breakdown
var breakdownSysId = '123456789'; //INSERT SYSID OF YOUR BREAKDOWN
var insertedCount = 0;

for (var i = 0; i < sources.length; i++) {
    var sourceValue = sources[i];
    
    // Check if the entry already exists
    var breakdownEntryGR = new GlideRecord('pa_manual_breakdowns');
    breakdownEntryGR.addQuery('breakdown', breakdownSysId);
    breakdownEntryGR.addQuery('value', sourceValue);
    breakdownEntryGR.query();

    if (!breakdownEntryGR.hasNext()) {
        // Insert a new entry
        var newEntryGR = new GlideRecord('pa_manual_breakdowns');
        newEntryGR.initialize();
        newEntryGR.breakdown = breakdownSysId;
        newEntryGR.value = sourceValue;
        newEntryGR.insert();
        insertedCount++;
    }
}

// Log the results
gs.print('Scheduled Job: Added ' + insertedCount + ' new entries to manual breakdown: ' + breakdownSysId);

[srinivasthelu]

Hi Jhon,

I do not think so. Instead ,you may want to implement onchange validation logic.

Thanks

Srinivas