How to prevent records from being created/updated under certain cases.

[emyrold]

We have a custom table where I have written (what I thought was a simple) BR to prevent a record from being created if:

- for every field_01 + field_02 combination, there needs to be a unique field_03 value.

e.g. field_01 = appName, field_02 = environment and field_03 = profileName

There should always be only one profileName for every appName and environment combination.

The table has many other columns per record, but these are the key fields.

The issue I have, is my (before) BR (with both insert and update checked) works when I create new records.   And when I update an existing record where I change profileName frome "Joe" to "Sally" (assuming Sally does not already exist).   However, if I now go and change any other value of other fields on the record, it prevents the update because profileName already exists.   The only work-a-round for now until I fix this logic is that when I want to change one of the other field values, I have to temporarily change profileName to something unique while I make the save and then again rename it back to the original profileName again.

So is there a way to do this in one BR or do I need two, one for insert and one for update?

var tla = current.u_tla;
var env = current.u_environment;
var profile = current.u_profile;

var hostGR = new GlideRecord('u_cdm_host_migration');
hostGR.addQuery('u_tla', tla);
hostGR.addQuery('u_environment', env);
hostGR.addQuery('u_profile', profile);
hostGR.query();

if (hostGR.next()) {
   current.u_profile.setError('Profile Name: ' +profile+ ' already exists for this TLA and Environment');
   current.setAbortAction(true);
}

[Tanaji Patil · Accepted solution]

Hi emyrold,

From your explanation what I usderstand is there should always be only one combination of 'u_tla' + 'u_environment' + 'u_profile'. While creating a new record the uniqueness must be checked. While updating an existing record, if this combination is touched then again check the uniqueness. But if the combination of these three fields is not touched then there is no need of checking the uniqueness and let them update the record.

Please use below modified code and let me if you need anything else. If you want any change in the combination you can simply change the conditions in '(tla.changes() || env.changes() || profile.changes())'.

var tla = current.u_tla;  

var env = current.u_environment;  

var profile = current.u_profile;  

if(!current.isValidRecord() || (tla.changes() || env.changes() || profile.changes()))

{

var hostGR = new GlideRecord('u_cdm_host_migration');  

hostGR.addQuery('u_tla', tla);  

hostGR.addQuery('u_environment', env);  

hostGR.addQuery('u_profile', profile);  

hostGR.query();  

if (hostGR.next())

{  

  current.u_profile.setError('Profile Name: ' +profile+ ' already exists for this TLA and Environment');  

  current.setAbortAction(true);  

}

}

[emyrold]

Hi Tanaji,

This works fairly well.   However, if you open a record (with all the fields filled out), right-click the header and choose "insert" it will create a new record with duplicate env, tla and profile names.

Seems like "insert" UI Action somehow bypasses the new record check if (!current.isValidRecord())

Any idea's ?   This is just an edge case, most people will not have the role 'admin' so they will not have the ability to choose the "insert" UI Action.

[Tanaji Patil]

Hi emyorld,

Sorry I don't have any idea how its bypassing. But including the below script at the top in the 'insert' UI action. It will restrict the 'insert' as well if the combination of the three fields already exists in the database table.

if(current.instanceOf('u_cdm_host_migration')){

var hostGR = new GlideRecord('u_cdm_host_migration');

hostGR.addQuery('u_tla', current.u_tla);

hostGR.addQuery('u_environment', current.u_environment);

hostGR.addQuery('u_profile', current.u_profile);

hostGR.query();

if (hostGR.next())

{

gs.setErrorMessage('Combination already exists'); //your error message

  current.setAbortAction(true);

}

}

This code should be followed by the OOB code present in that 'insert' UI action.

Thanks,

Tanaji

[emyrold]

Hi Tanaji,

Have you seen this post by Chuck Tomasi UI Action Overrides ?

So maybe I can just create a new UI Action on this custom table and then put the code in you suggested and not modify the OOB "insert" UI Action.   What do you think?

Thanks,

-e