Server side scripting to validate the query if record exist before insert

[DB1]

		var grgetUser = new GlideRecord("sys_user");
		grgetUser.addQuery("email",input.nonUser.nonPUseremail);
		grgetUser.query();
		if (grgetUser.next()) {
			if(grgetUser.active == 'false')
			{
				//Set Active = true
				//Check for existing role and then Add roles
				grgetUser.active = 'true';				

			}
			grgetUser.title = input.nonUser.nonPUsertitle;
				grgetUser.phone = input.nonUser.nonPusrphone;
				grgetUser.update();
			var grgetUserName1 = grgetUser.sys_id;
			var table2 = [
				{'tblname':'cmdb_rel_person', 'col1':'user', 'col2':'ci','dev_key':'devauthcaller','prod_key':'prodauthcaller'},
				{'tblname':'u_m2m_sys_user_cmdb_ci_service', 'col1':'sys_user', 'col2':'u_cmdb_ci_service','dev_key':'devauthappr','prod_key':'prodauthappr'},
				{'tblname':'u_m2m_from_cmdb_ci_service_watchlist', 'col1':'u_user','col2':'u_cmdb_ci_service','dev_key':'devwatchlist','prod_key':'prodwatchlist'}
			]
			table2.forEach(function(element) {
				var k =0;
				var test2 = usedfor.split(",");
				test2.forEach(function(value){
					if(input.nonUser[element.dev_key] == true && input.nonUser.nonPUseremail !="" && value == "Development")
					{

						var graddNonUsrRole = new GlideRecord(element.tblname);
						//graddNonUsrRole.addQuery('');
						graddNonUsrRole.initialize();			
						graddNonUsrRole[element.col1] = input.nonUser.nonPUseremail;
						graddNonUsrRole[element.col2] = res[k];
						graddNonUsrRole.insert();
					}

					if(input.nonUser[element.prod_key] == true && input.nonUser.nonPUseremail !="" && value == "Production")
					{
						//gs.addInfoMessage("User has been added successfully into Prod Key");			
						var graddNonUsrRole1 = new GlideRecord(element.tblname);
						graddNonUsrRole1.initialize();
						graddNonUsrRole1[element.col1] = input.nonUser.nonPUseremail;
						graddNonUsrRole1[element.col2] = res[k];
						graddNonUsrRole1.insert();						
					}

					k++;
				});

			});				

		} 

Hello All,

I have the above code from Portal widget to enable insertion in some what easy way.

We check for condition in if - like if it matches the dev key or prod key or if it contains that word Dev or Prod and then we insert records

var graddNonUsrRole = new GlideRecord(element.tblname);

//graddNonUsrRole.addQuery('');

graddNonUsrRole.initialize();

graddNonUsrRole[element.col1] = input.nonUser.nonPUseremail;

graddNonUsrRole[element.col2] = res[k];

graddNonUsrRole.insert();

We query the table using this "var graddNonUsrRole = new GlideRecord(element.tblname);"

But I need to check if record exist. How do I do it with the same above code?

Meaning col1 - has all the fields related to User but in 3 different tables. col 2 refers to CI in 3 different tables.

And input.nonUser.nonPUseremail = has the User value and res[k] contains CI value

How to add Query and check if record exist before insertion.

@Ankur Bawiskar @Iraj Shaikh @Dr Atul G- LNG @Sandeep Rajput @Amit Verma 

[Ankur Bawiskar]

@DB1 

what debugging have you done so far?

[DB1]

That the table name and that values of dev_key and prod_key from client side check = true

The concept is about inserting records in 3 different tables with above conditions satisfied which gets satisfied and insertion happens as well.

The only validation is pending is to check if record exist already. Meaning. 1st table is cmdb_rel_person

so I need to check if same user exist already for same CI where in typical query will be

var gr = new GlideRecord('cmdb_rel_person');

gr.addQuery('user', <username>);// which here could be  input.nonUser.nonPUseremail;

gr.addQuery('ci','IN',CI_sysid)// CI_sysid contains sys id of CI

gr.query();

if(!gr.next()){

//insert record

}

But I need help in adding this embedded with existing query

var gr = new GlideRecord(element.tblname);

gr.addQuery(graddNonUsrRole[element.col1], input.nonUser.nonPUseremail);

gr.addQuery(

graddNonUsrRole[element.col1],'IN',CI_sysid)// CI_sysid contains sys id of CI

gr.query();

if(!gr.next()){

graddNonUsrRole.initialize();

graddNonUsrRole[element.col1] = input.nonUser.nonPUseremail;

graddNonUsrRole[element.col2] = res[k];

graddNonUsrRole.insert();

}

Is this the right way to check?

[Iraj Shaikh]

Hi @DB1 

The code snippet you've provided is close to what you need, but there are a few adjustments to be made. You don't need to use `graddNonUsrRole[element.col1]` in the `addQuery` method. Instead, you should use the column names directly as strings. Also, you should not use `'IN'` operator unless `CI_sysid` is an array of sys_ids. If `CI_sysid` is a single sys_id, you should use the `=` operator.

Here's how you can embed the existence check within your existing code:

table2.forEach(function(element) {
    var k = 0;
    var test2 = usedfor.split(",");
    test2.forEach(function(value) {
        // Check if the record already exists
        var grCheckExistence = new GlideRecord(element.tblname);
        grCheckExistence.addQuery(element.col1, input.nonUser.nonPUseremail);
        grCheckExistence.addQuery(element.col2, res[k]); // Assuming res[k] contains the CI sys_id
        grCheckExistence.query();
        if (!grCheckExistence.next()) {
            // Record does not exist, so we can insert a new one
            if (input.nonUser[element.dev_key] == true && input.nonUser.nonPUseremail != "" && value == "Development") {
                var graddNonUsrRole = new GlideRecord(element.tblname);
                graddNonUsrRole.initialize();
                graddNonUsrRole[element.col1] = input.nonUser.nonPUseremail;
                graddNonUsrRole[element.col2] = res[k];
                graddNonUsrRole.insert();
            }

            if (input.nonUser[element.prod_key] == true && input.nonUser.nonPUseremail != "" && value == "Production") {
                var graddNonUsrRole1 = new GlideRecord(element.tblname);
                graddNonUsrRole1.initialize();
                graddNonUsrRole1[element.col1] = input.nonUser.nonPUseremail;
                graddNonUsrRole1[element.col2] = res[k];
                graddNonUsrRole1.insert();
            }
        } else {
            // Record exists, handle accordingly (e.g., update, skip, etc.)
            // For example, you could log a message or update the existing record
            // gs.addInfoMessage("Record already exists, skipping insertion.");
        }
        k++;
    });
});

In this code:

- We create a new `GlideRecord` object for the table we want to check (`grCheckExistence`).
- We add queries to check for the existence of a record with the specified `element.col1` and `element.col2` values.
- If the `query()` method followed by `next()` returns `false`, it means no record exists with those values, and we can proceed with the insertion.
- If a record does exist, you can decide what action to take, such as updating the existing record or simply skipping the insertion.

Make sure that `res[k]` contains the correct CI sys_id that you want to check against in the `element.col2` field. If `res` is an array of sys_ids, then the code should work as expected. If `CI_sysid` is supposed to be an array of sys_ids and you want to check if any of them match, then you would use the `'IN'` operator. Otherwise, for a single sys_id, use the `=` operator as shown in the code above.

Please mark this response as correct or helpful if it assisted you with your question.