Re: Checking for duplicate items, Forms4.5

In Forms, your base-table block has a pseudo-column :MYBLOCK.ROWID already available, so you can check that against the database when you look for duplicates. But doing that will not find newly created duplicates in your block that are not yet posted.

[Quoted] [Quoted] I have recently worked on the problem of looking for duplicates in a form. Using an on-error trigger and a database constraint made the solution pretty easy.

First in the key-commit process, I did a Post command, followed by checking: If not form_success or :system.form_status<>'QUERY' then Raise Form_trigger_failure; This terminated the commit processing if an insert or update failed due to a duplicate key. If there were no duplicate key problems, the process continued to a commit_form.

Using commit_form instead of post would probably work as well, now that I think about it. In either case, if the user decides to abandon all changes, we have a clear_form(no_validate,full_rollback) that is called from several places. It will back out any changes that might have occurred up to the duplicate record error.

In a form-level On-Error trigger, I used the following code:

• On-Error form-level trigger. Special processing here to trap
• duplicate key errors on insert and update. DECLARE ERR_VAL NUMBER(5) := ERROR_CODE; MSG VARCHAR2(150) := SUBSTR(' '||ERROR_TYPE||'-' ||TO_CHAR(ERR_VAL) ||': '||ERROR_TEXT,1,150); BEGIN
  • FRM-40508 = unable to update, FRM-40509 = Unable to insert
  • DBMS_ERROR_TEXT is the ORA-... error msg from the server IF ERR_VAL in (40508, 40509) AND INSTR(DBMS_ERROR_TEXT,'I_PK_OPTTERM')>0 THEN--unique constraint MESSAGE(' *** ERROR *** DUPLICATE PERIOD'); ELSE MESSAGE(MSG); --used for all other error messages END IF; RAISE FORM_TRIGGER_FAILURE; END;

    ---

When you use the above method, your form will automatically navigate to the correct row where the duplicate occurred. We have another timer routine we use to navigate to the proper field and set its color to red when the message is issued.

Steve Cosner
http://members.aol.com/stevec5088

In article <nuW7pKARivM3EwyZ_at_ahardy.demon.co.uk>, Andy Hardy <news1_at_ahardy.demon.co.uk> wrote:
>In article <7gt0nk$smv$1_at_news5.svr.pol.co.uk>, Simon Hedges
><shedges_at_hhhh.freeserve.co.uk> writes
>>You should be able to check the data in the Database as follows
>>
>>select MYTABLE.MYTEXT from MYTABLE where MYTABLE.MYTEXT =
>>:MYBLOCK.MYTEXT and :MYTABLE.MYKEY <> :MYBLOCK.MYKEY
>>
>>This will return any row which exactly matches the current text from the DB,
>>EXCEPT for the current row.
>
>Oh dear, sometimes I just look for the hardest way to do these things!
>Thanks!
>
>Can you confirm that Forms does nothing itself to check for uniqueness,
>and that if I relied on the normal response I'd just get the 'cannot
>INSERT' message with no indication of the field causing the problem?
Received on Sat May 08 1999 - 20:59:43 CEST