This discussion is archived
10 Replies Latest reply: Jun 10, 2013 1:11 PM by KarenH RSS

best way to save checkbox value in a tabular form based on view

KarenH Newbie
Currently Being Moderated
Hi Everyone,

I am using apex 4.2. I am proficient in sql/plus and very very very new to jQuery and javascript.

I have created a tabular form based on a view (landings_collection_view) which looks at a collection 'SPECIES_COLLECTION'.

I am able to update/add/delete to my tabular form using dynamic actions.

I am now trying to hide/show several columns based on the value of a CHECKBOX column and wondering the best way to do it.

Currently I have:

2 page items:
P113_ID
P113_VALUE

Dynamic action called CHANGE COLUMN:
event: CHANGE
selection type: jQUERY Selector
jQuery:
jQuery Select = select[name'f08'],select[name='f09'],input[name='f10'],input[name='f11'],input[name='f12'],select[name='f40'],input[name='f21'],input[name='f22'],input[name='f23'],input[name='f50']

event scope: Dynamic

true action#1: set value P113_ID javascript expression this.triggeringElement.id
true action#2: set value P113_VALUE javascript expression this.triggeringElement.value
true action#3: execute pl/sql code
declare

  v_member number;
  v_seq number;

begin 
  v_member := TO_NUMBER (SUBSTR (:p113_id, 2, 2));
  select ltrim(substr(:p113_ID,5,4),'0') into v_seq from dual;

  safis_collections.update_column(v_seq,
                                v_member,
                                :p113_value);
end;
true ation#4 refresh region :LANDINGS_COLLECTION

the tabular form is based on the query:
select 
NULL del_link, 
v.seq_id, 
v.seq_id seq_id_display,
s.common_name||'-'||m.market_desc||'-'||g.grade_desc||'-'||u.unit_desc species,
v.LANDING_SEQ,
v.SPECIES_ITIS,
v.GRADE_CODE,
v.MARKET_CODE,
v.UNIT_MEASURE,
v.DISPOSITION_CODE,
v.GEAR_CODE,
v.REPORTED_QUANTITY,
v.PRICE,
v.DOLLARS,
v.AREA_CODE_FLAG,
v.C014,
v.ADDITIONAL_MEASURE_FLAG,
v.ADDNL_REPORTED_QUANTITY,
v.ADDNL_UNIT_QUANTITY,
v.AREA_FISHED,
v.SUB_AREA_FISHED,
v.LOCAL_AREA_CODE,
v.FINS_FLAG,
v.FINSATTACHED,
v.FINSNOTATTACHED,
v.FINSUNKNOWN,
v.FINS_CODE,
v.EXPLANATION,
v.NATURE_OF_SALE,
v.HMS_AREA_CODE,
v.SALE_PRICE,
v.HMS_FLAG
from  LANDINGS_COLLECTION_VIEW v, 
      species s, 
      market_categories m, 
      grade_categories g, 
      units_of_measure u
where v.species_itis = s.species_itis and 
      v.market_code = m.market_code and 
      v.grade_code = g.grade_code and
      v.unit_measure = u.unit_measure
Fields F21, F22, F23 correspond to the simple checkbox fields FINSATTACHED, FINSNOTATTACHED, FINSUNKNOWN. The tabular form deals with fisheries management, specifically highly migratory species such as TUNA or SHARK.

my requirements on this tabular form are:
1. If a user checks FINSATTACHED, I need to set FINSATTACHED = Y and FINSNOTATTACHED and FINSUNKNOW both equal to null.
2. if a user checks FINSATTACHED, I need to display the field HMS_AREA (field f40). If FINSNOTATTACHED or FINSUNKNOW is checked, then I must hide HMS_AREA


I have a couple of issues that I am struggling with:

1. how can I save a 'Y' or null value in FINSATTACHED? as this is a view, I cannot use the select apex_item.checkbox....so I am unclear on how to set up the checked/unchecked data.
2. can I hide one field of a tabular form? for example hide HMS_AREA if FINSUNATTACHED or FINSUNKNOW is set to YES in row one...but show HMS AREA in row 2 if FINSATTACHED = YES?


thank you!

Karen
  • 1. Re: best way to save checkbox value in a tabular form based on view
    Joe Upshaw Journeyer
    Currently Being Moderated
    Karen,

    I think I can simply this for you but, it will involve a little jQuery/JavaScript. However, we can do a whole lot with very little code.

    The main thing is getting your jQuery selectors correct.

    Let's say that you've set the Static ID for your report region to MY_FISHY_RR. We can find all rows for which FINSATTACHED is checked like this:
    var finsAttachedInputs = $('#MY_FISHY_RR').find('td[headers="FINSATTACHED"] input:checked');
    That's it. This one line gives us an array containing every line for which the user has checked the FINSATTACHED check box. To walk through this selector a bit, it does this:

    Find the container who's ID is MY_FISHY_RR.
    Within that, find the td tag whose headers property = FINSATTACHED and
    Within that find the inputs (i.e. check boxes) that have been checked.

    So, now we want to hide or show columns.

    Find the rows that contain these checked inputs:
    var finsAttachedRows = finsAttachedInputs.closest('tr');
    So, now we have the rows that contain the checked boxes, now we just hide the columns that we want.
    $(finsAttachedRows).find( 'td[headers="HMS_AREA"]' ).hide();
    So, three lines of code. To find the one's where it isn't checked, the process is the same. Just use :not(:checked) in place of :checked.

    Cheers,

    -Joe

    Edited by: Joe Upshaw on Jun 3, 2013 1:32 PM
  • 2. Re: best way to save checkbox value in a tabular form based on view
    KarenH Newbie
    Currently Being Moderated
    oh my gosh Joe! I can't wait to try this....I will keep you posted and am certain I will have questions (as I always do!), but this is exactly the hint I was hoping for. thanks
  • 3. Re: best way to save checkbox value in a tabular form based on view
    KarenH Newbie
    Currently Being Moderated
    Hi Joe. stuck already. ;)

    My region now has STATIC ID = LANDINGS

    I am not certain now, where to place
    var finsAttachedInputs = $('#LANDINGS').find('td[headers="FINSATTACHED"] input:checked');
    Should that be placed in the existing Dynamic Action? as a new true action? or as the actual INPUT Selector (which I am a little confused on).

    thanks again. Karen
  • 4. Re: best way to save checkbox value in a tabular form based on view
    Joe Upshaw Journeyer
    Currently Being Moderated
    Karen,

    Be sure to note my update above...Apologies but, I just remembered that we made a custom extension for :unchecked and, so; it isn't included in the base jQuery.

    No big deal, just use :not(:checked) to find the rows where it isn't checked.

    Also, as you get more comfortable with selectors you can get down right silly with their concatenation. For example, the whole thing above could have really been done with just one line of jQuery;
    $('#MY_FISHY_RR').find('td[headers="FINSATTACHED"] input:checked').closest('tr').find( 'td[headers="HMS_AREA"]' ).hide();
    and the unchecked ones:
    $('#MY_FISHY_RR').find('td[headers="FINSATTACHED"] input:not(:checked)').closest('tr').find( 'td[headers="HMS_AREA"]' ).show();
    I just can not recommend enough that you allocate time to take the free online jQuery class located at: http://www.codeschool.com/courses/try-jquery. It will seem like magic.

    Cheers,

    -Joe
  • 5. Re: best way to save checkbox value in a tabular form based on view
    KarenH Newbie
    Currently Being Moderated
    thank you again! I really appreciate it....but alas, I am so unfamiliar with this that I am really not certain where to place the statement

    $('#LANDINGS').find('td[headers="FINSATTACHED"] input:checked').closest('tr').find( 'td[headers="HMS_AREA"]' ).hide();


    and thanks for the tip on the jQuery site. I just purchased a book, but think the online training might be more helpful.
  • 6. Re: best way to save checkbox value in a tabular form based on view
    Joe Upshaw Journeyer
    Currently Being Moderated
    You would place it a dynamic action. After Refresh of your LANDINGS region.

    **Event* After Refresh     
    **Selection Type* Region
    **Region* LANDINGS      (or whatever title your report region has)
    Condition     No Condition

    BE sure to pick JavaScript as your action.

    Edited by: Joe Upshaw on Jun 3, 2013 1:55 PM
  • 7. Re: best way to save checkbox value in a tabular form based on view
    KarenH Newbie
    Currently Being Moderated
    ok. here is what I have...can you see where I am going wrong? I am also wondering if the jQUERY Selector is actually formatted properly, as I am not seeing the value P113_VALUE or P113_ID being populated (as it does for others). thanks.


    2 page items:
    P113_ID
    P113_VALUE

    Dynamic action called CHANGE COLUMN:
    event: CHANGE
    selection type: jQUERY Selector
    jQuery:
    jQuery Select = select[name'f08'],select[name='f09'],input[name='f10'],input[name='f11'],input[name='f12'],select[name='f40'],input[name='f21'],input[name='f22'],input[name='f23'],input[name='f50']

    event scope: Dynamic

    true action#1: set value P113_ID javascript expression this.triggeringElement.id
    true action#2: set value P113_VALUE javascript expression this.triggeringElement.value
    true action#3: execute pl/sql code
    declare
    
      v_member number;
      v_seq number;
    
    begin 
      v_member := TO_NUMBER (SUBSTR (:p113_id, 2, 2));
      select ltrim(substr(:p113_ID,5,4),'0') into v_seq from dual;
    
      safis_collections.update_column(v_seq,
                                    v_member,
                                    :p113_value);
    end;
    true ation#4 refresh region :LANDINGS_COLLECTION
    true action#5 execute JAVASCRIPT Code
    var finsAttachedInputs = $('#LANDINGS').find('td[headers="FINSATTACHED"] input:checked');
    
    var finsAttachedRows = finsAttachedInputs.closest('tr');
    
    $(finsAttachedRows).find( 'td[headers="HMS_AREA"]' ).hide();
    selection type: REGION
    Region: LANDINGS_COLLECTION_VIEW.


    as mentioned the static ID of landings view collection = LANDINGS and the heading of f21 is FINSATTACHED.


    thank you!

    ps. I am looking at your last note and am not seeing EVENT=After Refresh...did I misunderstand?

    Edited by: KarenH on Jun 3, 2013 2:17 PM
  • 8. Re: best way to save checkbox value in a tabular form based on view
    Joe Upshaw Journeyer
    Currently Being Moderated
    Hmmm...

    Now that I am getting a little more understanding of your app, my inclination is a bit of a do over :-(

    Firstly, do you really want to update the database every time that they click a checkbox? The more typical use of a tabular form, and one of the main reasons for using it versus a single record view, is the ability to make all of you updates and then to submit them en masse. In contrast, as far as stylistic cues go, the typical thing to do is to make them in real time. So; you generally want to defer database changes until the user is done but, hide/show and the like as folks are clicking things.

    Secondly, the use of the f## syntax is tedious and more importantly can become totally renumbered if you do somethign like add a column. It is a dangerous and brittle way to get a reference (and, yes, I know that all the super smarty pants books do it this way). Much safer is to refer to the column by name using the header attribute. That's why I find this using td[headers="FINSATTACHED"] input rather than #f22.

    Thirdly, there are simple ways to know if you jQuery selector is doing what you expect.

    1. For PL/SQL just stick a debug record in a table and commit.
    2. For JavaScript, use "alert", e.g. alert('I got here!'); This tells you that it selected something. If you want to make sure that it selected the right something, you can see the contents of the triggering element with alert($(myElemenet).html()).

    So, putting this together, I would create separate dynamic actions for each checkbox on the change event. I would make my selectors more mnemonic as shown above, e.g. td[headers="FINSATTACHED"] input. These have a single true action of JavaScript. I would test them with simple alerting as shown above. These would contain all my hide/change logic. So:
    if $(this.triggeringElement).prop('checked') === true)
    {
      // Hide other stuff
    }
    else
    {
      // Show other stuff
    }
    For the save, is there a reason that you can't use the default MRU action?

    -Joe
  • 9. Re: best way to save checkbox value in a tabular form based on view
    KarenH Newbie
    Currently Being Moderated
    all good thoughts/questions.

    I agree,the use of the F## is confusing and I have been snagged a few times...that said, what I really want the column header to ready is FINS<br>Attached?...and that was why I thought using f21 might make more sense....but agreed, it does get confusing, and the tabular form does a lot.

    I also agree that it does not need to update the database, but it does need to update the underlying collection (SPECIES_COLLECTION) with the update members as the page may be refreshed as other things go on. What I really want is for everything happening in the tabular form (checkboxes set, text fields entered, select lists filled in) be reflected on the collection.

    Essentially what my dynamic action does (although not currently working on checkboxes) is to update the underlying SPECIES COLLECTION with any change made on the tabular form. THe actual database updates will take place only when the user hits the SUBMIT button.

    As a user interface, you are correct that as the user moves through this tabular form and checks boxes, or populates lists, other fields may be displayed or hidden. Such is the case of the FINS ATTACHED/UNATTACHED/UNKNOWN question.

    So, based on your last comment, I think I will take out the TRUE ACTION #5 and create a new DA specifically for FINSATTACHED. I will keep you posted and really appreciate your help. thanks
  • 10. Re: best way to save checkbox value in a tabular form based on view
    KarenH Newbie
    Currently Being Moderated

    Hi Joe,

     

    well, I have spent the past several days taking the online jQuery tutorial and it has been helpful in at least my getting a bit more familiar with the concepts...but still sadly a bit stuck.  I am going to re-explain in the hopes that I can better explain the issue.

     

    My tabular form is based on a view of a collection.  The collection is populated once.

     

    The tabular form is based on select lists, text fields, text boxes and checkboxes.

     

    if any tabular item is changed, a dynamic action is fired.  The dynamic action determines the field and row and then calls a database trigger to update the collection.  The actual final update to underlying tables is not done until the very last SUBMIT.  The problem I am having is actually updating the collection (with update member)...specifically on the checkboxes.   I guess I do not know how to provide a value to the checkbox.

     

    I have three checkboxes in the tabular form:  FinsAttached (f21), FinsNotAttached(f22), and FinsUnknown(f23).  There is also a hidden field called FINS_FLAG(f20).

     

    When FinsAttached is checked, the following must happen:  finsNotAttached is unchecked, FinsUnknown is unchecked and Fins_FLAG := 'Y'

    when FinsNotAttached is checked, the following must happen:  FinsAttached is unchecked, FinsUnknow is unchecked and Fins_FLAG := 'N'

    when finsUnknown is checked, the following must happen:  FinsAttached is unchecked, FinNotAttached is unchecked and Fins_FLAG := 'U'

     

    this must happen per row....so if there are three rows, a different value could be set in each row.

     

    Does that make sense?  any thoughts/help?

     

    thank you!

     

    ps.  I had hoped that I might set-up the Dyamic Action with the jQuery Selector:   select[name'f08'],select[name='f09'],input[name='f10'],input[name='f11'],input[name='f12'],select[name='f40'],input[name='f21' value="Y"],input[name='f22' value="N"],input[name='f23' value="U"],input[name='f28'],input[name='f50']


    or else, I thought the following would work:  set the HTML EXPRESSION of the tabular field FINSATTACHED to:  {code}

    <input id="f21_#ROWNUM#" class="#SEQ_ID#" type="checkbox" value="#FINSATTACHED#" maxlength="1" size="45" name="f21" value="Y">{code}

     

    and by doing so, when f21, for example was checked, the value "Y" would be stored in P113_VALUE (earlier post identified above).  but neither seems to do the trick. thanks again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points