Re: Two short questions for the gurus.....

Hi Sean,
  The answer to your first question depends on the number of different values the code may have and the volatility of the list. If it is a reasonably small number (say twenty or less), and unlikely to ever change then code a WHEN-NEW-RECORD-INSTANCE trigger along the lines of: BEGIN
  IF :schools.county = '13' THEN
    :schools.cnty_name := 'DUTCHESS';
 ELSIF :schools.county = '44' THEN

    :schools.cnty_name := 'ORANGE';
 ELSIF :schools.county = '50' THEN

    :schools.cnty_name := 'ROCKLAND';
 ELSIF :schools.county = '59' THEN

    :schools.cnty_name := 'SULLIVAN';
 ELSIF :schools.county = '62' THEN

    :schools.cnty_name := 'ULSTER';
 ELSE
    :schools.cnty_name := 'UNKNOWN';
  END IF;
END;
In the above example, schools.county is the code in the Oracle table. It is an item is the block, but is not displayed; schools.cnty_name is a text item in the block, but not part of the table, and is displayed.

   On the other hand, if there are a larger number of possible codes, or if codes may be added or changed, then the best (safest) way is to create a table containing the codes and their descriptions and SELECT the description from it. This way new codes can be added without someone having to modify the form. We have some tables of this type with as few as three records (currently) in them. Volatility can amount to a judgement call. My advice is that when in doubt, use a table; because its a pain in the neck to change a Form. In certain situations, you may even want to consider creating a simple form to allow the users to perform this maintenance themselves.

   As for your second question, the simplest route to take here is user education. Any querable item on a form may be queried using wildcards and the Forms wildcard character is the percent sign (%). For example, assume a LAST_NAME item, if the user types in SMITH% it will bring back all names that begin with SMITH (including SMITH itself), if they type in %SMITH it will bring back all names ending with SMITH (again including SMITH itself), and if they type in %SMITH% it will bring back all names containing SMITH anywhere in them (once again including SMITH). The user can even query on several items at the same time using wildcards in all of them (say FIRST_NAME = 'S%' LAST_NAME = 'D%' might bring back your name as well as any other persons with the initials S. D.). Why write code to do something Oracle does for you.

Hope this helps,
Paul

In article <3842c443_at_news.nwlink.com>,   "Sean Denney" <sean.denney_at_ichristian.com> wrote:
> Hi,
>
> Can anyone help me with a couple of forms questions.
>
> 1. How can I translate a code into a description for a forms field so
 that
> when a user queries the code field, they see the description. Do I
 need to
> create a new field or can I do it with a mask or something?
>
> 2. How do you automatically add wildcards to a user's query? Do you
 use a
> pre-query trigger?
>
> Any help would be great!
>
> Sean
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 29 1999 - 00:00:00 CET