Help with Code

[dvelloriy]

Hello all,

I need some help in building a fix script/ scheduled job script for below requirement:

I have created a report on asmt metric table to capture the experience feedback of our newly launched portal. This report has 2 critical columns: Portal page & Portal page params.

Portal page: target where the feedback was recorded. it has values like kb_article, ticket, sc_cat_item etc.

Portal page params - all required portal page parameters in json.

Refer screenshot below.

Requirement: Parse portal page params, extract the target sys_id or sysparm_article and display the hyperlinked target record in a new column. So, if portal page is kb_article and portal page param has KB0010149 then we need to show the KB article number so management can click there and check the article. Similarly, if it's a cat item, we need to reference the cat item sys_id and show the exact cat item name not the sys_id.

This is needed for leadership and management teams to check the feedback they are getting and action accordingly.

Please advise.

[Sheldon Swift · Accepted solution]

I'd probably try to use Document ID (if you're not familiar, see: Document ID field - Did You Know) set by a business rule on insert/update. Just make sure you set the conditions (e.g. Metric = Experience feedback).

Based on the information you've presented, the business rule might look something like this:

(function executeRule(current, previous /*null when async*/) {

    // Get the portal page params
    var params = current.getValue('params');

    // Ensure it's not null or empty before parsing
    if (params) {
        try {
            // Try to parse the string as JSON
            var parsedParams = JSON.parse(params);

            // Extract the sys_id, id, and sysparm_article safely
            var sys_id = parsedParams.sys_id;
            var table;
            var id = parsedParams.id;
            var articleNumber = parsedParams.sysparm_article;

            // Case 1: If 'id' is 'ticket', use 'table' field
            if (id === 'ticket') {
                table = parsedParams.table; // 'table' contains the actual table name
            }
            // Case 2: If 'id' is not 'ticket', treat 'id' as the table name
            else if (id === 'kb_article' && articleNumber) {
                // Special case: If 'id' is 'kb_article', use 'kb_knowledge' and 'sysparm_article'
                table = 'kb_knowledge';
            } else {
                table = id; // For all other cases, use 'id' as the table name
            }

            // Variable to hold the GlideRecord result
            var record = null;

            // Case logic to detect the table and execute the query
            switch (table) {
                case 'sc_cat_item':
                    var catItemGr = new GlideRecord('sc_cat_item');
                    if (catItemGr.get(sys_id)) {
                        record = catItemGr;
                    }
                    break;

                case 'kb_knowledge':
                    // Special case: When using KB number instead of sys_id
                    if (articleNumber) {
                        var kbArticleGr = new GlideRecord('kb_knowledge');
                        kbArticleGr.addQuery('number', articleNumber);
                        kbArticleGr.query();
                        if (kbArticleGr.next()) {
                            record = kbArticleGr;
                        }
                    }
                    break;

                case 'sc_req_item':
                    var reqItemGr = new GlideRecord('sc_req_item');
                    if (reqItemGr.get(sys_id)) {
                        record = reqItemGr;
                    }
                    break;

                // Add more cases as needed

                default:
                    var genericGr = new GlideRecord(table);
                    if (genericGr.get(sys_id)) {
                        record = genericGr;
                    }
                    break;
            }

			// Set document properties
            if (record) {
                current.u_document = record.getUniqueValue();
				current.u_document_table = table;
            } else {
                gs.log('Experience Feedback: No record found for table: ' + table + ' and sys_id: ' + sys_id);
            }

        } catch (e) {
            gs.log('Experience Feedback: Error parsing JSON: ' + e.message);
        }
    } else {
        gs.log('Experience Feedback: params is null or empty');
    }

})(current, previous);

If you decide that you'd rather write to a string value, you would update this section:

			// Set document properties
            if (record) {
                current.u_reference = record.getDisplayValue();
            }

[dvelloriy]

Thanks Sheldon, this solution works great. Please refer the screenshot below.

Just need few more suggestions. So I want to enhance this solution further. Now from the experience feedback, i want to give opportunity to our leaders to create Enhancement, story, defect etc and provide them a placeholder somewhere that the feedback has been addressed or not.

Can you please advise on the best route here?