HELP: Tables with unique columns in Forms 3.0

Is there a way of being able to enforce unique values in a Forms 3.0 block?

For example, we have a table (gen_dedn) containing 4-letter codes (gen_dedn_code) with an associated numeric code (gen_dedn_pri). The numeric codes must be unique.

We currently have a Forms 3.0 form that has a block with gen_dedn as its base table. The user can change the numeric values in gen_dedn_pri and then at the end, a check for duplicate values selects any values from the table gen_dedn that have the same values to those that have changed.

The problem we are having is this. Suppose the first 2 records, in the database, have the numeric values of 3 and 4. The user uses the Form to change these to 4 and 3 respectively and commits the changes. The check then recognises the first record has changed to 4, it checks the database for anything else with a value of 4 and discovers the second record, still with its value of 4 (hasn't been updated in the database yet). So, even though on the screen all the values are unique, it displays an error saying duplicate values exist. The only way around it is to delete one of the records, then change the other, then re-create the first one with the new value. Messy.

I cannot work out any easy way of setting the Forms 3.0 form up to allow the user to modify the records in the table gen_dedn, and only allow unique values in the gen_dedn_pri column. I tried using a database constraint to force the column to be unique, and removed our check. That still produced a similar error because the second record hadn't yet been changed to 3 so it figured there were 2 records containing the value 4. Surely there must be systems out there where tables can be maintained by users, with columns with unique values that can be changed?

Thanks in advance for any help.

Mark Received on Wed Feb 04 1998 - 00:00:00 CST