question Re: How to set a combination of 2 fields unique? in ServiceNow AI Platform forum

How to set a combination of 2 fields unique?

JLeong — Tue, 13 Oct 2020 11:12:57 GMT

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.

Re: How to set a combination of 2 fields unique?

Anil Lande — Tue, 13 Oct 2020 13:10:28 GMT

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

Re: How to set a combination of 2 fields unique?

bdubz7 — Tue, 13 Oct 2020 13:15:29 GMT

you can create field values based on a calculated value.

On the Dictionary Entry for the attribute, you can make it a Calculated Value to combine 2 fields or something similar.

Re: How to set a combination of 2 fields unique?

JLeong — Tue, 13 Oct 2020 14:35:37 GMT

Thanks Anil!!

Re: How to set a combination of 2 fields unique?

JLeong — Tue, 13 Oct 2020 14:36:46 GMT

Thanks bdubz7! I could try this too!

Re: How to set a combination of 2 fields unique?

Jim Coyne — Tue, 13 Oct 2020 14:55:05 GMT

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

Re: How to set a combination of 2 fields unique?

JLeong — Tue, 13 Oct 2020 16:28:46 GMT

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

Appreciate your assistance.

Re: How to set a combination of 2 fields unique?

Hendrik6 — Tue, 06 Sep 2022 12:33:54 GMT

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.

Re: How to set a combination of 2 fields unique?

Tony V — Fri, 07 Feb 2025 15:55:14 GMT

This may be an ancient post but the above answer is not true, or is no longer true. You can create a unique index based on multiple fields. If the current data violates the index you want to create you will have to either delete the offending record or delete all records. So this is best done when creating the table.

Go to table list or form
Right click header and choose Configure > Table
scroll down to Database Indexes related list
If there is an existing index on one of the fields you want to use you can optionally click it and drop the index. However when you create the new index the system will amend that index.
Click New
Move the fields you want to the selected box
Check Unique Index
Click Create Index