3

I have the following tabular report using the following query:

select id,
       name,
       telephone,
       apex_item.checkbox2(1,id) as "Tick when Contacted",
       apex_item.text(20,my_date) as "Date Contacted",
       apex_item.textarea(30,my_comment,5,80) as "Comment"
from   my_table

This report displays 10 records where the driving key is the checkbox assigned to F01.

My problem is, as this is a tabular report, using Oracle APEX_APPLICATION.G_F01.COUNT - how can I access the values of the textarea field, where "my_comment" is a user enterable value on the report and not from a database column/table?

From what I can see, it seems to be a sequence issue and if the records you enter are not in the correct order then values are missed.

I am only ticking the checkbox for row 1, 3 and 5 and so expect to return the values for textarea fields that relate to these selected rows only.

halfer
  • 19,824
  • 17
  • 99
  • 186
tonyf
  • 34,479
  • 49
  • 157
  • 246

1 Answers1

6

Yes, it gets tricky when your tabular form contains checkboxes. In your example, g_f01 will only contain 3 elements with values 1, 3, 5 but array g_f30 will contain 10 elements.

usually when using apex_item to build tabular forms it is best to also use an APEX collection:

  1. Populate the APEX collection on entry to the page with the relevant data from my_table. Hold the ID of the mytable rows in a hidden item e.g. apex_item.hidden(2,id).
  2. Write the report to work from the collection rather than my_table, and to use seq_id rather than ID in the checkbox item: apex_item.checkbox2(1,seq_id)
  3. On submit, use the g_fxx arrays to update the collection - oftne using more than one pass.
  4. Finally use the collection to update my_tabl

So in your example you might first update the APEX collection with to indicate which rows have been ticked by setting c050 to 'Y':

for i in 1..apex_application.g_f01.count loop
    apex_collection.update_member_attribute('MYCOLL', apex_application.g_f01(i), 
      50, 'Y');
end loop;

Then update it with the other changes:

for i in 1..apex_application.g_f02.count loop
    apex_collection.update_member_attribute('MYCOLL', apex_application.g_f02(i), 
      20, apex_application.g_f20(i));
    apex_collection.update_member_attribute('MYCOLL', apex_application.g_f02(i), 
      30, apex_application.g_f30(i));
end loop;

Finally apply the relevant changes to my_table:

for r in (select c002, c020, c030 
          from apex_collection
          where collection_name = 'MYCOLL'
          and c001 = 'Y' -- Checked rows only
         )
loop
    update my_table
    set my_date = r.c020
    ,   my_comment = r.c030
    where id = r.c002;
end loop;

Simple as that...?!

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Crystal clear ;) I think Tonsils had this question/confusion after my answer in http://stackoverflow.com/q/12142143/814048. My first answer was wrong, so wrong. Not that i don't know this, i must've just had a lapse of judgement: i even tested the damn code and the output showed i was wrong! I amended it now. But wouldn't the end result of the code there be the same to that of the collection-based answer? Would there actually be much of an advantage here to use a collection? – Tom Aug 29 '12 at 12:07
  • Probably not so much advantage of a collection here. It is just my default setting: if I'm using apex_item then I'm usually using a collection too! Your method would be simpler for this case, mine is probably overkill. – Tony Andrews Aug 29 '12 at 12:14
  • Appreciate both your answer Tony as well as your amended version Tom. Thanks guys. I feel like that we are the only three people on SO that seem to be using Oracle ApEx.... – tonyf Aug 29 '12 at 14:29