Re: Q: Forms and non-database fields

ezharkov_at_dxcern.cern.ch (Eugeny Jarkov) writes:

>I am using "SQL*FORMS_VERSION = 03.00.16.04.02".
 

>My block contains a few non-database fields. I do query on non-database field
>using the technique which is described in "Advanced SQL*Forms Techniques".
>I am trying to find out a way to allow not only query, but input into these
>non-database fields.
 

>table T1 (ID number, F1, ...);
>table T2 (ID number, NAME char(10));
 

>block B1 with the base table T1 (so "NAME" is non-database field).
 

>ID NAME F1 ......
>____ __________ _____ ......
 

>I allow input into "ID" and "NAME". For "NAME" field I use
>"ON-VALIDATE-FIELD" trigger:
 

> select ID from T2 where NAME=:B1.NAME
 

>I also use "POST-QUERY" trigger:
 

> select NAME from T2 where ID=:B1.ID

Use the post-change trigger instead (as a field trigger) on the ID field. When altering a value in a post-change trigger the validating will not fire. (I think you need to have the validation-scope set to field)

Another possibility: select name into a temporary variable and use the copy packaged procedure to copy the name into the field. The copy-procedure will not trigger the validation either.

>The problem:
> NAME is selected in the POST-QUERY trigger,
> ON-VALIDATE-FIELD fires,
> ID is selected in the ON-VALIDATE-FIELD,
> block B1 is marked as CHANGED.
 

>The questions:
> Is it possible to prevent ON-VALIDATE-FIELD from firing?
> Is it possible to setup that "No changes to commit"?
> Is there another technique to do this?
 

>Thanks!

Another way to do it is to make a view and then make the needed on-lock, on-update, on-insert and on-delete triggers. It is more cumbersome to do, but results in a faster form as the database is only queried once for each row and not twice (or more).

What you need (and I need also) is a on-altered-by-user trigger.

If your name-coloumn isn't unique you will encounter a lot of other problems. I have had a similar problem with displaying 2 'functionel dependent columns' and allowing for updates in both. I usually 'solves' this problem by not allowing user-update in the ID-field, but when validating the name-field I allow an ID-entry (my post-change look something like: 'select id, name into :id, :name from t2 where id like :name or :name like name' and further I handle the exeption off too-many-rows with an 'auto list-of-values'). This is normally not a big problem, as the ID and the NAME will not have confliction namespaces.

Hope this helps

| Anders Harder (harder_at_daimi.aau.dk) I learn mostly from my failures...      |
| Comp. Sci. Dept.| Private address:  it isn't that I don't learn from my     |
| Aarhus Univ.    | Ydunsvej 12       successes...                            |
| DK-8000  Aarhus | DK-8230 Aabyhoj   but I have more failures than successes!|
| Denmark         | Denmark           (Rune T. Kidde)                         |
-------------------------------------------------------------------------------