Check for existing record in a table

nicolemccray · ‎05-13-2020

I have a custom table that inserts some user fields each time a request is submitted. How can I do a check on the table to see if the Employee ID already exists, and if it does, display a message to the requester and do not allow submission?

nicolemccray · ‎05-18-2020

I was able to get this working by using an OnChange script:

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}

var employeeId = g_form.getValue('employee_id');

if (employeeId != ''){

var ga_applicant = new GlideAjax("CorporateTravel");
ga_applicant.addParam("sysparm_name","getRequestInfo");
ga_applicant.addParam("employeeId", employeeId );
ga_applicant.getXML(ajaxResponse);

}

function ajaxResponse(serverResponse) {
var applicant = serverResponse.responseXML.getElementsByTagName("applicant");

if (applicant.length > 0){
already_applied = true;

alert("This person already has an application request in the system. Only one application request for a person may be submitted.");

g_form.setValue('understand_accept1', '');
g_form.setValue('understand_accept2', '');
g_form.setValue('understand_accept3', '');

}
}

}

View solution in original post

Dhananjay Pawar · ‎05-13-2020

Hi,

Do you have any common field between custom table and request table?

nicolemccray · ‎05-13-2020

I need to do a check against the Employee ID, which does not exist in the request table.

Ankur Bawiskar · ‎05-13-2020

Hi,

does this field 'u_employee_id' store a reference to sys_user?

On which table this business rule is defined? Also no need of current.insert() in else condition

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

nicolemccray · ‎05-13-2020

No, it is a string field that is being pulled from a field on the form.

Pradeep Sharma · ‎05-13-2020

Hello Nicolemccray,

Create a OnSubmit Client Script against the catalog item and make sure to update line no 2 with the exact variable name that takes employee id from the user.

function onSubmit() {
    //Type appropriate comment here, and begin script below
    var employeeId = g_form.getValue('PASS CATALOG ITEM EMPLOYEE ID VARIABLE NAME HERE');
    var gr = new GlideRecord('u_travel');
    gr.addQuery('u_employee_id', employeeId);
    gr.query();

    if (gr.next()) {
        alert("Employee ID already present");
        return false;
    }



}

- Pradeep Sharma