5 How to Control Form Layout

Create the HT_EMP Table

First, you create a new table by running a script in SQL Scripts.

To create the HT_EMP table and the appropriate associated objects:

1. On the Workspace home page, click SQL Workshop and then SQL Scripts.

   The SQL Scripts home page appears.

2. Click Create.

   The Script Editor appears.

3. In Script Name, enter HT_EMP.

4. In the Script Editor, enter the following data definition language (DDL):

   CREATE TABLE ht_emp (
      emp_id                 NUMBER       primary key,
      emp_first_name         VARCHAR2(30) not null,
      emp_middle_initial     VARCHAR2(1),
      emp_last_name          VARCHAR2(45) not null,
      emp_part_or_full_time  VARCHAR2(1)  not null check (emp_part_or_full_time in ('P','F')),
      emp_salary             NUMBER,
      emp_dept               VARCHAR2(20) check (emp_dept in ('SALES','ACCOUNTING',
   
   'MANUFACTURING','HR')), 
      emp_hiredate           DATE,
      emp_manager            NUMBER       references ht_emp,
      emp_special_info       VARCHAR2(2000),
      emp_telecommute        VARCHAR2(1) check (emp_telecommute in ('Y')),
      rec_create_date        DATE         not null,
      rec_update_date        date)
   /
    
   INSERT INTO ht_emp
      (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute, rec_create_date)
   VALUES
      (1,'Scott','R','Tiger','F',
       100000,'SALES',sysdate,null,'cell phone number is xxx.xxx.xxxx
   home phone is yyy.yyy.yyyy','Y',
       SYSDATE)
   /
    
   CREATE SEQUENCE ht_emp_seq
      start with 2
   /
    
   CREATE OR REPLACE TRIGGER bi_ht_emp
         BEFORE INSERT ON ht_emp
         FOR EACH ROW
      BEGIN
         SELECT ht_emp_seq.nextval
           INTO :new.emp_id
           FROM DUAL;
         :new.rec_create_date := SYSDATE;
      END;
   /
    
   CREATE OR REPLACE TRIGGER bu_ht_emp
         BEFORE UPDATE ON ht_emp
         FOR EACH ROW
      BEGIN
         :new.rec_update_date := SYSDATE;
      END;
   /
   
   

5. Click Save.

   The script appears in the SQL Scripts Repository.

6. Run the HT_EMP script:

   1. Click the HT_EMP script.

   2. Click Run.

   3. On the Run Script page, click Run again.

7. From the View list, select Details and click Go.

   Figure 5-1 Details View in the Manage Script Results Page

   
   Description of "Figure 5-1 Details View in the Manage Script Results Page"
   
   

8. Click the View Results icon to access the Results page.

   Red text indicates errors while executing the file.

Create a New Application

Next, create a new application.

To create an application:

1. Return to the Workspace home page. Click the Home breadcrumb link at the top of the page.

2. On the Workspace home page, click the Application Builder icon.

   The Application Builder home page appears.

3. Click Create.

4. Select Create Application and click Next.

5. For Name, specify the following:

   1. Name - Enter Form Layout.

   2. Application - Accept the default.

   3. Create Application - Accept the default, From scratch.

   4. Schema - Select the schema where you installed the OEHR sample objects.

   5. Click Next.

      Next, you need to add pages. You have the option of adding a blank page, a report, a form, a tabular form, or a report and form. For this exercise, you add two blank pages.

6. Add a blank page:

   1. Under Select Page Type, accept the default, Blank, as shown in Figure 5-2.

      Figure 5-2 Add Page

      
      Description of "Figure 5-2 Add Page"
      
      

   2. Click Add Page.

      The blank page appears at the top of the page.

7. Click Next.

8. For Tabs, accept the default, One Level of Tabs, and then click Next.

9. For Copy Shared Components from Another Application, accept the default, No, and click Next.

10. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and then click Next.

11. For User Interface, select Theme 12 and then click Next.

12. Review your selections and click Create.

    The Application home page appears. Note that your application contains two pages.

Create a New Page Containing an Input Form

Next, create a new form using the Form on a Table or View Wizard.

To create a data input form:

1. On the Application home page, click Create Page.

2. For Page, select Form and click Next.

3. On Create Page, select Form on a Table or View and click Next.

4. For Table/View Owner, accept the default and click Next.

5. For Table/View Name, select the HT_EMP table and click Next.

6. For Page and Region Attributes:

   1. Page Number - Enter 2.

   2. Page Name - Enter Form Layout.

   3. Region Title - Enter Form Layout.

   4. Region Template - Accept the default.

   5. Breadcrumb - Accept the default.

   6. Click Next.

7. For Tab, accept the default, Do not use tabs, and click Next.

8. For Primary Key, accept the default and click Next.

   Note that the wizard reads the primary key from the database definition.

9. For Source Type, accept the default Existing Trigger and click Next.

10. For Select Columns, select all the columns. Press SHIFT, select the first column and then the last one. Then, click Next.

11. For Process Options, accept the defaults and click Next.

12. For Branching, enter 2 (the page you are creating) in both fields and click Next.

    Since this page is just for demonstration, you will not be utilizing branching.

13. Click Finish.

14. Click the Edit Page icon.

    The Page Definition for page 2 appears.

15. Delete the following validation:

    • Under Page Processing, Validations, select P2_REC_CREATE_DATE not null.

    • Click Delete.

    You are removing this validation because the value of this column is set using a trigger. The item will have no value in the form for a new record. This validation was automatically created because the underlying database column is not null.

Run the Page

Once you create a new input form, the next step is to run the page.

To run the page from the Page Definition:

1. Click the Run Page icon in the upper right corner as shown in Figure 5-3.

   Figure 5-3 Run Page Icon

   
   Description of "Figure 5-3 Run Page Icon"
   
   

2. If prompted for a user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".

   The application appears.

As shown in Figure 5-4, the new form appears. Note that the form contains basic employee details and includes select lists, text areas, and display only items.

Figure 5-4 Employee Info Form

Description of "Figure 5-4 Employee Info Form"

By default, the Primary Key column does not display since it is assumed that the primary key is system generated. In reality, the primary key is included in the page, but appears as a hidden item.

Notice that the page defaults with one item for each row and labels display to the left of the items. The item labels default to the column names with initial capitalization and with the underscores (_) replaced with spaces. You can override this default behavior by configuring user interface defaults for the table.

Also notice that items based on date columns default to include a date picker. Lastly, notice that the Emp Special Info item was created as a text area because of the size of the base column. This item type is a better choice for large text items since it allows the input text to wrap.

Edit the Region Title

To edit the region title and other region level attributes:

1. Click Edit Page 2 on the Developer toolbar.

2. Under Regions, click Form Layout.

   The Region Definition appears.

3. Change the Title to Employee Info.

4. Click Apply Changes at the top of the page.

5. From the Page Definition, click the Run Page icon in the upper right corner.

   Note the new region title.

Change the Display Point and Template

To change other region attributes:

1. Return to the Page Definition. Click Edit Page 2 on the Developer toolbar.

2. Under Regions, click Employee Info.

   The Region Definition appears.

3. Scroll down to User Interface.

   You can control the position of a region within a page template by changing the page template.

4. From Display Point, select Page Template Region Position 3.

   In this instance, your selection moves the region to the right side of the page. The region display points are determined by the page level template. If you do not select a page level template, Oracle Application Express uses the default page level template defined in the current theme. You can view specific positions by selecting the flashlight icon to the right of the Display Point list.

   Next, temporarily change the region template.

5. From Template, select Borderless Region.

6. Click Apply Changes at the top of the page.

7. From the Page Definition, click the Run Page icon in the upper right corner.

   The form appears as shown in Figure 5-5.

   Figure 5-5 Employee Info Form with New Display Point and Template

   
   Description of "Figure 5-5 Employee Info Form with New Display Point and Template"
   
   

Note the changes in the appearance of the form. The form title displays in a dark color on a white background and has a rule beneath it. Also, note that there is no longer a border around the form.

Change the Region Attributes Back to the Original Selections

To return to the region template and display point to the original selections:

1. Click Edit Page 2 on the Developer toolbar.

2. Under Regions, select Employee Info.

3. Scroll down to User Interface.

4. From the Template list, select Form Region.

5. From the Display Point List, select Page Template Body (3. Items above region content).

6. Click Apply Changes at the top of the page.

Edit Item Attributes

To edit all item attributes:

1. On the Page Definition for page 2, locate the Items section.

2. Under Items, click the Edit All icon.

   The Edit All icon resembles a small grid with a pencil on top of it as shown in Figure 5-6.

   Figure 5-6 Edit All Icon

   
   Description of "Figure 5-6 Edit All Icon"
   
   

   The Page Items summary page appears.

   You change how a page appears by editing the item attributes. Common item attribute changes include:

   • Changing item labels by editing the Prompt field.

   • Placing more than one item in certain rows to group like items together. For example, you could group all items that make up an employee's name.

   • Changing the item width. Many items display better when they have a width that is less than the maximum. To change the item width, enter a new value in the Width field.

   • Reordering the items. The initial order of items is based on the order of the columns in the table on which the region is based. To reorder items, enter a new value in the Sequence field.

3. To see the how item attributes affect page layout, make the following changes:

   1. Change the values in the Prompt, New Line, and Width fields to match those in Table 5-1:

      Table 5-1 New Prompt, New Line, and Width Field Values

      Prompt Field	New Line	Width
      Emp ID	Yes	30
      First Name	Yes	15
      Middle Initial	No	2
      Last Name	No	15
      Part or Full Time	Yes	2
      Salary	Yes	10
      Department	Yes	15
      Hire Date	Yes	10
      Manager	No	15
      Special Information	Yes	60
      Telecommute	Yes	2
      Record Create Date	Yes	10
      Record Update Date	Yes	10

      
      

   2. Click Apply Changes.

   3. Click the Run Page icon in the upper right corner of the page.

      Figure 5-7 Employee Info Form After Editing the Prompt, New Line, Width Attributes

      
      Description of "Figure 5-7 Employee Info Form After Editing the Prompt, New Line, Width Attributes"
      
      

   As shown in Figure 5-7, note that some items are pushed too far to the right because of the width of the Special Information item. Oracle Application Express lays out regions as tables, and the width of each column is determined by the largest display width of the items in that column.

Fix Item Alignment

There are several approaches to fixing item alignment:

• For the items Middle Initial, Last Name and Manager items, set New Field to equal No.

  This places the items directly after the items they follow, but in the same column. This approach is best used for positioning embedded buttons next to items. Note that this setting can make text items appear squashed.

• Change the Column Span field of the Special Information item.

  For example, setting the Column Span for the Special Information item to 5 would enable multiple items to display above and below it. This change causes five items to display above Special Information (First Name, Middle Initial, and Last Name).

  Be aware, however, that using Column Span to fix the display of the name does not result in a consistent layout. The Manager item would still be in the same column as Middle Initial. Because the Manager item is larger than Middle Initial, Last Name would still be pushed too far to the right. To fix this, you could change the Column Span of the Manager item to 3 so it displays above Special Information.

• Reset the column width in the middle of the region by adding an item of type Stop and Start HTML Table. This forces the close of an HTML table using the </table> tag and starts a new HTML table. Inserting a Stop and Start HTML Table item just after the Last Name item results in an even layout. Note that a Stop and Start HTML Table item only displays its label. You can prevent the label from displaying at all by setting it to null. To do this, you simply remove the defaulted label.

Change a Region to Display-only

There are two columns in the HT_EMP table for auditing, Record Create Date and Record Update Date. Because the values of these columns are set with triggers, these columns should not be updatable by users. This exercise demonstrates how to make items display-only and then how to add them to their own region.

Topics in this section include:

• Change Items to Display-only

• Create a New Region

• Move Audit Items to the New Region

• Change the Region Template to Hide/Show Region

Change an Item to a Radio Group

Because the Part or Full-time item only has two valid choices, this item is a good candidate for either a check box or a radio group.

To change the Part or Full-time item to a radio group:

1. Go to the Page Definition for page 2. Click Edit Page 2 on the Developer toolbar.

2. Under Items, select P2_EMP_PART_OR_FULL_TIME.

   The Edit Page Item page appears.

3. Locate the Name section. From the Display As list, select Radiogroup.

4. Locate the Label section. Specify the following:

   1. Label - Remove the text in the Label field.

   2. Template - Select No Label.

5. Under List of Values, create a static list of values. Specify the following:

   1. Named LOV - Select Select Named LOV.

   2. List of values definition - Enter:

      STATIC:Full-time;F,Part-time;P
      
      

   This definition will display as two radio buttons with the labels Full-time and Part-time, but the value being inserted into the database will be either F or P.

6. At the top of the page, click Apply Changes.

7. Click the Run Page icon in the upper right corner. The modified form appears as shown in Figure 5-18.

   Figure 5-18 Part or Full-time item Changed to a Radio Group

   
   Description of "Figure 5-18 Part or Full-time item Changed to a Radio Group"
   
   

   Notice that Full-time and Part-time displays as a radio group that is stacked in one column. You can have these buttons display side by side.

To display the Full-time and Part-time radio buttons side by side:

1. Go to the Page Definition for page 2.

2. Under Items, select P2_EMP_PART_OR_FULL_TIME.

3. Scroll down to List of Values.

4. In Number of Columns, enter 2.

5. At the top of the page, click Apply Changes.

6. Click the Run Page icon.

   By changing this setting to match the number of valid values (that is, Full-time and Part-time), the values display side by side as shown in Figure 5-19.

   Figure 5-19 Part or Full-time Item Displayed Side by Side

   
   Description of "Figure 5-19 Part or Full-time Item Displayed Side by Side"
   
   

Change an Item to a Select List

In the DDL you used to create the HT_EMP table, the Department is validated by a check constraint. You can implement the Department as a radio group, a select list, or a Popup LOV.

To change Department to a select list:

1. Go to the Page Definition for page 2. Click Edit Page 2 on the Developer toolbar

2. Under Items, select P2_EMP_DEPT.

3. From the Display As list in the Name section, select Select List.

   The other Select List choices are for either redirecting the user to another page or URL based on the selection, or submitting the current page which is used when other information needs to be retrieved based upon the selection in the Select List.

4. Scroll down to List of Values.

5. Under List of Values, create a static list of values. Specify the following:

   1. Named LOV - Select Select Named LOV

   2. List of values definition - Enter:

      STATIC:SALES,ACCOUNTING,MANUFACTURING,HR
      
      

   3. From Display Null, select Yes.

   4. In Null display value, enter:

      - No Assignment -
      
      

      The last two selections take into account that the EMP_DEPT column can contain nulls. As a best practice, whenever you implement a select list and have a column that can be null, you should set Display Null to Yes. Failure to do so results in the item defaulting to the first item in the select list.

6. At the top of the page, click Apply Changes.

7. Click the Run Page icon.

   The revised form appears as shown in Figure 5-20.

   Figure 5-20 Department Changed to a Select List

   
   Description of "Figure 5-20 Department Changed to a Select List"
   
   

Change an Item to a Check Box

The item Telecommute is ideal for a check box. When you change the Display Type, you can also move it up on the page and place it next to the Full-time and Part-time radio group.

To change Telcommute to a check box:

1. Go to the Page Definition for page 2. Click Edit Page 2 on the Developer toolbar.

2. Under Items, select P2_EMP_TELECOMMUTE.

3. From the Display As list in the Name section, select Checkbox.

4. Under Displayed, specify the following:

   1. Sequence - Enter 136.

   2. Begin on New Line - Select Yes.

5. Scroll down to List of Values.

6. To have the label precede the check box, specify the following:

   1. Named LOV - Select Select Named LOV.

   2. List of values definition - Enter:

      STATIC:;Y
      
      

      This List of values definition displays the check box after the label, but will not display a value associated with the check box. If the check box is checked, the value passed to the database will be Y.

7. At the top of the page, click Apply Changes.

8. Click the Run Page icon.

   Note that the check box appears for Telecommute as shown in Figure 5-21.

   Figure 5-21 Telecommute Field Changed to Check Box

   
   Description of "Figure 5-21 Telecommute Field Changed to Check Box"