This discussion is archived
13 Replies Latest reply: Apr 4, 2013 12:20 PM by fac586 RSS

How to change column type at runtime in Apex 4.0

979835 Newbie
Currently Being Moderated
Hello everybody,

I´m newbie as Apex developer using Apex 4.0 with OracleXE 11.2, and now my problem is:

I have a tabular form where one of the columns, when calling the form, is a Display Item, so that you cannot change existing values​​.

I want, when adding a new row, that column become a Select List (named LOV) based on a query that exclude existing values ​​in other rows already inserted.

Can I change that Display Item to Select List (named LOV) programmatically at runtime?
If not, what other options do I have?

Thanks a lot for any help,
Greetings
  • 1. Re: How to change column type at runtime in Apex 4.0
    fac586 Guru
    Currently Being Moderated
    976832 wrote:
    Hello everybody,
    Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already), and update your forum profile with a real handle instead of "976832".

    When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

    <li>Full APEX version
    <li>Full DB/version/edition/host OS
    <li>Web server architecture (EPG, OHS or APEX listener/host OS)
    <li>Browser(s) and version(s) used
    <li>Theme
    <li>Template(s)
    <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

    With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
    I´m newbie as Apex developer using Apex 4.0 with OracleXE 11.2, and now my problem is:

    I have a tabular form where one of the columns, when calling the form, is a Display Item, so that you cannot change existing values​​.
    As a self-confessed inexperienced APEX developer, why are you using complex and ugly tabular forms? Take a moment to think about this: have you ever seen this kind of UI pattern on any decent website: +{thread:id=850889}+ ?
    I want, when adding a new row, that column become a Select List (named LOV) based on a query that exclude existing values ​​in other rows already inserted.

    Can I change that Display Item to Select List (named LOV) programmatically at runtime?
    If not, what other options do I have?
    I suggest you build this using basic APEX components (form + report from the Form wizard), then customize the form using read-only conditions to switch between the select list and read-only value.
  • 2. Re: How to change column type at runtime in Apex 4.0
    979835 Newbie
    Currently Being Moderated
    Hi fac586,

    Thanks for your quick response.

    1. I have already update to a real handle instead of "976832", but apparently it´s not yet visible;

    2. Thank you for your reference to oracle.apex.com in order to reproduce and share problems and I'll consider using it;

    2. I have a large experience in Oracle Forms, and I think that this kind of UI pattern is very appreciated by end users for its simplicity and usability;

    3. I´ve read the thread you refer, and I understand APEX limitations when using tabular forms, but I agree with Denes when he says that "...also know that most of my costumers wouln't consider using apex if there would be no feature like tabular forms..."

    So, i´m tyring to learn APEX and overcome difficulties, in order to present this type of UI to my costumer, and that's why I ask for help.

    Of course I need to consider the cost-benefit ratio, and perhaps I have to change my approach in this particular issue.

    Best regards
  • 3. Re: How to change column type at runtime in Apex 4.0
    fac586 Guru
    Currently Being Moderated
    976832 wrote:
    Hi fac586,

    Thanks for your quick response.

    1. I have already update to a real handle instead of "976832", but apparently it´s not yet visible;

    2. Thank you for your reference to oracle.apex.com in order to reproduce and share problems and I'll consider using it;

    2. I have a large experience in Oracle Forms, and I think that this kind of UI pattern is very appreciated by end users for its simplicity and usability;
    Using web technologies it's neither simple nor usable.

    It's been a while since I used Forms, but I'm not sure that what you're describing is possible in a Forms multi-row block either? Aren't item types and LOVs fixed?
    3. I´ve read the thread you refer, and I understand APEX limitations when using tabular forms, but I agree with Denes when he says that "...also know that most of my costumers wouln't consider using apex if there would be no feature like tabular forms..."

    So, i´m tyring to learn APEX and overcome difficulties, in order to present this type of UI to my costumer, and that's why I ask for help.

    Of course I need to consider the cost-benefit ratio, and perhaps I have to change my approach in this particular issue.
    If you're coming from a Forms background I suspect you probably have little JavaScript/jQuery experience? Customizing the built-in APEX tabular forms functionality to implement your requirement requires significant competence in JS. In all likelihood the desire of some people for points in this forum will result in someone providing you with a solution, but would you be able to understand and maintain it?

    You should be able to create a working and robust implementation using standard APEX report and form components, items, conditions and your existing PL/SQL knowledge in minutes or hours. Unless you can write jQuery effortlessly it will take you days to customize the built-in tabular form.
  • 4. Re: How to change column type at runtime in Apex 4.0
    979835 Newbie
    Currently Being Moderated
    With Oracle Forms, LOVs are not so restricted and have other options, eg many triggers to solve the problem that led to what I described.

    Anyway, thanks you for your informations and advices. I will consider them and make decisions.
  • 5. Re: How to change column type at runtime in Apex 4.0
    InoL Guru
    Currently Being Moderated
    Out of curiosity:
    I want, when adding a new row, that column become a Select List
    Did you really manage that in Forms? Dynamically change the item type in a tabular form from Text Item to List Item?
  • 6. Re: How to change column type at runtime in Apex 4.0
    979835 Newbie
    Currently Being Moderated
    InoL, that is not the question...as I said:

    "With Oracle Forms, LOVs are not so restricted and have other options, eg many triggers to solve the problem that led to what I described."

    The issue that leads to my present problem in APEX, doesn't exist in Forms, so there it´s unnecessary put the problem of changing the item type.

    In APEX, if I define a LOV query that exclude values already existent in DB (composite PK), when you call the tabular form, the Select List column shows the ID and not the display value, what is expectable.
    As you probably know, in Forms, management of RGs vs LOVs vs Items is much more flexible.
  • 7. Re: How to change column type at runtime in Apex 4.0
    InoL Guru
    Currently Being Moderated
    Ah, now I think I know what you mean. It has nothing to do with "when adding a new row, that column +become+ a Select List". There is a feature request for Apex "Shared LOV Definition - Seperate where clause to control valid / invalid values or ability to identify current field) that I voted for too (knowing that it is possible in Forms).

    There is a work around for an LOV like this. It's something like:
    {code}
    select thing_name d, thing_id r
    from table_of_things
    where thing_type = 'FILTER'
    and
    (
    active_flag = 'Y'
    or
    thing_id = :P99_THIS_FIELD
    )
    {code}
    What happens here is that for new values you can only select values with active_flag='Y', but for values already entered this is ignored.

    More explanation can be found on the feature request application:
    https://apex.oracle.com/pls/apex/f?p=55447:1
    Vote for this feature!

    Edited by: InoL on Apr 3, 2013 3:15 PM
  • 8. Re: How to change column type at runtime in Apex 4.0
    979835 Newbie
    Currently Being Moderated
    Thanks InoL,

    Yes, you're right, when I put the question that way, that has been a result of thinking about ways to solve the problem...change the column type (when in insert mode) would be a work around...

    I think the solution you suggest is a good option.
    I'll try to use it.

    Thank you very much
  • 9. Re: How to change column type at runtime in Apex 4.0
    InoL Guru
    Currently Being Moderated
    It's a bit trickier in a tabular form, because you don't have item P99_THIS_FIELD. I haven't given it much thought about how to do it in a tabular form B-)
  • 10. Re: How to change column type at runtime in Apex 4.0
    979835 Newbie
    Currently Being Moderated
    Yes, I know that, but I suppose you gave me an idea to solve problem...probably tomorrow I'll try the implementation
  • 11. Re: How to change column type at runtime in Apex 4.0
    fac586 Guru
    Currently Being Moderated
    OK, so this thread isn't actually about what it says in the subject/what I thought it was. Chalk up yet another XY Problem...
    InoL wrote:
    It's a bit trickier in a tabular form, because you don't have item P99_THIS_FIELD. I haven't given it much thought about how to do it in a tabular form B-)
    That I do know: use the <tt>apex_util.savekey_{noformat}*{noformat}</tt>/<tt>apex_util.keyval_{noformat}*{noformat}</tt> APIs: {message:id=9719033}
  • 12. Re: How to change column type at runtime in Apex 4.0
    InoL Guru
    Currently Being Moderated
    Using apex_util doesn't seem to work. The problem is that the select list values are not populated at the same time as the row is populated. It's done after the rows are retrieved (use the debug option to see this).
    So, APEX_UTIL.KEYVAL_VC2 will just return the same (last) value for every execution of the LOV query.

    Going through the links I hit this remark: "Will not work on a tabular form. only a page of type report".
  • 13. Re: How to change column type at runtime in Apex 4.0
    fac586 Guru
    Currently Being Moderated
    InoL wrote:
    Using apex_util doesn't seem to work. The problem is that the select list values are not populated at the same time as the row is populated. It's done after the rows are retrieved (use the debug option to see this).
    Yes that makes sense considering how "Display as..." report column options are implemented.
    So, APEX_UTIL.KEYVAL_VC2 will just return the same (last) value for every execution of the LOV query.

    Going through the links I hit this remark: "Will not work on a tabular form. only a page of type report".
    It works with manual tabular forms where the select lists are generated using the <tt>apex_item.select_list_from_lov</tt> API in the report query. That's the situation in which I've used it before. It's odd that the +{thread:id=2250770}+ thread is so enthusiastic about it when it doesn't appear to do so...

Legend

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