In a table How to restrict/alert the creation of duplicate records based on several fields ?

Hari95 · ‎06-30-2022

Hello Everyone,

I had a use case to restrict the creation of duplicate records on a table based on 3 fields.

1. Account

2. Assignment Grp

3. Trouble Type

If any records with these 3 field values existing on the table, system needs to alert the user ex. ''Record with similar combination existed''.

Can anyone help me to achieve this requirement?

Thanks,

Hari

Ankur Bawiskar · ‎07-03-2022

Hi,

you should write before insert business rule on the custom table and then query the table with these 3 fields

Note: Ensure you give correct field names here

(function executeRule(current, previous /*null when async*/) {

	// Add your code here
	var gr = new GlideRecord(current.getTableName());
	gr.addQuery("u_account", current.u_account); // give correct field name
	gr.addQuery("u_trouble_type", current.u_trouble_type); // give correct field name
	gr.addQuery("u_assignment_group", current.u_assignment_group); // give correct field name
	gr.setLimit(1);
	gr.query();
	if (gr.hasNext()) {
		gs.addErrorMessage("This is a duplicate record");
		current.setAbortAction(true);
	}

})(current, previous);

Regards
Ankur

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

View solution in original post

RC12 · ‎06-30-2022

u can write a before business rule which will check with the values on the table. if it finds simliar values do an alert and and prevent it from inserting

Hari95 · ‎07-01-2022

Hi RC,

I do have only one table. On which table i need to write this BR and which table should i need to glide ?

Thanks,

Hari

Abhay Kumar1 · ‎06-30-2022

Hi Hari, You can use script mention on below and modified as per your requirement. https://www.servicenowelite.com/blog/2013/11/22/duplicate-record-scripts Add your script Hope this will helpful.

suvro · ‎06-30-2022

You can write the below before insert business rule

Ensure correct field names

var tgr = new GlideRecord('your table name');

tgr.addquery('account', current.account);

tgr.addquery('assignment_group', current.assignment_group);

tgr.addquery('trouble_type', current.trouble_type);

tgr.query();

if (tgr.next()){

gs.addErrorMessage(" This record already exists");

current.setAbortAction(true);

}