Re: Forms query on a non base table item

Bob

This my top FAQ.

Step 1:

Approach 1 :

Create a stored (server) function that does the look up.

In this case the function can look up the customer_name from customer_code using a cursor.

Create a view based on the order table columns and the function.

Approach 2:

Create a view based on the join of orders and customers tables with the required fields from both tables.

Step 2.

In the form:

Change the block data source from the table to either of the above views.

Eliminate the post query that previously looked up the customer name.

Change the customer_name item property to base table and query only.

Now the field will be queryable. You can also order by the now base table customer_name field if desired.

Elimination of the post query save communication traffic from the client to the server.

See Functional Magic paper by John C. Lennon at:

http://members.aol.com/jomarlen2/oraclesh.html

See Mary Lett's paper on updatable views:

at: WWW.ODTUG.COM

1998 Handouts / Developer / Using Modifiable Join Views in Forms

HTH Mark

Bob Hirons wrote in message <3715F801.7CDA5D7_at_hotmail.com>...
>How do I code a form to allow query on a non base table item?
>
>e.g.
>base table ORDER has-
>ORD_NO
>ORD_DESC
>CUSTOMER_CODE
>
>table CUSTOMER has-
>CUSTOMER_CODE
>CUSTOMER_NAME
>
>form has items
>ORD_NO base table item
>ORD_DESC base table item
>CUSTOMER_CODE base table item
>CUSTOMER_NAME non base table item
>
>Wish to allow query on CUSTOMER_NAME.
Received on Sun Apr 18 1999 - 17:03:36 CEST