6 How to Work with Check Boxes

Run the Application

Next, review the application by running it.

To run the application:

1. Click Run Application as shown in Figure 6-2.

   Figure 6-2 Run Application Icon

   
   Description of "Figure 6-2 Run Application Icon"
   
   

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

   The application appears. Note that the report contains eleven columns displaying product information. Users can link to an update form by clicking the Edit icon in the far left column.

3. Click the Edit icon next to a specific product. As shown in Figure 6-3, an update form appears.

   Figure 6-3 Update Form

   
   Description of "Figure 6-3 Update Form"
   
   

Hide the Warranty Period Field

First, hide the Warranty Period field by changing the Display As attribute.

To hide the Warranty Period field:

1. Click Edit Page 2 on the Developer toolbar.

   The Page Definition for page 2 appears.

2. Scroll down to the Items section.

3. Under Items, select P2_WARRANTY_PERIOD.

4. From Display As in the Name section, select Hidden.

5. Click Apply Changes.

Add a New Checkbox

In this exercise you create a check box that automatically sets the minimum product price to 75% of the list price.

Topics in this section include:

• Add a New Item

• Create a Process

• Edit the Item to Display as a Check Box

Edit the Item to Display as a Check Box

Next, change the Set Minimum Price radio group (P2_SET_MIN_PRICE) to display as a check box.

To edit P2_SET_MIN_PRICE:

1. Click Edit Page 2 on the Developer toolbar.

   The Page Definition for Page 2 appears.

2. Under Items, click P2_SET_MIN_PRICE.

3. From Display As, select Checkbox.

4. Scroll down to Label. In Label, delete the existing text, Set Minimum Price.

5. Scroll down to Default. In Default Value, enter N.

6. Under Lists of Values:

   1. Number of Columns - Enter 1.

   2. List of values definition - Enter:

      STATIC: <b> Set Minimum Price</b><br/> (25% Discount on List Price);Y
      

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

Run the Page

To run the page, click the Run Page icon in the upper right corner. The revised form appears as shown in Figure 6-6. Note the new Set Minimum Price check box.

Figure 6-6 Update Form with Set Minimum Price Check Box

Description of "Figure 6-6 Update Form with Set Minimum Price Check Box"

Change the Search Field to a Multi Value Check Box

To change the search field to a check box:

1. Click Edit Page 1 on the Developer toolbar.

   The Page Definition for page 1 appears.

2. Under Items, click P1_REPORT_SEARCH.

3. From Display As, select Checkbox.

4. Scroll down to Label. For Label, delete the existing text and replace with Product Status.

5. Scroll down to Source. In Source Value or Expression, enter:

   obsolete:orderable:planned:under development
   

6. Scroll down to List of Values. Specify the following:

   1. Named LOV - Accept the default.

   2. Number of Columns - Enter 4.

   3. List of values definition - Enter:

      SELECT DISTINCT product_status display_value, product_status return_value
      FROM oehr_product_information
      ORDER BY 1
      

      Note:

      Note that to create a multi value check box, the List of Values query must return more than one row.

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

   The Page Definition for page 1 appears.

Edit the Report Region Definition

To edit the report region definition:

1. Under Regions, click Product Report.

   The Region Definition appears.

2. Scroll down to Source.

3. In Source modify the WHERE clause to read as follows:

   ...
   WHERE  instr(':'||:P1_REPORT_SEARCH||':',product_status)> 0
   

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

   The Page Definition for page 1 appears.

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

   The Page Definition for page 1 appears.

Change the Default Check Box Behavior

Although the Product Status check boxes correctly filter the content on page 1, if you deselect all the check boxes, notice the report returns all products. This behavior results from the fact that if a check box has a NULL value (that is, it is deselected), then it defaults to the default value Y. The default value of Y, in turn, enables the check box.You can alter this behavior by adding a computation that remembers the state of the check box.To add a computation that tracks the state of the check box:

1. Under Page Processing, Computations, click the Create icon.

   The Create Page Computation Wizard appears.

2. For Item Location, select Item on This Page and click Next.

3. For Item, specify the following:

   1. Compute Item - Select P1_REPORT_SEARCH.

   2. Sequence - Accept the default.

   3. Computation Point - Select After Submit.

   4. Computation Type - Select Static Assignment.

   5. Click Next.

4. In Computation:

   1. Enter:

      none(bogus_value)
      

   2. Click Next.

5. For Condition:

   1. From Condition Type, select Value of Item in Expression 1 Is NULL.

   2. In Expression 1, enter:

      P1_REPORT_SEARCH
      

6. Click Create.

   The Page Definition for page 1 appears.

7. Click the Run Page icon in the upper right corner. Note that the Product Status check boxes display at the top of the page.

Change the Check Boxes to Display in Bold

Next, you edit the check box display values (or labels) so that they appear as bold text.

To edit check box display values (or labels) to appear in bold:

1. Go to the Page Definition for page 1.

2. Under Items, click P1_REPORT_SEARCH.

3. Scroll down to Element.

4. In Form Element Option Attributes, enter:

   class="fielddatabold"
   

   Form Element Option Attributes are used exclusively for check boxes and radio buttons and control the way the Application Express engine renders individual options.

5. Click Apply Changes.

   The Page Definition for page 1 appears.

Call APEX_ITEM.CHECKBOX

To edit the query to call APEX_ITEM.CHECKBOX:

1. Go to the Page Definition for page 1.

2. Under Regions, click Product Report.

3. Scroll down to Source.

4. In Region Source, add the new line appearing in bold face to the query.

   SELECT 
   "product_id",
   apex_item.checkbox(1,product_id) del,
   "product_name", 
   "product_description",
   "category_id",
   "weight_class",
   "warranty_period",
   "supplier_id",
   "product_status",
   "list_price",
   "min_price",
   "catalog_url"
   FROM   "oehr_product_information" 
   WHERE  instr(':'||:p1_report_search||':',product_status)> 0  
   

   APEX_ITEM is an Oracle Application Express supplied package that you can use to generate certain items dynamically. Note that the value passed in for p_idx in the above example is 1. You reference the check box values using the global variable APEX_APPLICATION.G_F01 later on.

   Oracle Application Express automatically adds new columns to the end of the column list. Next, you need to move the DEL column.

5. Scroll to the top of the page and select the Report Attributes tab.

6. Under Column Attributes, locate the Del column.

7. Click the Up arrow on the far right until the DEL column is directly below PRODUCT_ID. (See Figure 6-8).

   Figure 6-8 Report Column Attributes Page

   
   Description of "Figure 6-8 Report Column Attributes Page"
   
   

8. Click Apply Changes.

   The Page Definition for page 1 appears.

Add a Button to Submit Check Box Array Values

To add a button to submit the check box array values:

1. Go to the Page Definition for page 1.

2. Under Buttons, click the Create icon.

3. For Button Region, select Product Report (1) and click Next.

4. For Position, select Create a button in a region position and click Next.

5. For Button Attributes:

   1. Button Name - Enter DELETE_PRODUCTS.

   2. Label - Enter Delete Products.

   3. Accept the remaining defaults and click Next.

6. In Button Template, accept the default selection and click Next.

7. For Display Properties:

   1. Position - Select Top of Region.

   2. Accept the remaining defaults and click Next.

8. For Branching, select 1 Product Report and click Create Button.

Add a Process

To add a process that executes when the user clicks the Delete Products button:

1. Under Page Processing, Processes, click the Create icon.

2. For Process Type, select PL/SQL and click Next.

3. For Process Attributes:

   1. Name - Enter Delete Products.

   2. Sequence - Accept the default.

   3. For Point - Select On Submit - After Computations and Validations.

   4. Click Next.

4. Enter the following PL/SQL process and then click Next:

   FOR i in 1..APEX_APPLICATION.G_F01.count
   LOOP
      DELETE FROM oehr_product_information
      WHERE product_id = APEX_APPLICATION.G_F01(i);
   END LOOP;
   

   APEX_ITEM is an Oracle Application Express supplied package that you can use to generate certain items dynamically. When a page is submitted, the values of each column are stored in global package arrays, which you can reference using the PL/SQL variable APEX_APPLICATION.G_F01 to APEX_APPLICATION.G_F50. In this exercise, the value passed in for product_id is 1, so you reference the column values using the global variable APEX_APPLICATION.G_F01.

5. On Messages:

   1. In Success Message, enter:

      Product(s) deleted.
      

   2. In Failure Message, enter:

      Unable to delete product(s).
      

6. Click Create Process.

7. Run the page.

   Notice that the Delete Products button appears above the report as shown in Figure 6-9. To remove a product from the report, select the Del check box and then click Delete Products.

   Figure 6-9 Product Report with Delete Products Check Box

   
   Description of "Figure 6-9 Product Report with Delete Products Check Box"