Store string field data in table and perform calculations

[shane_davis]

I am using a data lookup with the 3 fields below on my Change Task table. My goal is that to make calculations on each Change Task's score for the task's assigned to user.   Each user's score for each task needs to be stored in a table and be reportable.   I also need to change the field color for the assigned to field on the Change Request table based on what their current score is.    

The part I really need help on is taking the score from each Change Task and permanently storing it in a table to where I can make calculations off that data.

Failure type - choice such as Unauthorized

Failure cause - choice such as Planning

Failure score - string value which would populate to 10 based on two cause and type choices above.

Any help is appreciated!!

[Anurag Tripathi]

Hi Shane,

You can roll up this score to the change request like this

create a new BR on the change task table

after

insert/update

condition: current.failure_score.changes()

script.

//glide to the parent or use getRefRecord and sabe the score to the change_request

But when you say that you need this total score to start from 0 every 90 days , where is this counter. It will not be on the change request as you will certainly not be using 1 change request for years. Is is rolling up to the User profile on some field on sys_user?

[shane_davis]

Regarding your statement below, great question!   That's the part I didn't know how to do.   I thought that I would have to record my failure score and total score to some type of table, but your idea sounds like a good one.   With your idea, I think I can create a Total Failed Change Score on the sys_user table.   Every time a Failure Score is saved on a Change Task, it will add that score to the Total Failed Change Score.   Then, after 90 days, it will set the Total Failed Change Score to 0.   Now, how I actually do that is another story because I am new to scripting and usually find pieces until I figure it out!

"But when you say that you need this total score to start from 0 every 90 days , where is this counter. It will not be on the change request as you will certainly not be using 1 change request for years. Is is rolling up to the User profile on some field on sys_user?"

Thank you very much for your help!!

[Anurag Tripathi]

Now i see the whole picture. Glad that I could help.

Let me know if any further help is required to achieve this, until then you can close off this loop my marking it answered.

[shane_davis]

Anurag,

      I am trying the script below, but it is not working.   Any ideas?   Thank you for all of your help! - Shane

function onChange(control, oldValue, newValue, isLoading, isTemplate) {



 //Get value of u_failed_change_score on the sys_user table, add that to the u_failure_score on the change_task table, and then overwrite the current user's score with the new score.

 var totalusrscore = g_form.getReference('sys_user');

 var f2 = g_form.getValue('totalusrscore.u_failed_change_score'); //Field on the sys_user table

 var f1 = g_form.getValue('u_failure_score'); //Field on the change_task table

 var total = f1 + f2; //Add the 2 fields together to get the sum



 g_form.setValue('f2', total); //set the score on the sys_user table to the new score

}

[Anurag Tripathi]

try this

1. function onChange(control, oldValue, newValue, isLoading, isTemplate) {  
2.      
3. //Get value of u_failed_change_score on the sys_user table, add that to the u_failure_score on the change_task table, and then overwrite the current user's score with the new score.  
4. var f2 = g_form.getValue('totalusrscore.u_failed_change_score'); //Field on the sys_user table  
5. var f1 = g_form.getValue('u_failure_score'); //Field on the change_task table  
6. var total = f1 + f2; //Add the 2 fields together to get the sum  
7.  
8. g_form.setValue('f2', total); //set the score on the sys_user table to the new score  
9. }  

The above code will just save the total in f2 which is a field on change task.

Now you tell me that user on whose profile this total should get rolled up is related to this task? Assigned to??