slide1
Download
Skip this Video
Download Presentation
Table fields from other tables Also called Non-Base Table Fields.

Loading in 2 Seconds...

play fullscreen
1 / 34

Table fields from other tables Also called Non-Base Table Fields. - PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Table fields from other tables Also called Non-Base Table Fields.' - milo


An Image/Link below is provided (as is) to download presentation

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.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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.

A trigger needs to be created that will insert the sales area description when the sales area number appears.

slide2

Adding a new field from another table to an existing data block

i.e. AreaName from SALESAREA to the CUSTOMER data block

Highlight CUSTOMER. Right-click and invoke the Layout Editor

slide3

The description needs to be added here.

Click on the Text Item tool. This will create a new field in the data block

slide4

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.

slide7

In the Property Palette, make the following changes.

Enabled: Yes

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

slide8

Notice some of the changes made here for the DESCRIPTION field

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.

slide9

Creating a trigger for the DESCRIPTION field

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

slide13

The PL/SQL Editor. The code for the trigger is now entered.

A POST_CHANGE

The CUSTOMER data block and the COMPANYSALESAREA field

slide14

Once the code is entered, compile the PL/SQL code

Enter the following code

This shows that the PL/SQL has not yet been compiled.

The code is being modified

slide15

Once compiled, EXIT

The code is not being modified

The code has been compiled

slide18

The Sales Area description now appears. Try modifying or deleting the field. It should not be allowed if the previous steps were performed correctly

slide19

A new LOV can now be created when the Customer Detail frame is displayed.

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.

slide22

Enter this SQL statement. Notice that this is a join because items from two tables are needed.

Click Next

slide24

Change the names as shown

Choose Automatically size columns and click Look up return item

slide25

Each return value has to be looked up separately.

The return value for Number is Customer.CompanySalesArea.

The return value for Name is Customer.Description

Click Next

slide30

The LOV is created

Compile and run the form

ad