Calculate value from to fields in two rows and insert this as a row in the same table.

[Twan]

Hi,

From  the table asmt_metric_result we copy via a business rule with conditions, the question and the value to a separate custom table.

The table looks like below

Question	Value	Score
1.1.1 Risk	2
1.1.1 Likelihood	2
1.2.1 Risk	1
1.2.1 Likelihood	3

What we like to achieve is to multiply the value of "1.1.1 Risk" with the value of "1.1.1 Likelihood" and insert the score in a new row. with the name "1.1.1 Score".

The same for 1.2.1. and 1.3.1 etc.

Who can help to create a script or other way to get this done.

[Juhi Poddar · Accepted solution]

Hello @Twan 

As per my understanding you want to create a new record with question as "1.1.1 Score" and value is the multiplication of value at "1.1.1 Risk" & "1.1.1 Likelihood"  in your custom table.

To achieve the requirement try this background script:

// Table name: replace with your custom table's name
var tableName = "custom_table_name"; // Example: u_custom_scores

// Step 1: Get all unique prefixes
var prefixMap = {}; // To store risk and likelihood values by prefix
var gr = new GlideRecord(tableName);
gr.query();

while (gr.next()) {
    var question = gr.getValue("question");
    var prefix = question.split(" ")[0]; // Extract prefix (e.g., 1.1.1)

    // Organize Risk and Likelihood by prefix
    if (!prefixMap[prefix]) {
        prefixMap[prefix] = { risk: null, likelihood: null };
    }

    if (question.endsWith("Risk")) {
        prefixMap[prefix].risk = parseInt(gr.getValue("value"), 10);
    } else if (question.endsWith("Likelihood")) {
        prefixMap[prefix].likelihood = parseInt(gr.getValue("value"), 10);
    }
}

// Step 2: Calculate score and insert new rows
for (var prefix in prefixMap) {
    var riskValue = prefixMap[prefix].risk;
    var likelihoodValue = prefixMap[prefix].likelihood;

    // Only calculate if both Risk and Likelihood are present
    if (riskValue !== null && likelihoodValue !== null) {
        var score = riskValue * likelihoodValue;

        // Check if the score row already exists
        var scoreCheck = new GlideRecord(tableName);
        scoreCheck.addQuery("question", prefix + " Score");
        scoreCheck.query();

        if (!scoreCheck.next()) {
            // Insert new Score row
            var newRow = new GlideRecord(tableName);
            newRow.initialize();
            newRow.setValue("question", prefix + " Score");
            newRow.setValue("value", score);
            newRow.insert();
        } else {
            // Update the existing Score row
            scoreCheck.setValue("value", score);
            scoreCheck.update();
        }
    }
}

Note: Please update the var tableName in code.

"If you found my answer helpful, please like and mark it as the "accepted solution". It helps others find the solution more easily and supports the community!"

Thank You
Juhi Poddar

[Twan]

Thanks for your help it solved our challenge. 

[Juhi Poddar]

Hello @Twan 

Glad to know that the issue is resolved. Feel free to reach out for any further queries.

Thank you 

Juhi Poddar