Check whether the average of a fields is checked for eligibility

[Are Kaveri]

Hi,

I have a below requirement.

There is a table called transformed data table.

we have student data such as Roll number, year, Rank and section

Student name	Anu	Anu	Sree	Sree	Sree
Roll number	1	1	2	2	2
Class	5	6	4	5	6
Section	A	A	A	A	A
year	2021	2022	2020	2021	2022
rank	3	3	4	3	4

On my Record producer I have a variable called Rank

I need to calculate the rank based on the last 2 years.

for Anu 

rank = avg(2022,2021);

I am not understanding how to get Rank of Anu on my Record producer Form.

Tried below script

onload client script:

function onLoad() {
    //Type appropriate comment here, and begin script below
	
	var userid = g_form.getValue('roll_number');
	var year = g_form.getValue('year');
	if(userid != ''){
             var ga = new GlideAjax('x_dev0965_RankAjaxUtils');
                ga.addParam('sysparm_name', 'getRanking');
               ga.addParam('sysparm_sysid',userid );
         	ga.addParam('sysparm_year',year);
                 ga.getXML(alertUser);
}
    function alertUser(response) {
        var answer = response.responseXML.documentElement.getAttribute("answer");
		alert('get answer value'+answer);
        if (answer ) {
          
            g_form.setValue('ranking',answer);
            
        } 
    }

}





Script Include:

getRanking: function() {
        var userSysID = this.getParameter('sysparm_rollno');
        var year = this.getParameter('sysparm_year');
        var year1 = year-1;
		var year2 = year-2;

        var gr = new GlideRecord('x_dev0965_transform_refer_data');
        gr.addQuery('roll_number', userSysID);
        gr.addQuery('year', 'IN', [year1, year2]);
        gr.query();
        var totalRanking = 0;
        var Count = 0;
        while (gr.next()) {
            totalRanking += gr.rating;
            Count++;
        }

        return Count > 0 ? totalRanking / Count : 0;

    },

My script include is not working as expected please help me.

[Prince Arora]

@Are Kaveri 

can you update the code as

getRanking: function() {
        var userSysID = this.getParameter('sysparm_rollno');
        var year = this.getParameter('sysparm_year');
        var year1 = (year-1) + "";
	var year2 = (year-2) + "";
        year = year1 + "," + year2;

        var gr = new GlideRecord('x_dev0965_transform_refer_data');
        gr.addQuery('roll_number', userSysID);
        gr.addEncodedQuery('yearIN'+ year);
        gr.query();
        var totalRanking = 0;
        var Count = 0;
        while (gr.next()) {
            totalRanking += parseInt(gr.rating);
            Count++;
        }

        return Count > 0 ? totalRanking / Count : 0;

    },

If my answer solved your issue, please mark my answer as ✅ Correct & 👍Helpful based on the Impact.

[Are Kaveri]

@Prince Arora  returning answer as 0.0 for everything.

I want to return average of rank of student. but not working

[Prince Arora]

@Are Kaveri '

I can see one issue in the script:

 var ga = new GlideAjax('x_dev0965_RankAjaxUtils');
                ga.addParam('sysparm_name', 'getRanking');
               ga.addParam('sysparm_sysid',userid );
         	ga.addParam('sysparm_year',year);
                 ga.getXML(alertUser);

You have passed "sysparm_sysid" from the client script and you are fetching data using

var userSysID = this.getParameter('sysparm_rollno');

Please update the Script include as:

var userSysID = this.getParameter('sysparm_sysid');

Also please add logs to print the totalRanking in the script include using

gs.info("totalRanking : " +totalRanking);

If my answer solved your issue, please mark my answer as ✅ Correct & 👍Helpful based on the Impact.

[Are Kaveri]

@Prince Arora  

The year is not printing that is why it is not coming.