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 ,

Can you please share some more details related to it like from where or which table you are pulling the data of current and previous year and any other related information like what are field types of those fields, So that its can help us to fulfill your requirement.

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

[Are Kaveri]

Hi @Aniket Chavan 

the requirement is as below:

we have request table , on which we need calculate points gained.

we have history of employee table where we find rank and performance points.

so ,

here my requirement is that for every employee each year we have to calculate Ratings that is depending on performance points gained each year(i.e., current and last year.)

so in my History of employee table i have data od past few years.

example:

emp 100 - in 2024 - rank 3 and we have to take performance points as 2.

emp 100  - in 2023 - rank 4 and we have to take performance points as 3.

then we need to add current and last year performance points as below

2024 year + 2023 year = 2+3 = 5 

I need below calculation .

[Aniket Chavan]

Hello @Are Kaveri ,

Okay thank you for the confirmation and all the details, you can give a try to the script below in Business rule or background script as well to update previous records 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', '>=', gs.getYear() - 1); // Filter records for the current year and the previous year
historyGr.addQuery('year', '<=', gs.getYear());
historyGr.orderBy('employee_number');
historyGr.orderByDesc('year');
historyGr.query();

var currentYear = gs.getYear();
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();
        }
    }
}

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

[Are Kaveri]

@Aniket Chavan 

when i executed in background script got below error.