1 2 Previous Next 16 Replies Latest reply on Nov 24, 2010 3:15 PM by 817412

    Need help with APEX_Collection

    Jen Hu
      I have a collection:
      BEGIN
      IF apex_collection.collection_exists('CONF') THEN
      apex_collection.delete_collection('CONF');
      END IF;
      apex_collection.create_collection_from_query(
      p_collection_name => 'CONF',
      p_query => '
      select eid, firstname,
      lastname,
      title
      from
      employee',
      p_generate_md5 => 'No'
      );
      END;

      under region, i have:
      select APEX_ITEM.CHECKBOX(1, c001), c002, c003, c004 from apex_collections
      where collection_name ='CONF'

      the checkbox doesn't show, i got <input type="checkbox" name="f01" value="3" /> on my report? How to fix this?

      also, i want to show the value of p_generate_md5 on the report. How to do that?

      Thanks.
      Jen

      Edited by: Jen Hu on Sep 29, 2010 11:09 AM

      Edited by: Jen Hu on Sep 29, 2010 11:23 AM
        • 1. Re: Need help with APEX_Collection
          731633
          Regarding

          the checkbox doesn't show, i got <input type="checkbox" name="f01" value="3" /> on my report? How to fix this?

          -- edit the report. Look at the attributes for the checkbox column. Change it to standard column. It is likely set to display as text / escape special characters.
          • 2. Re: Need help with APEX_Collection
            Jen Hu
            richb267,
            Thanks. It works great.
            I want to show value of p_generate_md5 on each row of the report. And change the value to yes/on based on checkbox checked or not. Do you know how to do that?

            Thanks,
            Jen
            • 3. Re: Need help with APEX_Collection
              731633
              You said

              I want to show value of p_generate_md5 on each row of the report. And change the value to yes/no based on checkbox checked or not. Do you know how to do that?

              p_generate_md5 is a parameter to the apex_collection.create_collection_from_query method, and you have hardcoded it to 'No'. This value is independent of the data in your report, so I'm not sure why you would want to show it in every row of the report. If you could explain more about what the data in your report means and how your APEX application uses it / processes it, that might help.

              Do you want to be able to turn the MD5 checksum generation on and off via a checkbox on the page? If so, you could add a new checkbox item to your page with a (static) list of values query STATIC2:Yes;YES,No;NO. If this checkbox is named P_COMPUTE_MD5, your code changes to

              BEGIN
              IF apex_collection.collection_exists('CONF') THEN
              apex_collection.delete_collection('CONF');
              END IF;
              apex_collection.create_collection_from_query(
              p_collection_name => 'CONF',
              p_query => '
              select eid, firstname,
              lastname,
              title
              from
              employee',
              p_generate_md5 => *:P_COMPUTE_MD5*
              );
              END;

              You'd want to
              (1) have a Refresh button that redisplays the report - the user would click this after checking or unchecking the check box
              (2) or, if you prefer, avoid the Refresh button and make the page submit whenever the checkbox changes state

              Regards, Rich
              • 4. Re: Need help with APEX_Collection
                Jen Hu
                Rich,
                What i want to do is to print a report with just rows that have been checked. My current idea is to create a new column "checked" which will have value yes or no. When the checkbox is checked, "checked" is yes. otherwise, no. Then i will filter checked column to show just selected rows.

                Do you have good suggestion?

                Thanks,
                Jen
                • 5. Re: Need help with APEX_Collection
                  731633
                  Hi Jen -

                  Are you going to write a PL/SQL process that iterates over the report checkboxes, grabs the rows that were checked and prints them?

                  I assume you want the user's checkbox selections to persist as he pages through the report. In other words - if the user selects some rows on page 1, goes to page 2 and then comes back to page 1, whatever he checked on page 1 should still be checked. If so, please see Joel Kallman's post "Preserving checked checkboxes in a report" at http://joelkallman.blogspot.com/2008/03/preserving-checked-checkboxes-in-report.html. I've used this successfully in several reports in my APEX app.

                  Regards, Rich
                  • 6. Re: Need help with APEX_Collection
                    Jen Hu
                    Rich,
                    Thanks very much for your help.
                    For your question, it is yes.
                    I followed Joel's instruction, however, the checkbox selection doesn't persist. Also, do you know how to show just the selected rows in my report.
                    I have a page under apex.oracle.com
                    workspace: houbcm
                    User: APEX
                    Password: apex1
                    Page: 21

                    Regards,
                    Jen
                    • 7. Re: Need help with APEX_Collection
                      jariola
                      Hi,


                      You have forgot create On Demand process to your app
                      >
                      First, I created an unconditional On Demand application-level process named "UpdateCheckboxValue" using the following code:
                      >

                      Go to
                      Home > Application Builder > Application 45308 >Shared Components
                      and click Application Processes. Then press "Create" button follow wizard and insert code from Joel blog post


                      Regards,
                      Jari
                      • 8. Re: Need help with APEX_Collection
                        Jen Hu
                        Jari,
                        Thanks. Very helpful.
                        I still can't figure out how to print out my report with just selected rows. Do you know how to do that?

                        Regards,
                        Jen
                        • 9. Re: Need help with APEX_Collection
                          jariola
                          Hi,


                          You mean filter checked rows ?

                          I can try set sample to apex.oracle.com and post instructions.
                          It might I can not do it today


                          Regards,
                          Jari
                          • 10. Re: Need help with APEX_Collection
                            Jen Hu
                            Jari,
                            Yes.
                            That will be great! Thanks a lot. I am new to apex_collection and learned a lot from you and rich.

                            Regards,
                            Jen
                            • 11. Re: Need help with APEX_Collection
                              jariola
                              Hi,

                              First create application On Demand process (like in Joel Blog).
                              Name process as SAVE_EMP_CHK
                              BEGIN
                                APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
                                  p_collection_name => 'EMP_CHK_COLLECTION',
                                  p_seq             => APEX_APPLICATION.G_X01,
                                  p_attr_number     => 2,
                                  p_attr_value      => APEX_APPLICATION.G_X02
                                );
                                htp.prn('1');
                              EXCEPTION WHEN OTHERS THEN
                                htp.prn('0');
                              END;
                              On Demand process name is important, as we call process by name from javascript

                              Then create new blank page.
                              Create process to new page After header. You can name this how you like
                              IF NOT APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => 'EMP_CHK_COLLECTION') 
                              THEN
                                APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
                                  p_collection_name => 'EMP_CHK_COLLECTION',
                                  p_query => q'!
                                    SELECT empno,
                                      'false' AS chk
                                    FROM emp
                                  !'
                                );
                              END IF;
                              Insert to page HTML header
                              <style>#apexir_CHK{display:none}</style>
                              <script>
                              function saveEmpChk(p,s){
                               $.ajax({
                                type:'POST',
                                url:'apex_application.show',
                                dataType:'text',
                                data:{
                                 p_flow_id:&APP_ID.,
                                 p_flow_step_id:&APP_PAGE_ID.,
                                 p_instance:&APP_SESSION.,
                                 p_request:'APPLICATION_PROCESS=SAVE_EMP_CHK',
                                 x01:s,
                                 x02:p.checked
                                },success:function(){gReport.pull()}
                               })
                              }
                              </script>
                              Then create interactive report to page from query
                              SELECT apex_item.checkbox(1,c.c002,'onclick="saveEmpChk(this,'||c.seq_id||')"','true') AS chk,
                                c002 AS checked,
                                empno,
                                ename,
                                job,
                                mgr,
                                hiredate,
                                sal,
                                comm,
                                deptno
                              FROM emp e, apex_collections c
                              WHERE collection_name = 'EMP_CHK_COLLECTION'
                                AND e.empno = c.c001
                              Go edit report attributes and check/change CHK column to "Standard Report Column"

                              Run page and you should get result like
                              http://apex.oracle.com/pls/otn/f?p=40323:53

                              Regards,
                              Jari
                              • 12. Re: Need help with APEX_Collection
                                Jen Hu
                                Jari,
                                It works great! Thanks a lot.
                                Do you think if it is possible to show just the employee info (no checkbox and checked value) when i download the report?

                                Regards,
                                Jen
                                • 13. Re: Need help with APEX_Collection
                                  jariola
                                  Hi,

                                  Set condition "PL/SQL Expression" to columns you do not like export
                                  NVL(:REQUEST,'EMPTY') NOT IN('CSV','XLS','PDF','XML','RTF','HTMLD')
                                  Regards,
                                  Jari
                                  • 14. Re: Need help with APEX_Collection
                                    Jen Hu
                                    Jari,
                                    Thanks a million. It works so perfectly. You are genius.

                                    Regards,
                                    Jen
                                    1 2 Previous Next