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.
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.
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.
1 comment:
Very helpful thanks
Post a Comment