Auto numbering rows in a Tabular Form

I have several pages where users enter tasks and steps that need to be sequenced.  The users expressed the desire to not have to manually enter the sequence numbers and have the system automate it with the ability to change them if needed.

Hats off to Jeff Kemp for the following solution which was very easy to implement!

Tabular Form – set default values with jQuery

1. Add a class to the tabular form column that you need to auto number by setting the Element CSS Classes column attribute (“sortorder” in this example).
2. Add the following javascript function to the page that searches through all items in the “sortorder” class, calculates the max value, and increments it:

   function setNextSortOrder() {   var highest=0;   $(".sortorder").each(function(){     if (this.value != "") {       highest=Math.max(highest,parseFloat(this.value));     }   });   $(".sortorder").last().val(highest+1); }

3. Modify the javascript call in your Add Row button to call the function:
   

   javascript:{apex.widget.tabular.addRow();setNextSortOrder();}

Word of caution – this works correctly only if you are displaying all rows at a time.  If you have pagination on your form it will increment the sequence based on what is currently shown, not all records.  I worked around this by setting a page item on page load with the total number of records and modified the function to set the value to either the number of records + 1 or the highest + 1, whichever is greater.  Each time you save the page item storing your number of records will get updated.

Skillbuilders Save Before Exit and false change detection on textareas with carriage returns in IE SOLVED

I have an application with a manual tabular form that contains textareas.  I’ve been using the Skillbuilders Save Before Exit plugin with this mostly successfully, but had a recurring problem in IE 11.  When running the page in IE 11, if the textarea contained a carriage return, the SBE was falsely detecting a change and would prompt the user but would not highlight the field where changes had been detected.  This issue did not occur when running the page in Firefox or Chrome.

After a lot of debugging and research, I tracked the issue down to the hidden field that is generated along with the textarea.  In the hidden field, there was a discrepancy between the value and defaultValue.  In IE the defaultValue rendered carriage returns as \r but rendered the value as \n.  Chrome and Firefox rendered both as \n.

To work around this, since changes did not need to be detected on the hidden field and only on the textarea displayed on the screen, I added a class to my hidden field and then added the class to the Ignore Modifications Selector.  The code for the manually created textarea looks like this:

 apex_item.hidden(3, C012, 'class="sbeIgnore"', 'f03_' || C013) ||
 apex_item.textarea(4,
 C012,
 2,
 30,
 'maxlength="1500" onChange="Text(this)"',
 'f04_' || C013,
 NULL)

And the Ignore Modifications Selector on the SBE dynamic action looks like this:

Generating a Crystal Report from APEX

All of the reports in the application I support are written in Crystal Reports 10.  When migrating the application to APEX, we did not want to have to re-write all of the reports as there are quite a few.  Although I could not find a way to implement a Crystal Report viewer in APEX, I was able to develop a method to generate a .pdf from Crystal and display the report on the screen in .pdf format.

First I developed some setup pages and a reporting tool application that allows users to select a report and enter the parameters.  I used the same methodology as I did in my blog series “Developing Dynamic Tabular Forms” only this time instead of a questionnaire being captured, report parameter selections are being captured.  It’s important to note that as a part of your report parameter setup, you will need to include a sequence column on the report parameters to ensure that the parameters are displayed on the screen in the same order as they appear within your Crystal report.  I also created a “print queue” table and page to allow users to view their print jobs.  Your print queue table needs to include the following:

• name of the report to be generated
• parameter string
• output file name
• status – I used ‘S’ for Submit, ‘P’ for Processing, ‘C’ for ‘Complete’ and ‘E’ for ‘Error’
• the url of the folder where the report output will be generated to

Whenever a report request is submitted, a page process first updates the collection with the values entered in the tabular form and then a second page process cycles through the collection, concatenates the parameters entered together with double pipes, and then inserts a record into the print queue via a database procedure.  I chose double pipes because I needed a delimiter that would not appear within one of the report parameters and I would also be passing the string to a database procedure to insert the print queue record so commas could not be used.

The print queue page that allows users to monitor their print jobs also contains a link column that users can click on to open the .pdf in a separate browser window when it has finished generating.  I put the page on a refresh timer and provided a manual refresh button as well.

This last piece is assuming you have your Crystal reports residing on a Windows server.  You will need to create a script (I used vbscript) that will query your print queue table for records that are in ‘S’ status, cycle through them, make the required API calls to Crystal to generate the report(s) as a .pdf and save to your share folder, and set the status of the print queue records to ‘C’ when complete (or ‘E’ if an error occurred). I also created a .bat file to call the vbscript.  You will then need a scheduled task to run on your server on a regular interval that calls the .bat file.

An Interactive Report with Dynamic Column Headings

I recently developed a tool that would allow users to select a data view and use the built in tools of an Interactive Report to sort, filter, and download the data.  The different views available to the users have a different number of columns and different column headings, so the display of the report had to be dynamic.

In comes collections again.  To accomplish this takes a little bit of time but it is actually quite simple and once it’s setup you can view any data view in the report as long as it contains 50 columns  or less.

First you’ll need a select list to display the list of data views and an interactive report.  Set the Page Action on Selection for the select list to Submit Page and create a page process to create your collection:

DECLARE
 v_sql VARCHAR2(2000);
BEGIN
 IF :P1_VIEW_NAME IS NOT NULL THEN
   v_sql := 'select * from ' || :P1_VIEW_NAME;
   IF apex_collection.collection_exists 
    (p_collection_name => 'EXTRACTION_VIEW') THEN
     apex_collection.delete_collection (p_collection_name => 'EXTRACTION_VIEW');
   END IF;

   IF NOT APEX_COLLECTION.COLLECTION_EXISTS 
    (p_collection_name => 'EXTRACTION_VIEW') THEN
     APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
     p_collection_name => 'EXTRACTION_VIEW',
     p_query => v_sql);
   END IF;
 END IF;
END;

Set your source for the interactive report:

select *
from apex_collections
Where collection_name = 'EXTRACTION_VIEW';

Here’s the tedious part.  You’ll need to create a page item for each of the 50 column headings.  I named mine PX_C0XX_HEADING.  Then in your interactive report you’ll need to do two things:

1. Set the Column Heading for each column to the corresponding page item (for example &P1_C001_HEADING.)
2. In the Conditional Display for each column, set the condition type to Exists (SQL query returns at least one row) and enter the following, substituting the appropriate column name:

select 1
from apex_collections
where c0XX is not null
and collection_name = 'EXTRACTION_VIEW';

The final piece is to populate the column heading page items.  I created a database function to determine the column name:

CREATE OR REPLACE FUNCTION get_extraction_column_heading (p_view_name IN VARCHAR2
 , p_data_type IN VARCHAR2, p_column_id IN NUMBER) RETURN VARCHAR2 IS
  CURSOR get_column_heading_c IS
  SELECT column_name
  FROM all_tab_columns
  WHERE table_name = p_view_name
  AND column_id = p_column_id;

  v_column_name all_tab_columns.column_name%TYPE;
BEGIN
  OPEN get_column_heading_c;
  FETCH get_column_heading_c INTO v_column_name;
  CLOSE get_column_heading_c;

  RETURN v_column_name;
END;

Then create an After Header computation for each column heading page item to call the function:

get_extraction_column_heading(:P1_VIEW_NAME, 'VARCHAR2', 1);

The number for the last parameter (P_COLUMN_ID) will correspond to the number of the column heading.

Note:  You may want to consider putting the same condition that you put on your columns on the computations for the column headings as well to eliminate unnecessary database calls.  I did not have any issues with this on my production application, but when creating the demo app I recieved numerous I/O errors which resolved after putting the condition on the computations.

You can access the demo application here (credentials are guest/demo): Dynamic IR Column Headings Demo

Collections and Manual Tabular Forms – A powerful combination

When I first started developing in APEX, I wanted to use everything out of the box because it was so new to me I wasn’t sure what I was doing.  I soon realized this was not going to cut it with my users who were used to very customized Oracle Forms applications.

I was challenged with creating an application to allow users to essentially fill out a questionnaire.  Sounds straight forward, but the catch was that the questions could potentially need to be changed, and some questions would need to be answered with a Yes/No, a list of values selected from the database, a date, or a free-form entry text field.  The different types of answers would have to be mixed together within the same tabular form because the questions had to be in a particular sequence.  In addition, some calculations would need to be performed based on the answer to the question.

Through the use of some basic tabular form pages to allow the questions to be setup, a manually created tabular form based on a collection, and a database package to generate the sql used to create the tabular form I was able to accomplish this.

You can access the demo application here (credentials are guest/demo): Questionnaire Demo Application

I will go through the different aspects of developing this through a blog post series I will call “Developing Dynamic Tabular Forms.”

Developing Dynamic Tabular Forms Part 1 – Questionnaire Setup Page

The first step in creating the questionnaire application is to build your setup page.  You will need a table and page(s) to capture the following:

1. question sequence number
2. question text – if your questions will be detailed/lengthy as mine were, you will want this to be in a text area
3. nullable flag (Y/N) – used to indicate if the question can be left blank
4. validation type (Character/Numeric/Date) – this will help drive the type of control to display for the answer
5. LOV sql text – this should be an adequately size column with a text area to allow for a sql statement to be entered, more on this later
6. large list flag (Y/N) – more on this later
7. multi-select LOV flag (Y/N) – used to indicate if the LOV displayed allows for multiple values to be selected
8. calculation SQL – this should be an adequately size column with a text area to allow for a sql statement to be entered, more on this later

I had originally started off making the setup screen a tabular form, but as the project went on additional flags needed to be added to handle other functionality not relevant to this discussion so I switched to an interactive report with a edit link to a pop-up page to enter each question individually.  Otherwise, the screen was too wide to view without scrolling and it made entry very painful.

The LOV sql text field will be used to generate the list of values needed for the field.  In my setup, if this field is blank and the validation type is “Character” then a free-form entry text field will be displayed.  Enter a valid SQL statement here to pull your LOV from a database table, or if you need a static LOV you can enter something like this:

select 'Yes' d, 'Y' r
from dual
union
select 'No' d, 'N' r
from dual
order by 2 desc

The large list flag is used to trigger whether the LOV will be displayed as a simple select list, or as a pop-up LOV.  This is necessary because there is a known limitation that restricts the size of the HTML generated to implement a select list control to 32KB.  If the results of your query will exceed 32KB, you will want to set this flag to “Y” so that a pop-up LOV will be displayed instead.  This can sometimes be trial and error, but if you get an ORA-06502: PL/SQL: numeric or value error: character string buffer too small error, chances are you need to set this flag to “Y” for one or more of your questions.

The last column that needs further explanation is the calculation SQL field. For this project, I needed to be able to calculate a score based on the answers on the questionnaire.  I accomplished this by entering a SQL statement for each question that impacted the score.  I will cover how this is used in a later post, but here is an example of a SQL statement used:

select case answer
when 'Y' then 4 end value
from questionnaire_answers
where answer_id = :1

You will also want to create a second table to store your answers that contains a foreign key to the questionnaire table.

In the next post in this series, I will show you how to write the database package that builds the sql statement used to generate the tabular form.

Developing Dynamic Tabular Forms Part 2 – Building the dynamic SQL statement

My first rule of APEX development is put as much into database stored procedures as possible.  This keeps your application code cleaner, more efficient and makes maintenance easier.  As mentioned earlier, this project is built on a manual tabular form based on a collection.  The SQL to create the collection is actually very simple, but the SQL to generate the tabular form itself is not.  Rather than trying to write the statement for the tabular form within the region, I opted to create a database function that returns a SQL statement string the region can use.  This function is contained within a package that the application uses.

When an instance of the questionnaire is generated, I automatically build all the records in the answers table for the questionnaire.  I then created a view to join the questionnaire table with the answers table, which is what the collection is generated from.

FUNCTION get_tabular_form_sql RETURN VARCHAR2 IS
 v_sql VARCHAR2(10000);
BEGIN 
 v_sql := v_sql || 'select apex_item.display_and_save(1, C002) SEQUENCE_NO, ';
 v_sql := v_sql || 'case ';
 v_sql := v_sql || 'when C014 = '||''''||'Y'||''''||' then ';
 v_sql := v_sql || ' apex_item.textarea(2, C004, 4, 100, '||''''||'readonly="readonly" style="border: none"'||''''||','||''''||'f02_'||''''||'||C013, NULL) ';
 v_sql := v_sql || 'else ';
 v_sql := v_sql || ' apex_item.display_and_save(2, C004) ';
 v_sql := v_sql || 'end as QUESTION_TEXT, ';
 v_sql := v_sql || 'case ';
 v_sql := v_sql || 'when C007 is null then ';
 v_sql := v_sql || ' case C006';
 v_sql := v_sql || ' when '||''''||'D'||''''||' then ';
 v_sql := v_sql || ' apex_item.hidden (3, C012, NULL, '||''''||'f03_'||''''||'||C013) || apex_item.date_popup(4, NULL, C012, '||''''||'DD-MON-RRRR'||''''||', 20, 2000, '||''''||'onChange="dateField(this)"'||''''||', '||''''||'f04_'||''''||'||C013, NULL) ';
 v_sql := v_sql || ' when '||''''||'N'||''''||' then ';
 v_sql := v_sql || ' apex_item.hidden (3, C012, NULL, '||''''||'f03_'||''''||'||C013) || apex_item.text(4, C012, NULL, 1500, '||''''||'onChange="Text(this)"'||''''||', '||''''||'f04_'||''''||'||C013, NULL) ';
 v_sql := v_sql || ' else ';
 v_sql := v_sql || ' apex_item.hidden (3, C012, NULL, '||''''||'f03_'||''''||'||C013) || apex_item.textarea(4, C012, 2, 30, '||''''||'maxlength="1500" onChange="Text(this)"'||''''||', '||''''||'f04_'||''''||'||C013, NULL) '; 
 v_sql := v_sql || ' end ';
 v_sql := v_sql || 'else ';
 v_sql := v_sql || ' case ';
 v_sql := v_sql || ' when nvl(C009, '||''''||'N'||''''||') = '||''''||'Y'||''''||' then ';
 v_sql := v_sql || ' apex_item.hidden (3, C012, NULL, '||''''||'f03_'||''''||'||C013) || apex_item.select_list_from_query(4, NULL, C007, '||''''||'multiple="multiple" onChange="selectList(this);"'||''''||', '||''''||'NO'||''''||', NULL, NULL, '||''''||'f04_'||''''||'||C013, NULL, '||''''||'NO'||''''||') ';
 v_sql := v_sql || ' when nvl(C008, '||''''||'N'||''''||') = '||''''||'Y'||''''||' then ';
 v_sql := v_sql || ' apex_item.hidden (3, C012, NULL, '||''''||'f03_'||''''||'||C013) || apex_item.popup_from_query(4, C012, C007, NULL, NULL, NULL, NULL, NULL, '||''''||'readonly="readonly" onChange="popUp(this);"'||''''||', '||''''||'YES'||''''||', NULL, '||''''||'f04_'||''''||'||C013) ';
 v_sql := v_sql || ' else ';
 v_sql := v_sql || ' apex_item.hidden (3, C012, NULL, '||''''||'f03_'||''''||'||C013) || apex_item.select_list_from_query(4, C012, C007, '||''''||'onChange="selectList(this)"'||''''||', '||''''||'YES'||''''||', NULL, '||''''||'- Select -'||''''||', '||''''||'f04_'||''''||'||C013, NULL, '||''''||'YES'||''''||') ';
 v_sql := v_sql || ' end ';
 v_sql := v_sql || 'end as ANSWER,';
 v_sql := v_sql || 'case ';
 v_sql := v_sql || 'when nvl(C009, '||''''||'N'||''''||') = '||''''||'Y'||''''||' then '; 
 v_sql := v_sql || ' apex_item.textarea(6, REPLACE(C012, '||''''||':'||''''||', CHR (13)||CHR (10)), 4, 40, '||''''||'readonly="readonly" style="border: none" class="selected"'||''''||') ';
 v_sql := v_sql || 'else ';
 v_sql := v_sql || ' apex_item.display_and_save(6, NULL) ';
 v_sql := v_sql || 'end as SELECTED_VALUE, ';
 v_sql := v_sql || 'apex_item.display_and_save(7, C005) NULLABLE, ';
 v_sql := v_sql || 'apex_item.display_and_save(8, C013) ANSWER_ID ';
 v_sql := v_sql || 'from apex_collections ';
 v_sql := v_sql || 'where collection_name = '||''''||'QUESTIONNAIRE'||''''||' ';
 v_sql := v_sql || 'order by seq_id ';
 
 RETURN v_sql;
END get_tabular_form_sql;

Here are what the collection columns referenced in the SQL are:

• C002 – question sequence number
• C014 – this is a flag I calculated in the view that indicates whether the question text contains any carriage returns.  If it does, we want to display the question in a read-only text area, otherwise display it in a text field.
• C004 – question text
• C007 – the LOV sql text
• C006 – the validation type
• C012 – the answer for the question from the answer table
• C013 – the primary key value from the answer table
• C009 – the multi-select LOV flag
• C008 – the large list flag
• C005 – the nullable flag

If you use APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY to build your collections, the order of the columns in your sql statement/view to create the collection should correspond to C0XX.  However, a lot of times just to double check, I will create an interactive report region on my page to display the data in the collection that I later hide or delete from the page.  This can be especially helpful if you generate a collection via APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 and have data in a mix of different column types.

In the next post, we will start working on the actual page itself and bring this all together.

Developing Dynamic Tabular Forms Part 3 – Creating the tabular form

So far we’ve created our setup page and database function to generate the SQL used by our tabular form.  Now it’s time to start putting it all together.

The first thing you will need on your tabular form page is a Before Header process to create the “QUESTIONNAIRE” collection.  This will query the view mentioned in the last post.  You will want to only query the view for the instance of the questionnaire you want the user to answer, but for simplicity’s sake let’s just assume here there is only one questionnaire.

Next create Classic Report region based on a SQL query.  In the region source you will want to call the function we created in the last post and click on the “Generic columns” radio button:

declare
 v_sql varchar2(10000);
begin
 v_sql := questionnaire_pkg.get_tabular_form_sql;

 return v_sql;
end;

Make sure after your region is created you set the Display As attribute for each column to “Standard Report Column.”  After you run it the first time there will be some cleanup needed to uncheck the Show checkbox for any columns you won’t be needing to display.

You’ll also need to add some javascript to your page.  In our last post, notice in the SQL that the fields generated have an “onchange” event included.  Due to the way that APEX stores the values that are selected/entered, we need to grab the values and put them into the hidden field attached to each entry field.  This will allow us to easily cycle through the tabular form to update the collection when the user submits the page.  Add the following in the Function and Global Variable Declaration section of your page:

function selectList(p_this) 
{ 
 var l_selected = html_SelectValue(p_this);
 if (l_selected.constructor == Array) l_selected = l_selected.join(':');
 p_this.parentNode.firstChild.value = l_selected;
 return l_selected;
}

function Text(p_this) 
{ 
 var l_text = $v(p_this);
 p_this.parentNode.firstChild.value = l_text;
 return l_text;
}

function popUp(p_this) 
{ 
 var label = p_this.previousSibling.innerHTML;
 var id = label.substring(4);
 var l_selected = html_SelectValue(p_this);
 $x_Value('f03_'+id, l_selected);
 return l_selected;
}

function dateField(p_this)
{
 var id = p_this.id.substring(4);
 var l_date = $v(p_this);
 $x_Value('f03_'+id, l_date);
 return l_date;
}

Note:  In order to use this functionality, you will need to ensure that in the Javascript section of your application’s User Interface Details that you have Include Legacy Javascript set to “Yes”.

At this point you should be able to run your page and see a tabular form with the questions you previously setup, with a mixture of different controls for users to answer the questions with.

You’ll notice in this example there is an extra column on the right side.  One of the gotchas I ran into is that there does not appear to be a way in a tabular form to show the selected values in a multi-select list when the data is re-queried back onto the screen.  To work around this, if the tabular form contains any multi-select lists, an additional field containing a read-only text area is included for the purposes of displaying the previously selected values that are saved to the database.  The SQL in the last post handles the generation of this additional text area and in your tabular form you just need to set this column as conditional based on the following SQL statement so that it won’t display if your questionnaire doesn’t contain any multi-select lists:

select 'X'
from apex_collections
where collection_name = 'QUESTIONNAIRE'
and C009 = 'Y';

Another gotcha is that with the multi-select lists and pop-up LOV’s (not standard select lists) used in this way, the display and return values need to be the same (or at least have return values that make sense to the user), because what is displayed on the screen after refresh will be the return value so keep this in mind when entering your LOV SQL Text.  I could not find a way to get around this, but fortunately with the data I was working with this wasn’t a big deal.

In the next post, we’ll talk about how we update the collection and the database.

Developing Dynamic Tabular Forms Part 4 – Saving the data in the tabular form

When the user Submits their changes, you’ll first need to update the collection.  Since we used the javascript to store the answers in the hidden field this is simple to do.  Create an On Submit – Before Computations and Validations with the following PL/SQL:

FOR i IN 1 .. apex_application.g_f01.COUNT LOOP
 apex_collection.update_member_attribute( 
 p_collection_name=> 'QUESTIONNAIRE' 
 , p_seq => i
 , p_attr_number => '12' 
 , p_attr_value=> wwv_flow.g_f03(i)); 
END LOOP;

You’ll then need an On Submit – After Computations and Validations process to handle updating the underlying table from the collection.  There are a few different ways this could be done.  I chose to define an array in the database package that my application uses.  The array is populated in the process and is passed to a database procedure.

Here’s what my On Submit – After Computations and Validations code looks like:

DECLARE
 CURSOR get_answers_c IS
 SELECT c012, c013
 FROM apex_collections
 WHERE collection_name = 'QUESTIONNAIRE'
 AND c012 IS NOT NULL;

 answer_record questionnaire_pkg.answer_rec;
 answer_array questionnaire_pkg.answer_array_type;
 
 v_current_record BINARY_INTEGER := 1;
BEGIN
 FOR rec IN get_answers_c LOOP
   answer_record.answer_id := rec.c013;
   answer_record.answer := rec.c012;
 
   answer_array(v_current_record) := answer_record;
   v_current_record := v_current_record + 1;
 END LOOP;

 IF answer_array.COUNT > 0 THEN 
   questionnaire_pkg.update_answers(answer_array);
 END IF;
END;

The “update_answers” database procedure then cycles through the array and does the update in the underlying table that stores the answers.

In my final post in this series, I’ll show you how to use the calculation SQL field we talked about in the first post to calculate a score based on the questionnaire answers.

Developing Dynamic Tabular Forms Part 5 – Performing calculations

The final piece of my project is to calculate a score based on the answers to the questionnaire.  This calculation will be done using the calculation SQL text field we setup in Part 1.

In my database package, I created a calculation function that will return the score.  This function simply loops through a query that joins the questionnaire table with the answer table and performs an “EXECUTE IMMEDIATE” to execute the calculation SQL text and keeps a running total.

FUNCTION calculate_score RETURN NUMBER IS
  CURSOR get_answers_c IS
  SELECT score_calculation, answers.answer_id
  FROM answers, questions
  WHERE answers.question_id = questions.question_id
  AND score_calculation IS NOT NULL
  AND answers.answer IS NOT NULL; 

  v_score_temp NUMBER;
  v_score NUMBER;
BEGIN
  v_score_temp := 0;
  v_score := 0;
 
  FOR rec IN get_answers_c LOOP
    BEGIN
      EXECUTE IMMEDIATE rec.score_calculation INTO v_score_temp USING rec.answer_id;
      v_score := v_score + v_score_temp;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      CONTINUE;
    END;
  END LOOP;

  RETURN v_score;
END;