Script to update the Percent Complete of CWM (Frames) in actual value in Target by Target Automation

[fernandogon]

Hi All,

 I'm new to programming and would like some help checking what could be wrong. My requirement is:

I need a script that updates the goal value (actual_value field) from the sn_gf_goal_target table based on all collaborative boards (sn_cwm_board) linked to a specific objective (goal) from the sn_gf_goal_target table and taking the arithmetic mean of the percent_complete field from the sn_align_core_planning_item table when we have several Can you help me?

Following the script:

//Initialize variables for calculation
var totalPercentComplete = 0;
var count = 0;
var goalSysId = target.goal; // Goal ID

// Consult the collaborative boards related to the objective
var grBoard = new GlideRecord('sn_cwm_board'); // Table of collaborative boards
grBoard.addQuery('primary_goal', goalSysId); // Filter by specific goal

// Go through each frame found to add the percent_complete values ​​and count the number of frames
while (grBoard.next()) {
var planningItemId = grBoard.getValue('planning_item_id'); // Field that stores the planning item ID
if (planningItemId) {
var grPlanningItem = new GlideRecord('sn_align_core_planning_item'); // Planning Items Table
if (grPlanningItem.get(planningItemId)) {
var percentComplete = parseFloat(grPlanningItem.getValue('percent_complete')) || 0; // Gets the percentage of completion
totalPercentComplete += percentComplete;
count++;
}
}
}

// Calculate the arithmetic mean
var averagePercentComplete = (count > 0) ? (totalPercentComplete / count).toFixed(2) : 0;

// Update the actual value of the goal associated with the objective in the sn_gf_goal_target table
if (goalSysId) {
var grGoalTarget = new GlideRecord('sn_gf_goal_target'); // Goal table
grGoalTarget.addQuery('goal', goalSysId); // Checks if a record exists for the specific objective
grGoalTarget.query();

if (grGoalTarget.next()) {
grGoalTarget.setValue('actual_value', averagePercentComplete); // Update the actual goal value fielda
grGoalTarget.update(); // Saves the update to the table
}
}

// Returns the calculated average value
averagePercentComplete;

Best Regards, Fernando

[Murthy Ch]

Hello @fernandogon 

Gone through the entire code but I don't see any issue. Everything looks good to me.

Only suggestion is to keep logs and verify the code and the values.

Also moved the goalSysId check condition to the top.

var totalPercentComplete = 0;
var count = 0;
var goalSysId = target.goal; // Goal ID
if (goalSysId) {
// Consult the collaborative boards related to the objective
var grBoard = new GlideRecord('sn_cwm_board'); // Table of collaborative boards
grBoard.addQuery('primary_goal', goalSysId); // Filter by specific goal

// Go through each frame found to add the percent_complete values ​​and count the number of frames
while (grBoard.next()) {
var planningItemId = grBoard.getValue('planning_item_id'); // Field that stores the planning item ID
if (planningItemId) {
var grPlanningItem = new GlideRecord('sn_align_core_planning_item'); // Planning Items Table
if (grPlanningItem.get(planningItemId)) {
var percentComplete = parseFloat(grPlanningItem.getValue('percent_complete')) || 0; // Gets the percentage of completion
totalPercentComplete += percentComplete;
count++;
}
}
}

// Calculate the arithmetic mean
var averagePercentComplete = (count > 0) ? (totalPercentComplete / count).toFixed(2) : 0;

// Update the actual value of the goal associated with the objective in the sn_gf_goal_target table

var grGoalTarget = new GlideRecord('sn_gf_goal_target'); // Goal table
grGoalTarget.addQuery('goal', goalSysId); // Checks if a record exists for the specific objective
grGoalTarget.query();
if (grGoalTarget.next()) {
grGoalTarget.setValue('actual_value', averagePercentComplete); // Update the actual goal value fielda
grGoalTarget.update(); // Saves the update to the table
}
}

// Returns the calculated average value
return averagePercentComplete;

BTW, Where do you running this script?

[fernandogon]

Hi @Murthy 

I´m using this calculation for a collaborative work management (CWM) to update the value of the goal in the strategic objective within the Strategic Planning Workspace in the Target automation.

[ravikumarmv]

Hi @fernandogon 

      There is no need to set the actual_value
  (grGoalTarget.setValue('actual_value', averagePercentComplete); // Update the actual goal value fielda
The value "averagePercentComplete" thats being returned at the end of script will be set in actual_value of the target by the target automation framework.

Thanks

Ravi