Re: Checking for duplicate items, Forms4.5

In article <L56qUDA4lxN3Ew$o_at_ahardy.demon.co.uk>,   Andy Hardy <news1_at_ahardy.demon.co.uk> wrote:
> In article <7h21iv$gog$1_at_nntp.csufresno.edu>, Steve Cosner
> <stevec_at_zimmer.csufresno.edu> writes
> >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.
>
> That's close to what I want, but I'd like to tell the user which items
> are invalid for the record with a meaningful message. e.g. 'A is
> mandatory, B is a duplicate for X'. I do this with a procedure that
> checks each field of the current record and writes errors into a
record
> group.
>
> I've removed item validation and am trying to do the validation at a
> per-record level. The duplicate checking is a pain... if I follow the
> approach that you outlined then I seem to loose the individual record
> messaging and end up with a 'Record has a duplicate field, somewhere'.

[Quoted] You don't lose the individual record. The cursor ends up on the correct record if you do a Raise Form_Trigger_Failure in the on-error process. Oh, one more thing: you need to check immediately following your Post or Commit_Form (and you should do this in EVERY form):

    Commit_Form;
    If not form_success
    or :system.form_status<>'QUERY' then       Raise Form_Trigger_Failure;
    End if;

This and the 'Raise F_T_F' in the on-error trigger will cause the cursor to remain on the record where the insert/update attempt returned the ORA -00001 error.

[Quoted] Also, the ORA-00001 message includes the name of the constraint that caused the record to be rejected, so from that your form can determine which field or combination of fields are in error. I use that information to navigate to the field in error.

> It feels like I should have a POST-RECORD trigger to do the
> validation, and if it is successful then POST the current record.
> Unfortunately, POST is a restricted function...

Well, you COULD do it from a timer, but I wouldn't.

> I'm not sure what your POST call is gaining in the KEY-COMMIT. At this
> point, the user has already decided to commit and normal processing
> would raise the duplicates errors?

I said in my original message that the Commit_Form would probably work without the Post. In fact I am sure of it.

> Bother... perhaps I'm trying to be too clever and should just try and
> track the errors produced by Oracle during it's normal processing!
> But I would like to get away from item level validation...

I don't understand what is so bad about item-level validation. It gives you the most precise control, so your cursor is in the right field, and the error message fits the current problem. The only things I check at the record-level (and sometimes only after key-commit has been pressed), are required field edits. Forms doesn't do this well, so I do it 'manually' at the later time.

Steve Cosner

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 11 1999 - 22:28:28 CEST