I have a good one here and i am hoping there is an answer for this.
I have a form which displays a profile. Each profile has multiple rows of data, each row consists of a sort order, an operation name and a delete flag, which when saved runs through a loop in a cfc and inserts into the db, no problem there.
There is a business rule that the Operation name field must not be duplicated within each profile.
Now i can write code to check for a duplication of an operation when a new operation name is created, no problem.
What i cant seem to get my head around is to check to make sure that there are no duplicates in any of the rows.
What i first did was create a select statement to check for duplicates and then if the select statement returned a recordcount greater than 0 it would return back an error.
Of course this doesnt work because each form value on the screen already exists in the db so every value was handled as a duplicate.
The only way i can think of doing it is to write a cfc that deletes the existing values from the table and creates a new values and at the point of creation checks for duplicate values, if there are dups, then do a transaction roll back and inform the user, i know its a bit wild but looking for any other ideas on how to handle this.