How to put a restriction when we are uploading a data

[Sriram Reddy]

Hi,

When loading data into ServiceNow using the 'load data' function, I want to impose a restriction on the Employee ID (Emp-ID) to ensure it is comprised of 7 digits. The first three digits should correspond to the department. Data should only be inserted if this condition is met; otherwise, a notification should be triggered indicating that the Emp-ID is in an incorrect format.

Refer the Sample Excel sheet

Thanks in Advance.

@AnveshKumar M @Amit Gujarathi 

[Saurav11]

Hello,

You can either use a transform map to do this which gives you better control over the data getting loaded.

OR 

Write a before insert BR on the table to check the condition and if it does not match abort the action.

Please mark my answer as correct based on impact.

[AnveshKumar M · Accepted solution]

Hi @Sriram Reddy 

You can create an onBefore transform script in the transform map you are using with the following script.

var emp_id = '' + source.u_emp_id;

if(emp_id.length != 7 && !isNumeric){

   ignore = true;

}

var dept_id = emp_id.substring(0,3);

var dept = source.u_department;

var gr = new GlideRecord('cmn_department');

gr.addQuery('name', '=', dept);

gr.addQuery('id', '=', dept_id);

gr.query();

if(gr._next()){

   ignore = false;

}else{

   ignore= true;

}

function isNumeric(str) {

  if (typeof str != "string"){

     return false;

  }

  return !isNaN(str) && !isNaN(parseFloat(str));

}

Ensure that your cmn_department table has Name and ID stored correctly.

Please mark my answer helpful and accept as a solution if it helped you 👍✅