How to set a combination of 2 fields unique?

JLeong · ‎10-13-2020

Hi Guys,

I have a table for Web Accounts. The entries should be unique based on the URL and Account ID entered. How to set a combination of 2 fields to be unique.

Thanks.

Anil Lande · ‎10-13-2020

Hi,

You cannot have the table level configurations to create Unique field as combination of two fields. However, you can have a Before insert/update business rule on your table to validate your new entry.

In the script section you can glide to current table and check if the record already exits with current records value i.e URL and Account ID. If record exist abort the operation.

The sample code would be like:

var web_acc = new GlideRecord("web_account_table");
web_acc.addQuery('u_url',current.u_url);
web_acc.addQuery('u_account_id',current.u_account_id);
web_acc.query();
if(web_acc.next())
{
current.setAbortAction(true);
}

Thanks,

Anil Lande

Please appreciate the efforts of community contributors by marking appropriate response as correct answer and helpful, this may help other community users to follow correct solution in future.
Thanks
Anil Lande

View solution in original post

JLeong · ‎10-13-2020

Thanks bdubz7! I could try this too!

Jim Coyne · ‎10-13-2020

No need for a calculated field or Business Rule - a unique index will do the trick. See this article - Create a table index

JLeong · ‎10-13-2020

Thanks so much Jim! Looks like this is the correct solution but I will consult first with my team.

Appreciate your assistance.

Hendrik6 · ‎09-06-2022

I would combine the approaches from Anil and Jim.

While Anil checks on the server-side whether there is a duplicate, Jim's approach checks a the database level. I think they both have a valid point.

An index prevents that even other applications in the feature will violate the uniqueness constraint. However, with the server-side Business Rule can add a more comprehensible error message to the screen.

In addition I would like to amend Anil's script. It fires an error if an existing record should be edited. Here is my approach:

(function executeRule(current, previous /*null when async*/) {
	var gr= new GlideRecord("your_table_name");
	gr.addQuery('field1', current.field1);
	gr.addQuery('field2', current.field2);
	gr.query();
	if (program.next() && gr.sys_id != current.sys_id) {
		gs.addErrorMessage("The combination of field1 and field2 is already used for another record.");
		current.setAbortAction(true);
	}
})(current, previous);

Please notice in the condition the part where I check if the alleged duplicate record is different from the current record.