|
|
Forms
|
|
|
|
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.
FYou
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 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.
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. |
|
|
|
|
