Table fields from other tables Also called Non-Base Table Fields. In the example of CUSTOMER, the Sales Area is known by number, but not the description of the Sales Area. This has to be obtained from another table, SALESAREA.
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Also called Non-Base Table Fields.
In the example of CUSTOMER, the Sales Area is known by number, but not the description of the Sales Area. This has to be obtained from another table, SALESAREA.
A trigger needs to be created that will insert the sales area description when the sales area number appears.
i.e. AreaName from SALESAREA to the CUSTOMER data block
Highlight CUSTOMER. Right-click and invoke the Layout Editor
Click on the Text Item tool. This will create a new field in the data block
A new item, TEXT_ITEM6 has been added. The same height and y-coordinates as Sales Area can be obtained by using the property palette.
In the Property Palette, change the name to DESCRIPTION. y-coordinates as Sales Area can be obtained by using the property palette.
The name has been changed to DESCRIPTION. y-coordinates as Sales Area can be obtained by using the property palette.
In the Property Palette, make the following changes. y-coordinates as Sales Area can be obtained by using the property palette.
Keyboard Navigable: No – prevents the users tabbing into this field
Database Item: No – i.e. does not map to an attribute in the CUSTOMER table
Query Only: No
Primary Key: No
Insert Allowed: No
Query Allowed: No
Update Allowed: No
Prevents any of the actions from happening
The new data item, DESCRIPTION, has now been added to the data block.
But it is not yet known where to get the data to fill this field.
This is done by creating a trigger.
There are different types of triggers upon the execution of an event:
1. PRE – the trigger fires before the event.
2. POST – the trigger fires after the event.
3. WHEN – the trigger fires in place of the event execution.
4. KEY – the trigger fires when a certain key is pressed.
In this example the trigger will fire after the Sales Area field changes in the CUSTOMER data block.
This requires that a POST-CHANGE trigger is created on the :CUSTOMER.CompanySalesArea field.
This is performed by first opening the Object Navigator
The colon indicates that it comes from a data block
Within the CUSTOMER data block, highlight COMPANYSALESAREA, and then click on Triggers
The following pop-up appears. Scroll down to POST_CHANGE and then click on Triggers
POST_CHANGE has been selected and then click on Triggers
The PL/SQL Editor. The code for the trigger is now entered. and then click on Triggers
The CUSTOMER data block and the COMPANYSALESAREA field
Once the code is entered, compile the PL/SQL code and then click on Triggers
Enter the following code
This shows that the PL/SQL has not yet been compiled.
The code is being modified
Once compiled, EXIT and then click on Triggers
The code is not being modified
The code has been compiled
There now is a POST_CHANGE trigger for COMPANYSALESAREA and then click on Triggers
Compile and run the CUSTOMER form and then click on Triggers
The Sales Area description now appears. Try modifying or deleting the field. It should not be allowed if the previous steps were performed correctly
The previous LOV just displayed different sales areas by number.
The new LOV should also show Area Name.
LOV shown from previous slide show. This LOV should be deleted to be replaced by the new LOV
Highlight both of these items and then delete them by: Edit, Clear.
Choose the LOV Wizard enter Create
Enter this SQL statement. Notice that this is a join because items from two tables are needed.
Include both CompanySalesArea and AreaName items from two tables are needed.
Change the names as shown items from two tables are needed.
Choose Automatically size columns and click Look up return item
Each return value has to be looked up separately. items from two tables are needed.
The return value for Number is Customer.CompanySalesArea.
The return value for Name is Customer.Description
Enter the name as shown items from two tables are needed.
Retrieve 6 records items from two tables are needed.
Both return items are assigned to the LOV items from two tables are needed.
The LOV is created items from two tables are needed.
Compile and run the form
The LOV showing the six sales areas items from two tables are needed.