Duplicate Checking in Trigger

[akadam]

Hello,

My item has kind of composit primary key. Combination of 4 fields make my Item unique. Somehow duplicate checking functionality offered by Integrity is not helpful or not working for me.

I created Unique Name field, and populated this field via pre-triggers. Now before I commit this item, I would like to check uniqueness of this calculated string but I do not find any direct call to get items based on string value. Neither the query is allowing to dynamically substitute parameter and execute.

What would be best suggession or solution in this case for duplicate checking?

Thanks for reading my post,

Regards

Adhik

[mrump · Accepted solution]

Hi Adhik,

a trigger like this should do (see attachment)

....
 
/**
 * creates the Command to Run the temporary Query
 * @return Packages.com.mks.api.Command queryCommand
 */
function getRunQueryCommand()
{
 var queryCommand = new Packages.com.mks.api.Command(Packages.com.mks.api.Command.IM, "issues");
 queryCommand.addOption(new Packages.com.mks.api.Option("fields", "Type,ID")); 
 
 var queryBuf = new java.util.StringBuffer();
 queryBuf.append('(');
 queryBuf.append("field[");
 queryBuf.append(singletonField);
 queryBuf.append("]=");
 queryBuf.append(currentValue);
 queryBuf.append('"');
 queryBuf.append(')');
 
 queryCommand.addOption(new Packages.com.mks.api.Option("queryDefinition", queryBuf.toString()));
 return queryCommand;
}
 
 
/**
* check whether the temp query finds at least one Item of the same value as the current Item
* @return true if successful; else always false
*/
function check4Singleton()
{
 // Create an API Object to run IM Command Line commands
 var api = environmentBean.createAPISessionBean();
 
 // Execute the Query command
 var runQueryOut = api.executeCmd(getRunQueryCommand());
 if( createQueryOut.getExitCode() == 0 ){
 logDebug("Successfully run temporary Query");
 if( runQueryOut.getExitCode() == 0 ){
 // analyse result 
 if (runQueryOut.getWorkItemListSize() == 0 ){ 
 logDebug("no duplicate found, proceed");
 return true;
 }
 else{
 print("found "+runQueryOut.getWorkItemListSize()+" Item(s) of the exact same value for Field "+ singletonField );
 logDebug("abort");
 return false;
 }
 }
 else
 {
 print("Failed to run temporary Query");
 logDebug("leave check4Singleton()");
 return false;
 }
 } // END if( createQueryOut.getExitCode() == 0 )
 else{
 print("Failed to create temporary Query");
 } 
 return false;
}
....

[akadam]

Thanks Matthias,

With little tweaks it worked. Now I have to make it generic enough so that others can use too like library function.

Regards

Adhik

[DanR.]

I get an error when using this script. "Could not save item: JavaScript Error: Internal Error: Invalid return (/mks2009/IntegrityServer/data/triggers/scripts/checkSingletonField_dev.js; line 139)"

I am using it as a pre-trigger.

I really need some help with this.

[DanR.]

Anybody?

[mrump]

Hi Dan,

what is in Line 139 of your script?

Please post it here.

Your error seems like "cast" problem.

[DanR.]

if (singletonField != null && singletonField.length() != 0){

if (!issueDeltaBean.getNewFieldDisplayString(singletonField)){

abortThisScript(" #### This script may opnly be run for an Item of the Type : "+ singletonField + " ! ####");

return;

}

}

[KaelLizak]

I suspect the problem is actually with the line prior to 138:

if (!issueDeltaBean.getNewFieldDisplayString(singletonField)){

My guess is there is a probelm with singletonField, or with the getNewFieldDisplayString(...) method.

Which release of Integrity are you on? It doesn't look like the method has changed since 2009 SP7 at latest.

[DanR.]

We are on 2009 SP5. The singleton Field in use is of Integer type.

[mrump]

Hi Dan,

is the "if-clause" part of a function definition?

I assume the "return" statement in line 140 might be the problem, as you can only return from inside a function.

HTH Matthias

[DanR.]

I didn't write this script, but I do see an IF statement in there.

[LLawton]

Hello Dan,

It is very dificult to debug a script or its implementation with so very little information, so here are a few pointers, assuming that the script you are using is the one attached to this discussion without modifications.

1) Line 134 uses parameter "SingletonField Type" which is not defined.

2) Line 137 reads like it expects the display value of singletonField to be a boolean.

3) Line 140 is unnecessary (I think it's a minor issue).

Because of these, I wouldn't expect the script to run without modifications.

First I would fix the parameter name to use the one defined in the script (singletonField) to make sure you get the value of the field you need to check.

Then I would get rid of the whole block from 136 to 142. It seems to be a holdover from a different version of this script that adds nothing here.

Then I'd give it a try.

[DanR.]

The Singleton Field is defined at the beginning of the script

// @param Field *SingletonField

[LLawton]

If I look at the code I have, the line you quote defines a parameter name that is not used. Please read what I wrote under item 1) in double-quotes.

But anyway, it's useless in that script. Did you try to comment out the section I mentioned and try running it?

[mrump]

Sorry Folks,

there was a Copy/Paste error in the "check4Singleton" function.

here's the corrected version:

/**
 * check whether the temp query finds at least one Item of the same Type as the current Item
 * @return true if successful; false in case of a failure
 */
function check4Singleton()
{
 // Create an API Object to run IM Command Line commands
 var api = environmentBean.createAPISessionBean();
 // Execute the Query command
 var runQueryOut = api.executeCmd(getRunQueryCommand());
 // process the response object
 if( runQueryOut.getExitCode() == 0 ){
 logDebug("Successfully run temporary Query");
 // analyse result 
 if (runQueryOut.getWorkItemListSize() == 0 ){ 
 logDebug("no duplicate found, proceed");
 return true;
 }
 else{
 print("found "+runQueryOut.getWorkItemListSize()+" Item(s) of the exact same value for Field "+ singletonField );
 logDebug("abort");
 return false;
 }
 }
 else{
 print("Failed to run temporary Query");
 logDebug("leave check4Singleton()");
 return false;
 }
}
// -------------------------------------- global variables-------------------------------------------------
var environmentBean = bsf.lookupBean('siEnvironmentBean');

[DanR.]

Is this the entire new script? Seems to be missing something.

[mrump]

Hi Dan,

obviously it is not.

The post only contains a corrected version of the function "check4Singleton().

My previous post contained a COPY/PASTE error, as there was an variable "createQueryOut" that needn't be there.

In my very first versions of the script I used an API call to create a new named query; then I used an API call to run that named query and finally I used an API call to delete that named query. Now since 2009 I know that that approach is not necessary, as you can use the "queryDefinition" option when running a query.

Anyway, I think there is enough code in all theses posts to solve the problem, as long as you have basic knowledge of javascript and how to setup a trigger in integrity.

If that's not the case, maybe you should call PTC service and ask them to write your script.