Skills Validation Business Rule for Assigning of Technician

Hi Cheshin,

We want to limit the assignment of technicians by use of the skills function.

We will populate task_skills by using the place of the request. So we will indicate skill lines in place and when a request is created for that place. The task will inherit the corresponding skills.

So when a technician is being assigned to the task, any technician without the corresponding skills will be stopped from being assigned. I am using a validation BR to do this.

So for an example.

Currently Place ID 100, will have Skill A, Skill B, Skill C and Skill D in place_skill table.

When a request is created for Place ID 100. the task_skill table will inherit these 4 skills.

For assigning of technician. In my validation BR, I need it to check if the technician has the required skills in his/her Person_skill table. If it meets the condition, the technician will be able to be assigned. If it does not meeting the conditions. It will be stopped via the validation BR.

The conditions could be eg. If task_skill has Skill A and B, and if technician has Skill A and B, he will be allowed to be assigned. Otherwise, he will be stopped.

Place_skill:

Person_skill:
Person A:

Person B:

Hi @StejonatL 

I understand the scenario and conditions, but missing the workflow.

How do you assign the technician to the task? Automatic? Manually? Which client? etc.

Cheers!

Hey @StejonatL 

Thanks for the info.

Personally, I would create a client script to check the validity of the assignment.

It will be easier to implement and the UX should be a bit better.

Cheers!

Hi @Shneor Cheshin 

Able to see what is wrong with my client script?

Currently line 3 is giving issue.

This is the client script code:

var taskId = getControlValue("request", "task_id");
var personId = getControlValue("task", "person_id");
var personSkill = getDBValues(stringFormat("SELECT skill FROM person_skill where person_skill.person_id = {0}",personId));
var taskSkill = getDBValues(stringFormat("SELECT skill FROM task_skill where task_skill.task_id = {0}",taskId));


if (personSkill != taskSkill)
    {    
        alert(getMessage("C_SKILL_NOT_PRESENT", "ERROR"));
            return false;
    }  
    
return true;

Hi @Shneor Cheshin 

Is there any example or baseline BR that compares multiple values within 2 tables? Similar to the requirements of the above work flow.

Hi @Shneor Cheshin 

What is wrong with my XML Code?

<hierarchy_select>
  <primary_table>person_skill</primary_table>
  <from>
    <table>person_skill</table>
    <table>task_skill</table>
    <table>task</table>
  </from>
  <attrs>
    <attr>person_skill.skill</attr>
    <attr>task_skill.skill</attr>
    <attr>task.person_id</attr>
    <attr>person_skill.person_id</attr>
  </attrs>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>person_skill.skill</left_operand>
        <operator>eq</operator>
        <right_operand>task_skill.skill</right_operand>
      </constraint>
    </data_constraint>
  </where>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>task.task_id</left_operand>
        <operator>eq</operator>
        <right_operand>1853</right_operand>
      </constraint>
    </data_constraint>
  </where>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>person_skill.person_id</left_operand>
        <operator>eq</operator>
        <right_operand>task.person_id</right_operand>
      </constraint>
    </data_constraint>
  </where>
  <where>
    <join_constraint>
      <constraint>
        <left_operand>person_skill.person_id</left_operand>
        <operator>eq</operator>
        <right_operand>task.person_id</right_operand>
      </constraint>
    </join_constraint>
  </where>
</hierarchy_select>

I am getting the below result:

<person_skill_hierarchy_select_result result_name="" />

Hi @Shneor Cheshin 

Ok thank you. I have now used your suggestion and used a client script. This client script is binded to a Screen Event → Table Event → BeforeRowSave

When I save my request i get an error

This is my client script and client script sql

var personId = getControlValue("task", "person_id");
var taskId = getControlValue("task", "task_id");
var projectSkill = getDBValue("C_SKILL_PRESENT_SQL",personId,taskId);
var personSkill = getDBValue("C_PER_SKILL_PRESENT_SQL",personId,taskId);


if (personSkill == "0")
    {
    alert(getMessage("C_SEC_PER_FAIL", "ERROR"));
    return false;
    }
    
if (personSkill == "1")
        {
        if (projectSkill == "0")
            {
            alert(getMessage("C_SEC_PROJ_FAIL", "ERROR"));
            return false;
            }
            
        else if (projectSkill == "1")
                {
                return true;
                }
        }

What could be the issue?

Hi @Shneor Cheshin 

After using getDBValues as is required for web client scripts. I am getting this error:
 

Below are the SQL scripts:
C_SKILL_PRESENT_SQL

select count (*) from person_skill where person_id = '{0}' and Skill like 'PROJECTSC%' and expiration_dt > SYSDATETIME() 
and skill in 
(
  select skill from TASK_SKILL where task_id  = '{1}' and Skill like 'PROJECTSC%'
)

C_PER_SKILL_PRESENT_SQL

select count (*) as count from person_skill where person_id = '{0}' 
 and Skill = 'PERSONALSC' 
 and expiration_dt > SYSDATETIME()
 and CAST(skill_level as INT) <= 
 
  (select CAST (skill_level as INT) from TASK_SKILL where
   task_id  = '{1}' and 
    Skill = 'PERSONALSC' 
  )

I suspect that the values being returned are string and somehow it throws me this error. Greatly appreciate your help.

Hi @Shneor Cheshin 

I get this error when trying to use count

Could these issues be caused by the return output being a string?

getDBValue without the ‘s’ works. The script works and i can get the correct result. Just that I cannot use it for web client I guess.

I am only having this issue with getDBValues.

Also, the result of the 2 sql scripts are one value as well, not an array or multiple values.

Any way around this?

@StejonatL 

Also try a simple query to start with. Just for testing.

For example

select count (*) as count from person_skill where person_id = '{0}' 

Cheers!