FORMS BUILDER EXERCISES

Exercise 1

1. Create a data block called "Employee" based on the S_EMP table.
2. Create a canvas called "EMP_CANVAS" to display the data block.
3. START_DATE is a field in the S_EMP table. Initialize the value of START_DATE to be the current date.
4. Make COMMISSION_PCT a radio group, and add five radio buttons: 10, 12.5, 15, 17.5, 20 to the group. So that the user can choose from these values.

Exercise 2

You need to complete Forms Builder Tutorial on data blocks, master-detail relationships, canvases, control blocks, non-database-table items, buttons, triggers.

Run the script company.sql. It will create several tables. The figure below shows the relational schema of these tables.  You can examine the tables in the Object Navigator under Database Objects.

  1. Create two data blocks PROJECT-BLOCK and WORKS_ON_BLOCK and attach to the two tables PROJECT and WORKS_ON respectively.
  2. Create a master-detail relationship between these two data blocks.
  3. Create a canvas called PROJECT_CANVAS to display the two data blocks.
  4. Create a control block CONTROL_BLOCK.
  5. Add a button "SAVE", another button "LIST OF DEPARTMENTS" and a text item "DEPARTMENT NAME" to CONTROL_BLOCK.
  6. Attach a WHEN-BUTTON-PRESSED trigger to the "SAVE" button to execute commit_form.
  7. Save the form as YOURNAME_Works.fmb, because you will use it in other exercises.

Exercise 3

This exercise uses the rental.sql script to create tables, if you have not already run this script from exercise 2 download it here. The figure below shows the relational schema of these tables. 

 

1.  Create a data block VIEWING that shows the 3 fields clientNo, propertyNo and viewDate from the Viewing table. Do not include the item "comment" since it is a RESERVED word in SQL.

2.  Attach this data block VIEWING to a canvas labeled VIEWING_CANVAS.

3.  Change the initial value of viewDate to the current date.

4.  Create a data block CLIENT that has all the fields from the Client table. Attach this block to VIEWING_CANVAS.

5.  Create a MASTER-DETAIL relationship between VIEWING and CLIENT data blocks. Select the VIEWING data block and use the Data Block Wizard for this.

6.  Make the item prefType in the CLIENT block a radio group. Add two radio buttons: "Flat" and "House" to the group.

7.  Create a control block CONTROL. Create a "SAVE" button, a "DISPLAY CLIENT" button and a text item labeled "Search Client Name" in the control block. Attach all these items to the VIEWING_CANVAS.

8.  Attach a WHEN-BUTTON-PRESSED trigger and the "commit_form;" command to the SAVE button.

9.  Create an LOV and a radio group called Client_LOV. The LOV should display the values of ClientNo, fName and lName from the Client table. It must return these values to the  corresponding items in the CLIENT block. Attach the Client_LOV to the "DISPLAY CLIENT" button using the WHEN-BUTTON-PRESSED trigger.

10.  Use Query->Enter and Query-Execute to scroll through values in the VIEWING and CLIENT  data blocks. You may need to change the ordering of the blocks for this to work.

11.  Check that your Client_LOV works correctly and returns 3 fields in the CLIENT block.

12.  Change the value for maxRent in CLIENT and use the "SAVE" button to save the change. Verify the change by running a SQL query "SELECT * FROM Client;"

13.  Save the form as Yourname_Rental.fmb, because you will use it in other exercises.

Exercise 4

 

You need to complete Forms Builder Tutorial Part II on triggers, LOVs, sequences, program units, calculated values, alerts.

 

A. You will use Works.fmb which you created in Exercise 2.

 
 

1. Use Query->Enter and Query->Execute to verify that the MASTER-DETAIL relationship is working and populating data in the blocks WORKS_ON-BLOCK and PROJECT_BLOCK.

2. Create an LOV DEPT_LOV which returns DNUMBER and DNAME from the Department table. DNUMBER must be returned to the item DNUM in PROJECT_BLOCK and DNAME must be returned to the text item "DEPARTMENT NAME" in CONTROL_BLOCK.

3. Use a WHEN-BUTTON-PRESSED trigger to attach DEPT_LOV to the button "LIST OF DEPARTMENTS".

4. Customize the DEPT_LOV so that it only displays some departments, e.g., those departments where the last 4 digits of the MGRSSN is "5555". Create a text item "LASTFOUR" in CONTROL_BLOCK. Attach it to the PROJECT_CANVAS.

Modify the DEPT_LOV SQL code with the following:

"WHERE Department.MGRSSN LIKE :CONTROL_BLOCK.LASTFOUR".

5. Create a sequence SEQ_PNUM and use it to generate values to be returned to the item PNUMBER in the data block PROJECT_BLOCK. Remember that PNUMBER is the primary key for the PROJECT table. If your sequence returns duplicate values for PNUMBER that are already in the PROJECT table there will be a primary key constraint violation. So the initial value for SEQ_PNUM should be greater than 30, e.g., 50.

Use the Property Palette for item PNUMBER and and change the "Required" property to "No".

6. Add a text item EARNINGS to CONTROL_BLOCK. Attach the item EARNINGS to the PROJECT_CANVAS so that EARNINGS aligns with the other items ESSN, PNO, HOURS in WORKS_ON_BLOCK.

7. Create a program unit called CALCULATE_EARNINGS which calculates the value of earnings by multiplying the value of :WORKS_ON_BLOCK.HOURS by the hourly pay rate of 20 (DOLLARS). Return the calculated earnings to the text item EARNINGS -- :CONTROL_BLOCK.EARNINGS. IMPORTANT - you need to use the colon symbol ":" when you refer to an item in a block. The format is :name-of-block.name-of-item

8. Add a block level POST-QUERY trigger to the WORKS_ON_BLOCK or to the item HOURS in WORKS_ON_BLOCK which calls the CALCULATE_EARNINGS program unit.

9. Create an alert. Make sure that the alert will be called when the "Save" button is  pressed and provides the option to submit or to cancel.

10. Save the form.
  B. The following exercises will use Rental.fmb which you created in Exercise 3.

 
 

1. Create a "Save" button to save information and add a WHEN-BUTTON-PRESSED trigger on the Client Canvas.

 

2. Add an LOV which is connected to the "Client" button. The clientNo and the Client Last

 

3. Name returned by the LOV must be input into clientNo and CLIENT_NAME in the form.

 

4. Customize the LOV: First make sure that the item CLIENT_NAME is visible on the canvas. Allow the user to enter a substring into this item CLIENT_NAME.  Modify the LOV code to search the database so that the client names that are returned match this substring. Use the SQL LIKE operator in the LOV code.

 

5. Save the form.

Exercise 5

 

You need to complete Forms Builder Tutorial Part III to finish this exercise. The objectives of this exericse are: trigger, delete records, clear forms, close windows, switch between canvases.

 

A. The following exercises will use Works.fmb which you updated in Exercise 4.

 
 

1.  Add a "Delete Project" button to delete the project information. Make sure you delete all the works_on information related to the deleted project.

 

2.  Add a "Delete Works_On" button to delete the works_on information.

 

3.  Add a "Clear" button to clear the whole form.

 

4.  Create another canvas called EMP_CANVAS to manage the information of the employees. Add a data block called EMPLOYEE to EMP_CANVAS to display the employee information.

 

5.  Add a "Employee Mangement" button to PROJECT_CANVAS, so that when the user click this button, he/she will be navigated to EMP_CANVAS.

 
  B. The following exercises will use Rental.fmb which you updated in Exercise 4.

 
 

1.  Add a "Delete Viewing" button to VIEW_CANVAS to delete the viewing information.

 

2.  Add a "Clear" button to VIEW_CANVAS to clear the whole form.

 

3.  Add a "Client Mangement" button to VIEW_CANVAS, so that when the user click this button, he/she will be navigated to CLIENT_CANVAS.

Hints for Final Group Project

Please use this starter form for your group project.

  1. It has a control block, save and clear buttons
    and several form level triggers.
    Link to starter.fmb is here
  2. Please look at this sample script that created all the
    S_XXX tables that we have used in our tutorials.
    Please create a similar script to create your project database.
    Link to summit2.sql" is here