Plug & play tabular form handling

I still remember the time when I was an APEX newbie and I really got confused with tabular forms (updateable reports). They behaved very different from what I knew about "normal" page items. So what where the issues which confused me?

• It isn't possible to create any validations on the columns of the report, like I can do it for page items.
• You have to use the APEX_Application.g_fxx arrays instead of using bind variables as for page items.
• To find the correct array you have to look into the generated HTML code for the correct mapping.
• If the order of the columns gets changed, your code gets screwed up, because the columns now maps to a different g_fxx array.
• If a column has a condition or authorization the g_fxx array changes again, in the case the column doesn't get rendered.
• The code isn't really readable if there are just references to g_f01, g_f02, ...
• The format mask has to be duplicated if the g_fxx is accessed and you want to get the correct data type (eg NUMBER, DATE)
• When you want to get the number of array entries you have to take care that you don't use the array for a checkbox column, because that just contains the checked values!
• You have no function to find out if the row has changed.
• When the validation of a format mask or required column fails, the error message is very internal. The user doesn't have a clue which column and which row was effected. And it stops after the first error.

That's a long list and that's why I did some programming on the weekend to get a solution for the above short comings, so that newbies and experienced APEX developers don't have to care anymore.

To make a long story short, I have created a package which you just call once in your application and which takes care of the above problems. It allows you to

• reference your columns by name without having to hassling around to which array your column is mapped.
• To get the correct number of rows to process
• Identify which row has changed
• Get your data with the correct data type (NUMBER, DATE) without having to take care about format masks.
• Add a required column check by declaration, instead of coding it.
• Get more meaningfull error message than APEX provides.

But first try it by yourself, I have set up a tabular form with the default APEX handling for tabular forms. Follow the instructions and play around with it.

Standard Tabular Form example

After you have done that, check out my

Enhanced Tabular Form example.

How to integrate it into your application?

Follow the steps in my How to integrate posting.

In the case if you don't want that one of your report regions is processed by the library, add $APEXLIB_IGNORE_VALIDATION$ in the region comment to skip the processing.

That's the basic integration which does the required column and format mask check. If you want to access your column in your own validation code, follow the example to see how to do that.
Example

1. Create a new page
   • Page Type: Form/Tabular Form
   • Allowed Operations: Update, Insert and Delete
   • Table / View Name: EMPLOYEES
   • Select Columns(s): Everything except of PHONE_NUMBER, COMMISSION_PCT, MANAGER_ID and DEPARTMENT_ID
   • Primary Key Column1: EMPLOYEE_ID
   • Source Type: Existing trigger
   • Updateable Columns: ALL
   • Confirm everything on the following pages
2. Open the Report Attributes of the created region.
   • For the columns LAST_NAME, EMAIL, HIRE_DATE, JOB_ID go to the Column Link section and enter $APEXLIB_REQUIRED$ as Link Attributes
   • For the column HIRE_DATE set the following properties
     • Display As: "Date Picker"
     • Date Picker Format Mask: "DD-MM-YYYY" => we use a different format mask than the database has, so that auto date convertion doesn't work!
     • Element Width: 10
   • For the column JOB_ID set the following properties
     • Display As: Select List (query based LOV)
     • LOV Query:

       SELECT JOB_TITLE AS D, JOB_ID AS R FROM JOBS ORDER BY JOB_TITLE

   • For the column SALARY set the following properties
     • Number / Date Format: FML999G990D00
3. Let's create some extra validation, so that you see how easy it is to access your columns. Create a validation for the created page
   • Identify the validation level: Page level validation
   • Select a validation method: PL/SQL
   • Select the type of PL/SQL validation you wish to create: Function Returning Error Text
   • Sequence: 10
   • Validation Name: Validate Hire Date and Salary
   • Error Display Location: On Error Page
   • Validation:

     
     DECLARE
         vMinSalary NUMBER;
         vMaxSalary NUMBER;
     BEGIN
         FOR ii IN 1 .. ApexLib_TabForm.getRowCount
         LOOP
            IF ApexLib_TabForm.hasRowChanged(ii)
            THEN
                IF ApexLib_TabForm.DV('HIRE_DATE', ii) > SYSDATE
                THEN
                    ApexLib_Error.addError
                      ( pError      => 'Hire Date is in the future'
                      , pColumnName => 'HIRE_DATE'
                      , pRow        => ii
                      );
                END IF;
                --
                SELECT MIN_SALARY
                     , MAX_SALARY
                  INTO vMinSalary
                     , vMaxSalary
                  FROM JOBS
                 WHERE JOB_ID = ApexLib_TabForm.V('JOB_ID', ii)
                ;
                IF ApexLib_TabForm.NV('SALARY', ii) NOT BETWEEN vMinSalary AND vMaxSalary
                THEN
                    ApexLib_Error.addError
                      ( pError      => 'Salary has to be between %0 and %1'
                      , pColumnName => 'SALARY'
                      , pRow        => ii
                      , p0          => vMinSalary
                      , p1          => vMaxSalary
                      );
                END IF;
            END IF;
         END LOOP;
         --
         RETURN ApexLib_Error.getErrorStack;
     END;
     

   • Error Message: Error occurred.

As you can see in the code, you don't have to care about date convertion, you are only processing the changed records. The code is still understandable when you look at the code 5 days later, aren't names more meaningful than just references
to APEX_Application.g_f01? ;-)

You have different getter functions to access your columns. ApexLib_TabForm.V, -NV and -DV have the same purpose as the existing APEX functions which you can use for items. Check out the package specification of the ApexLib_TabForm package, all the functions are documented.

BTW, if you are not satisfied with the error message the validateColumns procedure is raising for required and format mask check. You can define your own error text by creating a message (Shared Components/Text Messages). The name has to be APEXLIB_TABFORM_COLUMN_REQUIRED (Format: Column "%0" is required.) or APEXLIB_TABFORM_INVALID_FORMAT (Format: Column value of "%0" doesn't match to the format "%1".) The row prefix can be define with APEXLIB_TABFORM_ROW_PREFIX (Format: Row %0: ).

Impact on your existing application

Nothing really changes except that you have better error messages in your tabular form, so it's no problem to use it also for your existing application.

Update

• Check out the Accessing the row selector of a tabular form for an example how you can access the row selector.
• Check out Hacking the APEX error page to display the errors as inline message.
• Check out Hacking the APEX error page - Part 2 to highlight the column cells which have an error.

Known restrictions

• If you use APEX_Item or HTMLDB_Item to build your own form elements. I can't map the columns, because I don't know which index you have used.
• If a column has a condition or authorization. See below.

Problems?

Enable debugging, it will show you what the library is doing and which columns are mapped to which array. Look for ApexLib_TabForm in the output.

What's next?

I have now a question. Where do you install the packages? Are you allowed to install them into the FLOWS schema? Because that would make the condition checking a breeze. If there is a big demand to just install the packages into the application schema, I have to re-code the condition/authorization checking, because it's not accessable from the application schema.

Do you have other ideas and troubles with tabular form handling which you want to automate? Feedback very welcome!