|
"People fail
forward to success." Mary Kay Ash |
Read
first then play the video:
FRM-VIDEO -(Hands-On
05) How to use Display items, Summary and function columns
Display items, Summary and
function columns
Introduction
Display items are
read-only items and are only useful for calculated data such as
subtotals and unchangeable data.
You can use Summary
Columns to calculate average, sum, min, and max of a group of
items in a block. It can be read-only or text items. Use Function
columns to return calculation of binding variables to your screen.
It also can be read-only or text items.
Hands-On
While designing the tables,
you have identified a derived field in one of the tables (Item). There
should not be a column in a table that is calculated or generated by
two other columns. This is the Third Normal form rule. It should be
rectified for good database design. Their DBA will remove any columns
whose contents are depended on the other columns. In the
"Item" table the "itemtot" column is a column
depended on quantity * actualprice.
See Figure 7.
Your tasks are:
1- Modify the
"Items" tab canvas layout to generate a new item called
"itemtotal". This field will be populated from other "Item"
table"s column during order entry (quantity * actualprice).
2- Avoid populating the "itemtotal"
from the table"s column.
3- Add a display field to
calculate the grand total of all calculated field call "itemtotal"
for each customer's order.
4- Run and test all user
functional requirements.
F You will learn how
to: display fields, summary and formula functions, use tab canvas, use
"object navigator," use "Data Blocks," use "Layout Editor,"
use "Property Palette," use "Run Form," and "Execute Query."
Figure 7
Open a Module
In the "Object Navigator"
window, highlight Forms. Go to the Main menu and choose "File,"
select "Open" to open an existing form (customer_orders_V04)
from the "iself"
folder.
Save a module
Click on the "CUSTOMER_ORDERS_V04"
form. The color changes to blue. Then change the name and save the
Form name as version 05 (customer_orders_v05). This way the
original form is untouched.
Move an object
In the Layout Editor
window, click on the "ITEM" tab to display its canvas. Select and
move the "ITEMTOT" item and Scrollbar to right to reserve a space
for a new item.
To select multiple objects,
push and hold the control key and then click on the objects that you
wish to be part of group. You can also, go outside of the objects and
click and hold the cursor on the canvas and move it until all the
objects are in the box. Release the mouse. Now, you selected all your
objects. Go anywhere in side of the handlers to move your selected
objects.
In the Layout Editor
window, make sure that the "Block" box shows the "ITEM"
block. If not select the "ITEM" data block.
Create a Display item
On the toolbar in the
Layout Editor Window, select "Display item" and click the "+"
where you wish to have your new item (DISPLAY_ITEMnn). In this
hands-on exercise, you should have your item next to the "quantity"
item. Adjust its size. And open its property palette.
Formula Column
In its Property Palette,
change "name" to itemtotal. Change "Justification" to right.
Change "Datatype" to Number. Its "format mask" to $99,999.00.
Change "Calculation Mode" to Formula, and type the formula
on the "Formula" box (:qty * :actualprice). The colon next to an
item, references to a binding variable on the screen such as
actualprice and quantity. Change "database item" to no,
because this is not a column from the item table. Change
"prompt" to "itemtotal." Change "Prompt
attachment edge" to "Top." Then close the window.
Add another "Display
Item" to calculate the totals of "itemtotal." Notice that the
"Block" box on the canvas window points to the "ITEM" block.
And the "Number of Items Displayed" on this object is 5.
On the toolbar in the
Layout Editor, select "Display item" and click the "+" where
you wish to have your new item (DISPLAY_ITEMnn). In this hands-on
exercise, you should have your item under the "ITEMTOTAL" item.
You get 5 objects but we only need one. Right click on the object and
open its property palette.
Summary Column
In its Property Palette,
change "name" to "ORDERTOTAL." Change "Justification" to
"Right." Change "Data Type" to "Number." Change "Calculation
Mode" to "Summary."
Here, the formula property
is not applicable. Change "Summary Function" to "SUM." Change
"Summarized block" to "ITEM." Change "summarized Item" to
ITEMTOTAL. Change "Number of items displayed" to 1. Change "Database
item" to "no." Type the prompt property to "Order Totals:."
Change "Prompt attachment edge" to "Start." Then, close the
window.
Make some window
adjustments, if needed.
In the Object Navigator
window, right click on the ITEM data blocks to open its Property
Palette.
In its Property Palette,
change "Query all records" to "yes." Then close
the window.
Navigate the application's
layout.
Run the Form Runtime
Then run the application.
Execute Query
Click "Execute query"
and navigate through the application.
Notice that the ITEMTOTAL
column matches with the itemtot column.
There is no "Format mask"
for the "order total" item.
Close the Runtime Forms.
Change a Format Mask
In the Layout Editor, right
click on the order total (ORDERTOTAL) to open its Property Palette.
In its Property Palette,
change its format mask to $999,999.00. Close the window and run the
application.
Execute Query
Execute the query and
navigate through the application.
When you are done with all
your testing, then close the application and save the changes.
|
"Whenever I hear,
'It can't be done,' I know I'm close to success." Michael
Flatley (Lord of the Dance) quoted by Eric Celeste |
Questions:
Q: Describe a Display Item
in the Form Module.
Q: What are the Summary
Columns in the Form Module?
Q: What are the Function
Columns in the Form Module?
Q: What are the differences
between Summary Columns and Function Columns?
Q: How do you calculate a
SUM of a group of items in a block?
Q: While designing the
tables, you have identified a derived field in one of the tables
(Item). There should not be a column in a table that is calculated or
generated by two other columns. This is the Third Normal form rule. It
should be rectified for good database design. Their DBA will remove
any columns whose contents are depended on the other columns. In the
"Item" table the "itemtot" column is a column
depended on quantity * actualprice.
See Figure 7.
Your tasks are:
1- Modify the
"Items" tab canvas layout to generate a new item called
"itemtotal". This field will be populated from other "Item"
table"s column during order entry (quantity * actualprice).
2- Avoid populating the "itemtotal"
from the table"s column.
3- Add a display field to
calculate the grand total of all calculated field call "itemtotal"
for each customer's order.
4- Run and test all user
functional requirements.
|