Form Validation Forum :: Check for uniqueness for 2 fields

Context: Parking decals issued for university students, faculty, and staff.  Each parking decal has unique number. 

Question: If one person needs to register two vehicles and therefore be issued two unique parking decals, how can I insure the data in Decal1 and Decal2 fields are unique throughout the database? 

Additional Info: Data in Decal1 can't be duplicated in Decal1 or Decal2 fields and Decal2 can't be duplicated in Decal1 or Decal2 fields.

Thanks

Hello,

I'm not sure that I completely understand your problem. Are Decal1 and Decal2 fields present in the same record? Do you want data in Decal1 to be unique for all records? Do you wand Decal1 to be unique not only in the Decal1 but also in the Decal2 field?

Regards,
Razvan RACASANU

Thanks.  In essence, I need to validate one field against two fields.  Decal1 field must be unique among all data in Decal1 and Decal2 fields.  Once I get this to work I will repeat it for same sinario with Decal2 (Decal2 must also be unique among all data in Decal1 and Decal2). Decal1 and Decal2 are included in each record. Thanks for your help.

I'm not sure that I completely understand your problem. Are Decal1 and Decal2 fields present in the same record? Do you want data in Decal1 to be unique for all records? Do you wand Decal1 to be unique not only in the Decal1 but also in the Decal2 field?

Yes, Decal1 and Decal2 fields are present in same record.  Data in Decal1 must be unique for all records (Values for Decal1 or Decal2 fields cannot be the same.  This is true for all records.).  Yes, Decal1 to be unique not only in the Decal1 but also in the Decal2 field.

Hello,

The check unique trigger only checks in the same column, so it cannot check if Decal1 is unique in the Decal2 column. To do this, you need to create a custom BEFORE trigger in which to manually verify this by creating inside of it a recordset. For more information on how this can be done, please read this section from our online documentation.

Regards,
Razvan RACASANU

Thanks.  I am not quit there yet and need more knowledge.  Hope you can help.

Decal1 and Decal2 trigger the correct error if their transaction values are the same when submitting the form but neither of these values seem to be compared to existing Decal1 and Decal2 column values already in database.  If I enter a value for Decal1 or Decal2 that already exists in the database for Decal1 or Decal2 columns, no trigger action occurs.  This is what I need help on.  Please note that if, for example, Decal1's value is 123456, this value must be unique for all values in columns Decal1 and Decal2.  The same is true for Decal2, also.

Here is what I added to my form and this has been set to a "Before" property...

(note: table = securevehreg and fields are Decal and Decal2)

//start Trigger_Custom trigger
function Trigger_Custom(tNG) {
<cffunction name="AliasCustomTrigger">
<cfargument name="tNG" required="true">
<cfset var query = "">
<cfset var result = "">
<cfset var myerror = "">
<cfset query = "SELECT * FROM securevehreg WHERE Decal = " & Request.KT_escapeForSql(tNG.getColumnValue("Decal"),tNG.getColumnType("Decal")) & " AND Decal2 = " & Request.KT_escapeForSql(tNG.getColumnValue("Decal2"), tNG.getColumnType("Decal2"))>
<cflock name="check_multiple_unique" type="exclusive" timeout="10">
<cftry>
<cfquery name="result" datasource="#tNG.connection#">
#PreserveSingleQuotes(query)#
</cfquery>
<cfif result.RecordCount GT 0>
<cfset myerror = Request.tNG_CreateObject("tNG_error")>
<cfset myerror.init ("There is already a decal with the same number!", ArrayNew(1), ArrayNew(1))>
</cfif>
<cfcatch>
<cfset myerror = Request.tNG_CreateObject("tNG_error")>
<cfset myerror.init ("Could not access database!", ArrayNew(1), ArrayNew(1))>
</cfcatch>
</cftry>
</cflock>
<cfif IsObject(myerror)>
<cfreturn myerror>
<cfelse>
<cfreturn Request.KT_Null>
</cfif>
</cffunction>
<cfset Request.AliasCustomTrigger = AliasCustomTrigger>
<cfscript>
//start Trigger_Custom trigger
function Trigger_Custom(tNG) {
return Request.AliasCustomTrigger(tNG);
}
Request.Trigger_Custom = Trigger_Custom;
View full message

Hello,

If you want to try to find the value in either fields, you should not use the AND condition. The WHERE clause should look like this:

... WHERE Decal1 = #Decal1# OR Decal1 = #Decal2# OR Decal2 = #Decal1# OR Decal2 = #Decal2#

where #Decal1# and #Decal2# represent the values from the transaction.

Regards,
Razvan RACASANU