Chapter 5: Introduction to Forms Builder

1
Chapter 5 Introduction to Forms Builder

• Jason C. H. Chen, Ph.D.
• Professor of MIS
• School of Business Administration
• Gonzaga University
• Spokane, WA 99258
• chen_at_jepson.gonzaga.edu

2
Why Forms? Why SQLs?

• It is not practical to expect users to regularly
  create SQL queries to interact with a database.
  Instead, users use applications called forms.
• A form allows a user to easily interact with the
  database by inserting, updating, viewing, and
  deleting data.
• The IT/IS professionals (like you) use an Oracle
  utility (i.e., Developer Suite) such as Form
  Builder to create Windows-based (and Web-based)
  form.

3
Form Builder
MODULE
MODULE
Canvas
Libraries Packages Objects
Data Block Data Block
see

Item
Item
interface
Relation

Item
Item
Form a group of objects, screens, programs,
menus, data, etc. - is stored in a separate file
on a system, or - as a separate object in a
database
text field labels
coding
Data Base

if multiple tables involved
4
How to do the Tutorials

• Before you start, you should refresh your two
  databases. (How?)
• You must complete all the tutorials in Chapter 5.
• While you are working with tutorials, you are
  strongly recommended that save the work up to an
  appropriate stage (e.g., save the form) with the
  name file name along with the page number. e.g.,
  5AOrders280.fmb
• Why?

5
Registering Oracle File Extensions and Installed
Folders (Select View then folder options then
) 1 .FMB Form Builder (p. 261)
oracle\oraDev\bin\ifbld60.exe .FMX Form
Module Executable oracle\oraDev\bin\ifrun60.exe
Form Runtime oracle\oraDev\bin\ifdbg60.exe
Form Compile oracle\oraDev\bin\ifcmp60.exe
Program ---? Compile
---? Run Web Layout (from Browser)
---? Run Paper Layout (from within Report
Builder) 2. .RDF Report Binary (p.513,523)
oracle\oraDev\bin\RWBLD60.exe
.REP(executable file) oracle\oraDev\bin\RWRUN6
0.exe .jsp (Report JSP) .XML
.html 3 .OGD Graphics Builder (design
file) oracle\oraDev\bin\GOBLD60.exe
.OGR Graphics Runtime oracle\oraDev\bin\GOR
UN60.exe
6
Lesson A Objectives

• Learn how Forms Builder displays forms in a Web
  browser
• Use a data block form to view, insert, update,
  and delete database data
• Create a data block form that displays a single
  record at a time
• Become familiar with the Object Navigator, and
  use the Object Navigator to change form object
  names
• Use the Data Block and Layout Wizards to modify
  form properties
• Create a tabular-style data block form that
  displays multiple records

7
Displaying Forms in a Web Browser

• Developer10g displays forms as Web pages
• In Web browser
• Form appears in Forms Services window within
  browser window
• Forms Services application displays menus and
  toolbar
• Provide general functions for all form
  applications

8
Displaying Forms in a Web Browser
9
Figure 5-2 Web-Based Client/Server Database
Architecture

• Network

10
Forms Builder to Browser
11
Using A Data Block Form - Definitions

• Block group of related form items, such as text
  fields and option buttons
• Data block
• corresponds to a specific database table, and
• contains objects, such as text fields or option
  buttons, that display values from the tables
  data fields
• Data block form one or more data blocks
• Text items display text input fields
• Form menu
• Contains selections to manipulate form

12
Start OC4J Instance Window
(You have to spend much time practicing all
examples in this chapter to gain your own
experience)

• Start SQLPlus and log onto the database
• SQLgt _at_ c\oradata\chapter5\northwoods.sql
• SQLgt _at_ c\oradata\chapter5\clearwater.sql

• Then start OC4J Instance
• Click Start then Programs
• Click Oracle10g then Oracle10g Developer Suite
• Click Forms Developer then Start OC4J Instance
• A command window with the title Start OC4J
  Instance opens (see next slide)
• 4. Minimize the Start OC4J Instance Window

13
(No Transcript)
14
Start Forms Builder

• Click Start then Programs
• Click Oracle then Oracle10g Developer Suite
• Click Forms Developer
• Click Forms Builder
• The Form Builder development environment opens.

• Next, to open the Location form
• Click File on the menu bar
• Click Open, navigate to the Chapter5 folder on
  data disk (your floppy or coradata\ )
• Select Loation.fmb (from the folder of
  oradata\chapter5\...)

15
To Run the Form
1. Click Program on the menu bar 2. Click Run
Form A dialog box open, and asks if you
want to log on before compiling the from 3. Click
Yes and enter your connection information 4.
Finally, a form service window is
displayed. Notice that you should be patient
since some processes may be very slow due to
connecting to many components.
16
Forms Services Window

• Form menu contains selections that allow you to
  manipulate the form
• Has a toolbar with buttons to insert, view,
  modify, and delete records
• At bottom of the window are lines that display
  information about form status
• Query button group allows you to query data, has
  two modes
• Normal mode - can view data records, sequentially
  step through the records, and change data values
• Enter Query mode - can enter a search expression
  in one of the form fields, and then retrieve the
  associated records

17
Forms Services Window
18
Using a Form to View Table Records

• Retrieve specific table records
• Click the Enter Query button
• Type a search expression in one or more of the
  form text items
• Click the Execute Query button
• Retrieve all of the records in a table
• Place form in Enter Query mode and do not type
  search condition

19
(No Transcript)
20
Closing a Form and Committing Changes

• To close a form
• Click Exit button
• Click Action on the form menu and then click Exit
• Close browser window
• If unsaved changes exist, dialog box will prompt
  to save changes

21
Homework - Practice and complete Chapter 5
tutorials up to p.297 (all detailed steps are
available on my powerpoints) Add your personal
information on the bottom of the layout. Save
your work as 5AFaculty_Lname_Fname.fmb and email
it to me by next Monday with subject title of
bmis441_Oracle5A or mbus673_Oracle5A
22
Form Builder
MODULE
MODULE
Canvas
Libraries Packages Objects
Data Block Data Block
see

Item
Item
interface
Relation

Item
Item
Form a group of objects, screens, programs,
menus, data, etc. - is stored in a separate file
on a system, or - as a separate object in a
database
text field labels
coding
Data Base

if multiple tables involved
23
Hierarchical Structure of Form Components
Example of objects Painting
Form
Application
Window(s)
Frame
Canvas -2
Canvas -1
Canvas
Data Block-1
Graphics
Area of painting
Frame
Data Block-2
Note that a block contains triggers and relations
Sub-frame1
Sub-frame2
Items
24
Creating a Data Block Form (p.275)

• When creating a form for a table, Forms Builder
  automatically creates
• Labels
• Text entry fields
• Insert, update and delete and select queries
• Three steps to creating a data block form
• Creating a Data Block
• Creating the Form Layout
• Using the Layout Editor

25
Using a Form to Insert, Update, and Delete
Records

• To insert
• Use blank record that appears when opening a form
  or as last line of query results
• To update
• Retrieve record with a query, modify field and
  save
• To delete
• Retrieve record with a query, use Remove Record
  button to delete

26
Viewing and Interpreting Form Errors

• Some errors are detected, or trapped, within the
  form and form issues the error message
• Other errors are not detected until the form
  sends the data values to the database and Oracle
  DBMS issues the error message
• To view information on database errors
• Click Help menu, Display Error
• Dialog box displays error information

27
Viewing Information About Database Error
28
The Object Navigator
29
Steps to Creating a Data Block Form (p.277)

• Three steps to creating a data block form
• Creating a Data Block
• Creating the Form Layout
• Using the Layout Editor

30
Creating a New Data Block Form

• Create a new form module
• Create a new data block using
• Data block wizard
• Manual process
• Using Data Block Wizard (next slide)
• Select table name
• Move editable fields from Available Columns list
  to Database Items list
• Use Layout Wizard to create form layout

Practice p.278
31
(No Transcript)
32
Creating a Data Block - Steps 1,2 (p.279)
33
Step 3 (p.280)
34
Steps 3,4 (p.280)
35
Step 5 (p.281)
36
Step 5 continued, Figure 5-12 (p.281) Step 1
(p.282)
37
Step 2 Figure 5-13 (p.282) Step 1 (p.283)
(cleared, not selected)
38
(No Transcript)
39
(No Transcript)
40
Creating the Form Layout (p. 285) Step 1 (p. 287)
41
Creating the Form LayoutTypes of Canvas (p.287)

• Content canvas
• fill the entire window
• Stacked canvas
• two or more canvases are stacked in a window
• Toolbar canvas
• Has horizontal or vertical toolbars that program
  commands can display or hide
• Tab canvas
• Enables different related canvases to appear on a
  single tab page

42
Creating the Form LayoutStyle Page (p.278)

• Style page allows you to specify the layout style
  and properties, to determine how the data are
  displayed.
• Form-style layout only one record is displayed
• Tabular-style layout multiple records are
  displayed on the form with an optional scroll bar
  can be selected.

43
Step 1 - Layout Wizard (p.286)
44
Steps 2,3 - Layout Wizard (p.288)
45
(No Transcript)
46
Step 4 Modifying the prompts (p.290)
47
Step 6 - Layout Wizard (p.290)
48
Form-style layout only one record is displayed
Steps 6,7 - Layout Wizard (p.290)
49
Steps 8,9 (p.291)
50
Step 9 (Result of creating a Form Layout Figure
5-21, p.291)
51
(No Transcript)
52
Save your work , or

• Click Save button on the Forms Builder toolbar
• Navigate to the chapter5 folder, and save the
  file as Ch5AFaculty-p293.fmb (default file
  extension is fmb)

53
Layout Editor

• Use to customize form display
• Can add images and static text to enhance form
  appearance

54
Layout Editor
55
Running a Form

• Click Run Form button
• Form is compiled into a .fmx file for execution
• A text file with same name as form and .err
  extension holds errors that occur when running
  form

56
Form Components and the Object Navigator

• Object Navigator
• Hierarchical display of all form components
• Different views

57
Viewing Form Components

• Expand All and Collapse All
• Icons indicate node type
• Items
• Text items
• Trigger
• Event
• Relation

58
Object Navigator Views

• Ownership View
• Visual View

59
Object Navigator Window in Visual View
60
Changing Object Names in the Object Navigator

• Change default names to descriptive names

61
Modifying Forms Using the Data Block Wizard and
Layout Wizard

• Reentrant mode
• Modify existing data block or layout
• Select data block or form before opening wizard
• All pages appear at once

62
Creating a Form to Display Multiple Records

• Tabular layout
• Display multiple records on same form
• Select Tabular on Style page of Layout Wizard

63
Northwoods University Faculty Multiple Record Form
64
Lesson A Summary

• Form
• Provides graphical view of database data
• Use to view or modify data
• Appears as Web page in browser
• Components
• Data block
• Layout

65
Lesson A Summary (continued)

• Forms Builder environment
• Object Navigator
• Data Block Wizard
• Layout Wizard
• Layout Editor

66
Lesson B Objectives

• After completing this lesson, you should be able
  to
• Create a data block form that is based on a
  database view
• Modify form properties to improve form appearance
  and function
• Create a master-detail form that contains
  multiple data blocks
• Format form text items using format masks

67
Creating a Form Based on a Database View

• Create data block forms based on views
• Can only use to view database data
• Not edit
• Use wizards
• Just like form based on table

68
Modifying Form Properties

• Modify properties of data block forms
• Property Palette
• Displays list of properties and values
• Right-click object to launch
• Toolbar
• Context bar
• Property node

69
Property Palette
70
Modifying Frame Properties

• Update Layout property
• When using Layout Wizard in reentrant mode
• Manual formatting lost

71
Modifying Text Item Properties Using the Property
Palette

• Intersection mode
• Values appears as when objects have
  different values
• Text item properties
• Control how users enter data values
• How form displays data values
• Hint
• ToolTip

72
Text item Appearance Properties
73
Text Item Data Value Properties
74
Text Item Database Properties
75
Creating a Data Block Form That Displays Data
from Multiple Tables

• Master-detail relationship
• One database record can have multiple related
  records through foreign key relationships
• Data Block Wizard
• Create master block
• Create detail block

76
Layout of Faculty Advisor Form
77
Creating the Detail Data Block

• Master-Detail page
• Auto-join feature of Data Block Wizard
• Create link manually
• Click Create Relationship
• Explicitly select related items
• Join Condition field syntax
• detail_block.join_item master_block.join_item

78
Running the Master-detail Form

• Put insertion point in master block before
  executing query
• Add, update, delete, and query database records
  in master-detail form
• Just as in single-table form
• Only insert, update, delete, or query records in
  block currently selected and displaying insertion
  point

79
Adding Another Detail Data Block to the Form

• Data block can be master block in multiple
  master-detail relationships
• Data block can also be detail block in one
  master-detail relationship
• And master block in second master-detail
  relationship
• Use Data Block Wizard
• To create new data block that contains new
  information

80
Using Format Masks to Format Character Strings

• Format mask
• Same as format model
• Set using Property Palette
• Proceed with FM

81
Lesson B Summary

• Create data block from view
• Modify properties using Property Palette
• Text item properties
• Master-detail relationship
• Create master block first
• Specify relationship automatically or manually

82
Lesson C Objectives

• After completing this lesson, you should be able
  to
• Use sequences to automatically generate primary
  key values in a form
• Create lists of values (LOVs) to provide lists
  for foreign key values
• Describe the different form items that you can
  use to enter and modify data values

83
Using Sequences to Generate Primary Key Values

• Users may make mistakes entering primary key
• Use sequence to generate key
• Create sequence in database

84
Creating Form Triggers

• Form trigger
• Program unit in form that runs in response to
  event
• Associated with form object
• Event
• User action or system action
• Fire
• Execute trigger

85
Creating Form Triggers (continued)

• Triggers dialog box
• Shows all block events that can have associated
  triggers
• Search using wildcards
• PL/SQL Editor
• Environment for writing, compiling, and editing
  PL/SQL programs
• Source code pane
• Status line

86
Triggers Dialog Box
87
Creating Form Triggers (continued)

• Use implicit cursor to retrieve records
• Reference form text item in PL/SQL program
  syntax
• block_name.item_name
• Compile trigger

88
Correcting Common Trigger Syntax Errors

• Common errors include
• Syntax errors such as referencing database
  objects or form items incorrectly
• Not connected to database
• PL/SQL Editors compilation messages pane
  displays
• Line number of error
• Error message
• Correction suggestions

89
Trigger Syntax Error Caused by Referencing a
Database Object Incorrectly
90
Creating a List of Values (LOV)

• List of values (LOV)
• Pronounced ell-oh-vee
• Displays list of possible data values for text
  item
• LOV display
• Dialog box displays possible choices for form
  text item

91
Creating a List of Values (LOV) (continued)

• Record group
• Form object that represents data in tabular
  format
• Provides values for LOV
• LOV Wizard

92
Creating an LOV

• LOV Wizard pages
• LOV Source
• SQL Query
• Column Selection
• Column Display
• LOV Display
• Advanced Options
• Items
• Finish

93
Column Display Page
94
Adding a Command Button to Open the LOV Display

• Create command button on canvas
• Button tool
• Create associated button trigger
• GO_ITEM(block_name.item_name)
• LIST_VALUES

95
Using the LOV Wizard in Reentrant Mode

• Modify properties of existing LOV
• Change LOV display position
• Change column widths

96
Representing Data Values Using Other Item Types

• Other item types
• Radio button
• Check box
• Others

97
Creating Radio Buttons

• Radio buttons
• Represent data fields whose values must be one of
  small set of mutually exclusive selections
• Exist within radio group
• Select item type in Layout Wizard
• Manually create radio buttons
• Use Radio Button tool
• Change properties using Property Palette
• Align edges using Align Objects dialog box

98
Creating Check Boxes

• Check boxes
• Represent data values that can have only one of
  two opposing values
• Label represents one value
• Value when Checked
• Value that will be saved in database if checkbox
  checked
• Value when Unchecked

99
Lesson C Summary

• Triggers
• Program units that execute when user or system
  performs action
• List of values (LOV)
• Displays list of selections for form text item
• Other item types
• Radio button
• Check box