Re: Default values in WebDB

1. Create a PL/SQL function that accepts a user_id and returns the appropriate company_id:

create or replace function Company_Lookup (p_user_id in number) return number as

    v_company_id number;
begin

    select company_id into v_company_id from users where user_id = p_user_id;

    return v_company_id;
end Company_Lookup;

2. In the Default Value field for company_id on your Parts form, call this function as follows:

#Company_Lookup (user_id)

If you encounter this situation a lot, it may be useful to create a 'generic' lookup function where you pass it the table name and column name to query, the value to query for and the column name to return and then use dynamic SQL to generate the query:

create or replace function Generic_Lookup (p_table_to_query in varchar2, p_col_to_query in varchar2, p_value in number, p_col_to_return in varchar2) return number as

    v_value number;
begin

    /* pseudo code follows, use dynamic SQL, either DBMS_SQL in Oracle7/8 or EXECUTE IMMEDIATE in Oracle8i */

    select p_col_to_query into v_value
    from p_table_to_query
    where p_col_to_query = p_value;

    return v_value;
end Generic Lookup;

I've also taken the approach of creating views which 'flatten' my relationships. Then you can build the WebDB forms on the views rather than the base tables.

Hope this helps,

Tim Taylor

Edward Beemer <ebeemer_at_tas.nl> wrote in message news:7pr3i2$avv$1_at_zonnetje.nl.uu.net...
> Hello guys,
>
> Consider a table USER_INFO with the fields USERNAME and COMPANY_ID.
> Consider a table PARTS with the fields COMPANY_ID, PART_NO, DESCRIPTION,
> where COMPANY_ID + PART_NO form the primary key.
>
> When a user inserts a new PARTS record, I want the COMPANY_ID field to be
> filled by default with the COMPANY_ID for the user as defined in the
> USER_INFO table.
>
> How do I get this done?
>
> With kind regards,
> Edward Beemer
>
>
>
Received on Mon Aug 23 1999 - 16:49:48 CEST