Need help for one calculation

[Are Kaveri]

Hi  

I have below requirement.

there is one table called History of employee.

I need to calculate current year and last year Rank and calculate the points gained.

employee number	year	rank	performance points
100	2024	3	1
100	2023	4	2
100	2022	5	3
101	2024	4	2
101	2023	4	2
101	2022	4	2

I need to calculate the performance points for employee.

for this i am not understanding how to pull current and previous year and add the performance points.

please help me

[Aniket Chavan]

Hello @Are Kaveri ,

Please give a try to the scrpt below and let me know how it works for you.

// Assuming you have a GlideRecord named 'history_of_employee' representing the history table
var historyGr = new GlideRecord('history_of_employee');
historyGr.addQuery('year', '>=', new Date().getFullYear() - 1); // Filter records for the current year and the previous year
historyGr.addQuery('year', '<=', new Date().getFullYear());
historyGr.orderBy('employee_number');
historyGr.orderByDesc('year');
historyGr.query();

var currentYear = new Date().getFullYear();
var lastYear = currentYear - 1;

while (historyGr.next()) {
    var employeeNumber = historyGr.employee_number.toString();
    var year = historyGr.year.toString();
    var performancePoints = parseInt(historyGr.performance_points.toString(), 10);

    if (year == currentYear || year == lastYear) {
        // Assuming you have a GlideRecord named 'request' representing the request table
        var requestGr = new GlideRecord('request');
        requestGr.addQuery('employee_number', employeeNumber);
        requestGr.addQuery('year', year);
        requestGr.query();

        if (requestGr.next()) {
            // Update the request record with the calculated performance points
            requestGr.setValue('performance_points', requestGr.performance_points + performancePoints);
            requestGr.update();
        } else {
            // Create a new request record if it doesn't exist for the employee and year
            var newRequestGr = new GlideRecord('request');
            newRequestGr.initialize();
            newRequestGr.setValue('employee_number', employeeNumber);
            newRequestGr.setValue('year', year);
            newRequestGr.setValue('performance_points', performancePoints);
            newRequestGr.insert();
        }
    }
}

[Aniket Chavan]

Hello @Are Kaveri ,

Please give a try to the scrpt below and let me know how it works for you.

// Assuming you have a GlideRecord named 'history_of_employee' representing the history table
var historyGr = new GlideRecord('history_of_employee');
historyGr.addQuery('year', '>=', new Date().getFullYear() - 1); // Filter records for the current year and the previous year
historyGr.addQuery('year', '<=', new Date().getFullYear());
historyGr.orderBy('employee_number');
historyGr.orderByDesc('year');
historyGr.query();

var currentYear = new Date().getFullYear();
var lastYear = currentYear - 1;

while (historyGr.next()) {
    var employeeNumber = historyGr.employee_number.toString();
    var year = historyGr.year.toString();
    var rank = parseInt(historyGr.rank.toString(), 10);

    // Determine performance points based on the rank
    var performancePoints = 0;
    if (rank === 3) {
        performancePoints = 1;
    } else if (rank === 4) {
        performancePoints = 2;
    } else if (rank === 5) {
        performancePoints = 3;
    }

    if (year == currentYear || year == lastYear) {
        // Assuming you have a GlideRecord named 'request' representing the request table
        var requestGr = new GlideRecord('request');
        requestGr.addQuery('employee_number', employeeNumber);
        requestGr.addQuery('year', year);
        requestGr.query();

        if (requestGr.next()) {
            // Update the request record with the calculated performance points
            requestGr.setValue('performance_points', requestGr.performance_points + performancePoints);
            requestGr.update();
        } else {
            // Create a new request record if it doesn't exist for the employee and year
            var newRequestGr = new GlideRecord('request');
            newRequestGr.initialize();
            newRequestGr.setValue('employee_number', employeeNumber);
            newRequestGr.setValue('year', year);
            newRequestGr.setValue('performance_points', performancePoints);
            newRequestGr.insert();
        }
    }
}

Let me know your views on this and Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks,

Aniket