This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Mar 14, 2012 7:39 AM by Joe R RSS

Multi-Select List with Submit

Joe R Explorer
Currently Being Moderated
version 4.0.2.00.07

Hello,

I have two regions on a form that I need to display based on a selection from a Select List. The Select List needs to allow multiple selections.
I have conditions set on the regions as PL/SQL Expression to evaluate the contents of the Select List and display if there is a match.

Under the Settings for the Select List -> Page Action when Value Changed the only option for allowing multiple selections is when that is set to None (Default).
The other options apparently don't allow for multiple selections, but will set the selection into session. I need both.

How do get the values from the Select List into session so that the regions display?

Can someone help me?

Thanks,
Joe
  • 1. Re: Multi-Select List with Submit
    fac586 Guru
    Currently Being Moderated
    Joe R wrote:
    version 4.0.2.00.07

    Hello,

    I have two regions on a form that I need to display based on a selection from a Select List. The Select List needs to allow multiple selections.
    I have conditions set on the regions as PL/SQL Expression to evaluate the contents of the Select List and display if there is a match.

    Under the Settings for the Select List -> Page Action when Value Changed the only option for allowing multiple selections is when that is set to None (Default).
    The other options apparently don't allow for multiple selections, but will set the selection into session. I need both.
    How could that work? Think about it. If a select list is set to submit on change then as soon as you click on an option the value has been changed and the page is submitted, so you can't click on another option to make multiple selections. Conversely, if it allows multiple selections, how does the browser know you're finished making them and it's time to submit? The two concepts are mutually exclusive.

    Multi-select lists are not that common in the web environment and tend to confuse users anyway.

    Using checkboxes for the options and a button to submit and re-display the page would be clearer and simpler to implement.
  • 2. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Hi Fac,

    I was hoping to talk to you! :)

    I was thinking about that and I had envisioned a selection made, the page is "submitted", the region displayed. Another selection is made, the page is "submitted", the region displayed keeping the previous selections since they are already set. Something like that.

    How would your idea about a button to set session using Checkboxes instead of a Multi-Select List work?

    Thanks,
    Joe

    Edited by: Joe R on Mar 6, 2012 9:43 AM
  • 3. Re: Multi-Select List with Submit
    fac586 Guru
    Currently Being Moderated
    Joe R wrote:
    Hi Fac,

    I was hoping to talk to you! :)

    I was thinking about that and I had envisioned a selection made, the page is "submitted", the region displayed. Another selection is made, the page is "submitted", the region displayed keeping the previous selections since they are already set. Something like that.
    I see "something like that" working using checkboxes and a submit button.
  • 4. Re: Multi-Select List with Submit
    fac586 Guru
    Currently Being Moderated
    Joe R wrote:

    How would your idea about a button to set session using Checkboxes instead of a Multi-Select List work?
    You present the options used to conditionally display the regions as a checkbox item, with a "Refresh" button (if there's no other suitable submit button already on the page) the user can click after selecting the options. You use the checkhox item value in your region conditions.

    Can't be any more specific without more detailed information about the requirements or an example on apex.oracle.com.
  • 5. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Fac,

    I've requested a workspace on apex.oracle.com.

    I have multiple issues now.

    I have four selections as Checkboxes that return an ID. The possible values are 41, 181, 281, and 341.

    I have two regions that are conditionally displayed depending on the selections made.
    Region 1 displays if 41 and/or 181 are selected.
    Region 2 displays if 281 and/or 341 are selected.
    In the Conditions set as PL/SQL Expression for Region 1: :P49_PRODUCT_GROUP_ID IN (41,181) I have also tried :P49_PRODUCT_GROUP_ID = 41 OR :P49_PRODUCT_GROUP_ID = 181
    In the Conditions set as PL/SQL Expression for Region 2: :P49_PRODUCT_GROUP_ID IN (281,341) I have also tried :P49_PRODUCT_GROUP_ID = 281 OR :P49_PRODUCT_GROUP_ID = 341

    I put a Button Among Region Items with a Request of 'Go'.
    I put a Branch to my Page, 49, set to the button name of :P49_REFRESH

    When I click the 'Go' button no region is displayed and the checkbox selected is no longer selected, but the return value is in Session.
    When I select more than one checkbox I'm getting an error:
    ORA-20001: Invalid PL/SQL expression condition: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    
    ERR-1024 Unable perform processing.
    Return to application.
    Any ideas?

    Thanks,
    Joe
  • 6. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Hello,

    I never got an email back regarding access to apex.oracle.com so can someone help me?

    Thanks,
    Joe
  • 7. Re: Multi-Select List with Submit
    fac586 Guru
    Currently Being Moderated
    Joe R wrote:
    Fac,

    I've requested a workspace on apex.oracle.com.

    I have multiple issues now.

    I have four selections as Checkboxes that return an ID. The possible values are 41, 181, 281, and 341.

    I have two regions that are conditionally displayed depending on the selections made.
    Region 1 displays if 41 and/or 181 are selected.
    Region 2 displays if 281 and/or 341 are selected.
    In the Conditions set as PL/SQL Expression for Region 1: :P49_PRODUCT_GROUP_ID IN (41,181) I have also tried :P49_PRODUCT_GROUP_ID = 41 OR :P49_PRODUCT_GROUP_ID = 181
    In the Conditions set as PL/SQL Expression for Region 2: :P49_PRODUCT_GROUP_ID IN (281,341) I have also tried :P49_PRODUCT_GROUP_ID = 281 OR :P49_PRODUCT_GROUP_ID = 341

    I put a Button Among Region Items with a Request of 'Go'.
    I put a Branch to my Page, 49, set to the button name of :P49_REFRESH

    When I click the 'Go' button no region is displayed and the checkbox selected is no longer selected, but the return value is in Session.
    When I select more than one checkbox I'm getting an error:
    ORA-20001: Invalid PL/SQL expression condition: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    
    ERR-1024 Unable perform processing.
    Return to application.
    See the documentation for how checkbox item values work:
    >
    Displayed using a list of values. A list of values is required for items displayed as check boxes. The value corresponding to a checked box is returned in a single colon-delimited string.
    >
    Check out these values in your app using the Session viewer.

    Your region conditions have to evaluate whether the checkbox item delimited strings contain matching values.
  • 8. Re: Multi-Select List with Submit
    Tom Petrus Expert
    Currently Being Moderated
    How did you create the checkbox? Is is one checkbox with multiple values or do you have 4 seperate checkboxes?
    If you have 1 checkbox with multiple values your session state of the item will be a colon-seperated value of the selected values (eg, '2:3:5'), although this would give an ora-01722 error.
    On your branch, there are no conditions, but you specified the 'when button pressed' i assume?
    When you only select one value and press your button, what is saved in the session state (click 'session' on the developer bar)?

    I've not had trouble requesting a workspace before, might be you need to wait a while, and you could try again?
  • 9. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Fac - Thanks for the reply.

    Tom -
    The checkboxes are created from a lookup table and return 4 separate values. When I select one checkbox, I'm selecting the box that returns a value of 181, I see the value 181 in the session viewer.
    The branch is set to fire when the button is clicked.

    The condition on the Region that should display is currently set to PL/SQL Expression: :P49_PRODUCT_GROUP_ID IN (41,181)

    When I select more than one checkbox and get the error I can see in the session viewer the colon delimited list like 41:181

    What else can I tell you guys?

    Thanks,
    Joe
  • 10. Re: Multi-Select List with Submit
    Tom Petrus Expert
    Currently Being Moderated
    Try setting the condition of your region to "Exists (SQL query returns at least one row)", and use this expression

    SELECT 1
    FROM (SELECT REGEXP_SUBSTR (str, '[^:]+', 1, LEVEL) item
    FROM (SELECT :P49_PRODUCT_GROUP_ID str
    FROM DUAL)
    CONNECT BY LEVEL <= length (regexp_replace (str, '[^:]+')) + 1
    )
    WHERE item IN ('81', '141')

    This splits out the colon delimited list into multiple rows, allowing the IN.

    Or you could just use an INSTR function to check if the number is in the delimited list.
    Point is, you can't use a string with the IN clause like that, it would translate as '81:141' IN (81, 141)
  • 11. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Tom,

    I tried your query and the INSTR function and still the region is not displayed.

    I also tried to submit a request for access on apex.oracle.com and I got to the last page to enter the security stuff and the page errored:

    ORA-00001: unique constraint (APEX_040100.WWV_FLOW_PROV_COMPANY_NAME) violated
    Error requesting Workspace: . Return to application.

    Language: en
    Application Express 4.1.1.00.23

    I don't know what to do now?

    Thanks,
    Joe
  • 12. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Hello Fac/Tom,

    I've finally got a workspace created on apex.oracle.com.

    The workspace is: jar
    The user_name is: dev_guest
    The password is: dev

    The application is: 59896 - JOE

    I've set up 3 Regions: Main, Region 1, and Region 2
    There are 4 checkboxes on the Main Region. ASTRO Subscribers, ASTRO 25 Infrastructure, TETRA Subscribers, and TETRA Infrastructure.

    If ASTRO Subscribers and/or ASTRO 25 Infrastructure is selected, press the Go button, Region 1 should display.
    If TETRA Subscribers and/or TETRA Infrastructure is selected, press the Go button, Region 2 should display.

    If more than one selection is made at the same time there is an error returned.

    Please let me know if you need more information.

    Hopefully you'll be able to help me get this working!

    Thanks,
    Joe
  • 13. Re: Multi-Select List with Submit
    fac586 Guru
    Currently Being Moderated
    Joe R wrote:

    I've set up 3 Regions: Main, Region 1, and Region 2
    There are 4 checkboxes on the Main Region. ASTRO Subscribers, ASTRO 25 Infrastructure, TETRA Subscribers, and TETRA Infrastructure.

    If ASTRO Subscribers and/or ASTRO 25 Infrastructure is selected, press the Go button, Region 1 should display.
    If TETRA Subscribers and/or TETRA Infrastructure is selected, press the Go button, Region 2 should display.

    If more than one selection is made at the same time there is an error returned.
    As described above and in the documentation, the value of multiple checkbox selections is stored in the item as a colon-delimited string. When 2 checkboxes are selected the value of <tt>P1_PRODUCT_GROUP</tt> is like
    41:181
    The ORA-06502: PL/SQL: numeric or value error: character to number conversion error occurs because your region conditions of the form
    :P1_PRODUCT_GROUP_ID IN (41,181)
    require PL/SQL to perform an implicit character-to-number conversion of the value of <tt>P1_PRODUCT_GROUP</tt>. This fails as '41:181' cannot be converted into a number.

    The region conditions need to be expressions that can discriminate between, and match on, such multiple colon-delimited values. I've changed them to use a common pattern:
    regexp_like(':' || :P1_PRODUCT_GROUP_ID || ':', ':41:|:181:')
    This regular expression-based approach works by wrapping the checkbox item value in additional colons to ensure that the first and last values are delimited, then seeking a match on any of the permitted options using regexp alternation.
  • 14. Re: Multi-Select List with Submit
    Joe R Explorer
    Currently Being Moderated
    Fac,

    Is this statement the same for any version? I'm on 4.0.2.00.07. I set the condition in the Region to PL/SQL Expression and the Region does not display!

    There are some bugs in this version like not being able to delete certain items. I get a 404 Page Not Found when deleting certain items. Maybe there's something weird going on with this.

    Thank you very much for your help! It is greatly appreciated!

    Thanks,
    Joe
1 2 Previous Next

Legend

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