1 2 Previous Next 23 Replies Latest reply: Jun 20, 2013 1:26 AM by 60c2e771-9773-45ab-973f-f990733a47d1 RSS

    Radio button column in the tabular report

    210640
      Hi everyone,
      I have a tabular report. I wanted to have radio button and check box some of the field. Please take a look at my sample application.

      http://apex.oracle.com/pls/otn/

      Workspace ==> SHYIN
      username ==> SHY
      password ==> shy


      Application ID : 29879 - TabularForm

      On page 2, of the detail form, I want to have radio button in Primary Contact column and check box in status column. I would appreciate any suggession. Thanks.

      SHY
        • 1. Re: Radio button column in the tabular report
          ZKay
          Hi Shy,

          I followed an example set by Andy to create radio buttons on tabular form last month. You can access Andy's example on the following url:

          http://apex.oracle.com/pls/otn/f?p=33642:193

          I tested the above example for radio buttons, you might be able to create check boxes as well with some minor changes.

          Cheers,
          Zahid
          • 2. Re: Radio button column in the tabular report
            210640
            Hi Zahid,
            I saw that example, but in my case, instead of having two radio for each row, I want only one. The reason for that is for each customer, I can only have one primary contact. I have hard time modifying that example. I would appreciate if anyone could guide me on this. Thanks.
            SHY
            • 3. Re: Radio button column in the tabular report
              ATD
              Hi,

              Have a look at: Re: creating a yes/no radiogroup....if selected then YES, else NO in tabular - this refers to: [http://apex.oracle.com/pls/otn/f?p=55041:18] which deals with selecting a primary employee on the EMP table

              Andy
              • 4. Re: Radio button column in the tabular report
                210640
                Thanks Andy. That is exactly what I am looking for. I will incorporate that in my code and let you know the feedback, or may be coming again with more questions :)
                SHY
                • 5. Re: Radio button column in the tabular report
                  ATD
                  np, SHY, just update this thread if you need anything further on this

                  Andy
                  • 6. Re: Radio button column in the tabular report
                    210640
                    Hi Andy,
                    Honestly speaking, I am pretty new to apex and have no experience using collection before. I tried the link that you mentioned. But I am not getting it. I think I need some guideline where to create which process.

                    Here are the steps that I followed so far.
                    1. I created the collection in before header
                    2. I created the report based on the collection.
                    3. Add two buttons "Add New Rows" and "Update"
                    4. Add process to add collection members to execute in "Add New Rows"

                    My difficulties are
                    - in the report, I see the records from the table, but the radio button is not selected for the primary contact record.
                    - When I click "Add New Rows", no new records are created

                    So, I think, I am not creating the process in the proper place or I don't understand some logic. Can you please guide me through this? Thanks.

                    SHY
                    • 7. Re: Radio button column in the tabular report
                      ATD
                      Hi SHY,

                      OK - I've made a few changes to your pages.

                      On page 1, I have added a process that checks for the collections and deletes it if it exists. This is so that when the user clicks an Edit link on a customer, they do not get any old collection data.

                      On page 2, I've updated the Before Header process to only create the collection if it does not exist - this is needed to allow you to submit the page and/or add new records without clearing the collection.

                      The javascript function on page 2 was still referring to "f01" for the radio buttons - you have these in "f03", so I've changed this. There was also a bit of confusion over the name of the hidden page item that holds the primary ID selected - I've made sure that these all show as P2_SELECTED_ID. The source of this also needed to be filtered by CUSTOMER_ID - or, at least, I assume that this is your intention, to have one primary per customer?

                      I have also updated the source of the tabular form to get the values for the radiobuttons from c001 instead of c003. c001 contains the ID for the item and c003 contains either Y or N. The javascript code needs to know the ID so that it can match it against the entry in P2_SELECTED_ID and either tick or untick the item.

                      The page, given these changes, now works ok. The only thing left is to add in the process to update the actual table with the selected item. Hopefully, my code in the other thread will guide you through this, but let me know if you need further help.

                      Andy
                      • 8. Re: Radio button column in the tabular report
                        210640
                        Hi Andy,
                        Thanks you for guiding me through this. I know that you have been very patient with me. But I am still having problem. I have added two after submit processes to Update Collection items and Save the data to the database, following the code that you provide in the previous forum link. But got null for all the fields except primary key field and customer id field(foreign key field). Can you please let me know where I did wrong?

                        here is my Update_Collections process
                        ---------------------
                        declare
                        v_c003 char(1);
                        v_c001 number;
                        begin
                        for x in 1..apex_application.g_f01.COUNT
                        loop
                        v_c001 := apex_application.g_f01(x);

                        if v_c001 = :P2_SELECTED_ID then
                        --htp.p('yes');
                        v_c003 := 'Y';
                        else
                        --htp.p('no');
                        v_c003 := 'N';
                        end if;

                        apex_collection.update_member (
                             p_collection_name => 'C_CONTACT_INFO',
                             p_seq => v_c001,
                             p_c001 => apex_application.g_f01(x),
                             p_c002 => apex_application.g_f02(x),
                             p_c003 => v_c003,
                             p_c004 => apex_application.g_f04(x),
                             p_c005 => apex_application.g_f05(x),
                             p_c006 => apex_application.g_f06(x));
                        end loop;
                        end;
                        ---------------------------------

                        here is my Save_Contacts process
                        ----------------------------
                        begin
                        delete from contacts where customer_id = :P2_CUSTOMER_ID;

                        insert into contacts (ID, NAME, PRIMARY_CONTACT, PHONE , STATUS, CUSTOMER_ID)
                        select c001, c002, c003, c004, c005, :P2_CUSTOMER_ID
                        from apex_collections
                        where collection_name = 'C_CONTACT_INFO';

                        commit;
                        end;
                        ----------------------------
                        Both are after submit process. I also added success message to check and see all the processes are being executed. I saw both update and save processes being executed according to success message. Can you please guide me where I did wrong? Thanks Andy.
                        • 9. Re: Radio button column in the tabular report
                          ATD
                          Hi,

                          The issue is with the apex_item.hidden() items - these can not be used as separate columns but must be concatenated before a displayable item. For example:

                          Instead of:
                          SELECT APEX_ITEM.HIDDEN(1,FIELD1) FIELD1,
                          APEX_ITEM.TEXT(2,FIELD2) FIELD2
                          FROM TABLEX
                          it should be:
                          SELECT APEX_ITEM.HIDDEN(1,FIELD1) || APEX_ITEM.TEXT(2,FIELD2) FIELD2
                          FROM TABLEX
                          I think that you had hidden the first FIELD1 column which means that Apex does not render it as part of the page, so the f01 items are not available to your code. Using the second method, means that the item is part of the page, even though it is hidden from the user, and is, therefore, available to your code.

                          I'll update your page to handle this and get back to you

                          Andy
                          • 10. Re: Radio button column in the tabular report
                            ATD
                            Hi,

                            OK - have a look at your page now.

                            I have made a few of changes:

                            1 - Updated the tabular form's SQL to:
                            SELECT
                            APEX_ITEM.HIDDEN(1, SEQ_ID) || APEX_ITEM.HIDDEN(2, C001) || APEX_ITEM.RADIOGROUP(3, C001, NULL, NULL, 'onclick="javascript:$x(''P2_SELECTED_ID'').value=' || C001 || '"') PRIMARY_CONTACT,
                            apex_item.text(4,c002) name,
                            apex_item.text(5,c004) phone,
                            APEX_ITEM.HIDDEN(6, C006) || apex_item.text(7,c005) STATUS
                            from apex_collections 
                            where collection_name = 'C_CONTACT_INFO'
                            So all HIDDEN items are concatenated in front of displayable items.

                            2 - Updated your Update_Collections process to be unconditional. This is needed as the page can be submitted by one of three ways - Save, Update and Add Row - and the collection needs to be updated for all three

                            3 - Changed P2_SELECTED_ID to remove the source settings

                            4 - Created a new page process (PL/SQL, On Load - Before Header):
                            DECLARE
                             vID NUMBER;
                            BEGIN
                             SELECT MIN(ID) INTO vID FROM CONTACTS WHERE PRIMARY_CONTACT = 'Y' AND CUSTOMER_ID = :P2_CUSTOMER_ID;
                             IF vID IS NULL THEN
                              SELECT MIN(ID) INTO vID FROM CONTACTS WHERE CUSTOMER_ID = :P2_CUSTOMER_ID;
                              IF vID IS NOT NULL THEN
                               UPDATE CONTACTS SET PRIMARY_CONTACT = 'Y' WHERE ID = vID;
                              END IF;
                             END IF;
                             :P2_SELECTED_ID := vID;
                            END;
                            This just ensures that P2_SELECTED_ID has a value where possible - if no contact for the selected customer is flagged as the primary, this code will do that. I have assumed here that you require each customer to have a primary contact - if not, you could remove the IF tests. I have found that setting the value of a page item that is not directly connected to a database column is best done using a page computation or process.

                            The page now seems to function as I think you need it to - but let me know if not

                            Andy
                            • 11. Re: Radio button column in the tabular report
                              210640
                              Hi Andy,
                              Thank you very much for not only guiding/helping me to get the result that I need, also educating me. This is the first Apex application that I am working on. That is why understanding/using API and implementing processes in proper places/conditions are pretty new to me.
                              SHY
                              • 12. Re: Radio button column in the tabular report
                                ATD
                                You're welcome, SHY,

                                Hopefully, Apex should become easier the more you get to use it - but, if you need anything further, just post another question and I'm sure someone can help you

                                Andy
                                • 13. Re: Radio button column in the tabular report
                                  210640
                                  Hi Andy,
                                  I surely come back with new question!
                                  Now, I am trying to change the status column to check box. I can see the correct status the check box in the form. But when I tried to add new rows or update anything, I am getting "No data found" error.

                                  here is my query
                                  ------------------
                                  SELECT
                                  APEX_ITEM.HIDDEN(1, SEQ_ID) || APEX_ITEM.HIDDEN(2, C001) || APEX_ITEM.RADIOGROUP(3, C001, NULL, NULL, 'onclick="javascript:$x(''P2_SELECTED_ID'').value=' || C001 || '"') PRIMARY_CONTACT,
                                  apex_item.text(4,C002) name,
                                  apex_item.text(5,C004) phone,
                                  APEX_ITEM.HIDDEN(6, C006) || apex_item.checkbox(7,C001, null, C005) INACTIVE
                                  from apex_collections
                                  where collection_name = 'C_CONTACT_INFO'
                                  ----------------------------

                                  this is the updated Contact_Collection
                                  ------------------------------
                                  begin
                                  IF NOT APEX_COLLECTION.COLLECTION_EXISTS('C_CONTACT_INFO') THEN
                                  apex_collection.create_or_truncate_collection(p_collection_name => 'C_CONTACT_INFO');
                                  for y in (select ID,
                                  NAME,
                                  PRIMARY_CONTACT,
                                  PHONE,
                                  decode(STATUS, 'I', ID, null) status,
                                  CUSTOMER_ID
                                  from CONTACTS
                                  where CUSTOMER_ID = :P2_CUSTOMER_ID)
                                  LOOP
                                  apex_collection.add_member(
                                  p_collection_name => 'C_CONTACT_INFO',
                                  p_c001 => y.ID,
                                  p_c002 => y.NAME,
                                  p_c003 => y.PRIMARY_CONTACT,
                                  p_c004 => y.PHONE,
                                  p_c005 => y.STATUS,
                                  p_c006 => y.CUSTOMER_ID);

                                  end loop;
                                  END IF;
                                  end;
                                  ---------------------------------

                                  here is my updated Update_Collection process
                                  ----------------------------------
                                  DECLARE
                                  V_C003 CHAR(1);
                                  V_C001 NUMBER;
                                  V_C005 CHAR(1);
                                  BEGIN
                                  FOR X IN 1..APEX_APPLICATION.G_F01.COUNT
                                  LOOP
                                  V_C001 := APEX_APPLICATION.G_F02(X);

                                  IF V_C001 = :P2_SELECTED_ID THEN
                                  --HTP.P('YES');
                                  V_C003 := 'Y';
                                  ELSE
                                  --HTP.P('NO');
                                  V_C003 := 'N';
                                  END IF;

                                  if APEX_APPLICATION.G_F07(X) = APEX_APPLICATION.G_F02(X) THEN
                                  V_C005 := 'I';
                                  ELSE
                                  V_C005 := 'A';
                                  END IF;

                                  APEX_COLLECTION.UPDATE_MEMBER (
                                  P_COLLECTION_NAME => 'C_CONTACT_INFO',
                                  P_SEQ => APEX_APPLICATION.G_F01(X),
                                  P_C001 => APEX_APPLICATION.G_F02(X),
                                  P_C002 => APEX_APPLICATION.G_F04(X),
                                  P_C003 => V_C003,
                                  P_C004 => APEX_APPLICATION.G_F05(X),
                                  P_C005 => V_C005,
                                  P_C006 => APEX_APPLICATION.G_F06(X));
                                  END LOOP;
                                  END;
                                  ----------------------------------

                                  I did not make any changes to Save_Contacts process.

                                  Thanks
                                  SHY
                                  • 14. Re: Radio button column in the tabular report
                                    ATD
                                    Hi,

                                    OK - you've come across a common issue with browsers. A checkbox is only submitted with the form IF it is ticked, otherwise it is ignored completely. This means that if only one is ticked, your G_F07 array will only contain ONE value. The error that you are seeing is because you have more items in your G_F02 array than in the G_F07 one and the code has run out of G_F07 values. This also means that it is difficult to tell which checkbox has been ticked - it could be the first, second or last one, but the G_F07 array will still only contain one value.

                                    There are ways around this, though they can get complicated. Do you need to use checkboxes or would a Yes/No select list do?

                                    Andy
                                    1 2 Previous Next