0858-0861

Previous Table of Contents Next

Page 858

Table 35.3. continued

Property	Value
Width	200
Height	200
Database Table Item	False

These fields should be populated with data whenever an ITEMS record is queried. To do this, create the POST-QUERY trigger on the ITEMS block as follows :

 begin    select pl.pl_name, pc.pc_name, prod.prod_name, cp.cp_name, s.size_desc       into :items.pl_name, :items.pc_name, :items.prod_name, :items.cp_name,              :items.size_desc       from product_lines pl, product_classes pc, products prod,               color_patterns cp, sizes s     where pl.pl_id = pc.pc_pl_id         and pc.pc_pl_id = prod.prod_pl_id         and pc.pc_id = prod.prod_pc_id         and prod.prod_pl_id = :items.item_pl_id         and prod.prod_pc_id = :items.item_pc_id         and prod.prod_no = :items.item_prod_no         and cp.cp_no = :items.item_cp_no         and s.size_code = :items.item_size_code; exception    when NO_DATA_FOUND then       message (`Database Integrity Error. Contact your DBA.');       bell;       raise FORM_TRIGGER_FAILURE; end; 

NOTE

This trigger was written this way because in a client/server application, a single query is much more efficient than multiple independent queries. Because each of the five fields that must be loaded can be obtained through a unique row query, they can be merged in a single query step. Another (and probably better) approach is to create a view that retrieves these values in the initial query. You can always use a view as the base table for a block. The exception step introduces a few new concepts regarding triggers. The message built-in is used to send a message to the user screen and the bell built-in plays an audible beep from the user 's terminal. Also, the FORM_TRIGGER_FAILURE exception that is raised prior to exiting the trigger causes all processing to be aborted on failure.

Now, you should define the lists of values for each of the fields. To create a list of values, select LOVs in the Object Navigator and click the Add Object button. The new LOV dialog appears as shown in Figure 35.16. In the Query Text box, type in the query against the PRODUCT_LINES

Page 859

table as shown and click the OK button when finished. This creates a new record group and associates it with the new LOV. (Note that you can also create a list of values based on an existing record group.)

Figure 35.16.
Creating a new list of
values.

Now, select the new LOV and its property sheet. Change the name of the LOV to PRODLINE_LOV and select the Column Mapping property in the property sheet. A button appears in the Value Edit box with the label More. Click this button to display the column mapping dialog as shown in Figure 35.17. The column names from the query are displayed in a table with the characteristics for the column shown below the table. To hide the ID column from the display, set the display width equal to 0. Select the PL_NAME column, set its display width to 150, and change the column title to Product Lines. Click OK to complete the column mapping. Attach this LOV to the PL_NAME field in the ITEMS block by selecting its property sheet and scrolling toward the bottom to the Miscellaneous Properties section. Change the LOV property to PRODLINE_LOV and set the LOV X Position and LOV Y Position to 100 and 50. Finally, set the LOV for Validation property to True. This causes the form to make sure that the value entered is valid without writing a validation trigger. (To ensure data integrity, you can write a WHEN-VALIDATE-ITEM trigger for this field to "null out" the Product Class and Product Name fields whenever the Product Line is changed.) Create a list of values for each of the remaining non-database fields. (Use the POST-QUERY trigger to determine the columns and tables for the mapping.)

Figure 35.17.
Column mapping for a
list of values.

Page 860

Finally, to complete the form, create a PRE-INSERT trigger on the items block to select the next ITEM_SEQ value from the sequence generator as the value for a new ITEM_NO. You should then save and generate the form to test this concept. A feature to note is that the list can be activated using the List Values key (F9 in most standard IBM PC configurations; to see a list of defined keys, select Help Show Keys from the menu). The validation feature allows the user to type only part of the field name to narrow the list. For example, type T into the Product Line field and then hit the Tab key. The complete name, Terminal Tackle, is entered in the field.

Using Relationships to Link Separate Blocks in a Form

So far, all the examples discussed here use only a single block for data. The key feature of Oracle (or other relational databases) is that the tables are related to each other by key fields. An example of the use of related tables is shown in Figure 35.18 in the Order Entry form.

Figure 35.18.
Order Entry form.

To construct this form, first create a default block for the Orders table using the form layout style. Create non-database fields for customer name, address, and city using the Display Field tool. (A display field is used to display data but does not need to provide input capability.) Also, create a list of values for the customer number field and define the order number and order date fields as non-navigable. (These fields are populated in a PRE-INSERT trigger.) You should write a POST-QUERY trigger to populate the customer information.

Now, create a second block for the order_items table using a vertical tabular style that displays five rows of data. Modify the properties for the OI_ITEM_NO field so that it is not displayed and is assigned to the null canvas. The only field that remains on the canvas is OI_QTY. Create

Page 861

non-database fields in this block for CATALOG_NO, ITEM_DESC, LIST_PRICE, and ITEM_TOTAL using the Display Field tool. Also, create a numeric, non-displayed field ITEM_PRICE on the null canvas. Create a POST-QUERY trigger for the ORDER_ITEMS block as follows:

 begin   select i.item_pl_idi.item_pc_id'-'          ltrim (to_char (i.item_prod_no, `099999'), ` `)'-'          ltrim (to_char (i.item_cp_no, `09'), ` `)'-'          i.item_size_code,          p.prod_namedecode (cp.cp_name, `N/A', ` `,' `cp.cp_name' `) s.size_desc,          i.item_price     into :order_items.catalog_no, :order_items.item_desc, :order_items.item_price from items i, products p, color_patterns cp, sizes s    where items.item_no = :order_items.oi_item_no      and p.prod_pl_id = i.item_pl_id      and p.prod_pc_id = i.item_pc_id      and p.prod_no = i.item_prod_no      and cp.cp_no = i.item_cp_no      and s.size_code = i.item_size_code;   :order_items.list_price := ltrim(to_char(:order_items.item_price,                                      `990.00'), ` `); :order_items.item_total := ltrim ( to_char                              ((:order_items.oi_qty*:order_items.item_price),                                          `990.00'), ` `); end; 

TIP

Oracle Forms does not allow for a format mask on display items. For that reason, the fields should be defined as character fields and the item should be formatted using PL/SQL. Using right justification, you can display the fields so that the individual digits are properly aligned.

To complete this block, create a list of values for the catalog number field with a WHEN-VALIDATE-ITEM trigger to display the list price and item total fields.

Finally, to complete this form, select the Relationships group under the Orders block. Click the Add Object button to display the Relation dialog as shown in Figure 35.19. Modify the relation name to order_item_rel and define the detail block as ORDER_ITEMS. Next, define the logic that should be followed if a master record is deleted. In this case, select a cascading delete. (All detail records are deleted if the associated master record is deleted.) The block coordination should be defined so that the detail query is immediate (Deferred is off), and the user should not be able to navigate to the detail block unless a record exists in the master block.

Previous Table of Contents Next