13 Replies Latest reply: Dec 9, 2009 10:38 AM by 708209 RSS

    Tabular form: how to dynamically enable/disable columns and rows


      I am very new to ApEx so sorry if this has been dealt with before, but I cannot find a solution in the forums nor with Google, so here I am.

      I have a tabular form based on a view, with two items as primary key and an instead-of trigger for update (only updates done there, not inserts/deletes), and the form works fine (I can update and requery, etc.) The form was created with the wizard, not manually.

      My problem is, I need to disable (disable - not hide) some of the report's columns to prevent edits depending on the value of one of its own columns - for instance, a value of +"q"+ in the period column will enable/disable some columns, while a value of +"h"+ will enable/disable other columns. (The "q"/"h" value is the same for all rows in the form.)

      In a similar fashion, I also need to disable/enable entire rows depending on another column's value.

      To summarize, when displaying the tabular form, and depending on one column's value, I need to enable/disable some columns. Then, for each row I navigate to, and according to another column's value, I need to enable/disable one or more items on that row.

      Any detailed help on how to do this would be greatly appreciated (and if you need more info please ask.)

      This was very easily done in Forms with the when-new-record triggers, and settings items as enabled/disabled. I am really confused so as to how to do this with ApEx - not to mention I'm helpless with JavaScript... Since the page works well with the wizard-generated form, I'd like to keep it that way, but I'm willing to learn new tricks if the solution is building it manually (as explained here: http://www.oracle.com/technology/products/database/application_express/howtos/tabular_form.html) or do it with a dynamic query (as explained here: http://www.oracle.com/technology/products/database/application_express/howtos/dynamic_report.html) even though I'm not sure I understand the things explained there (and whether it needs it own MRU process...?)

      Thanks in advance,

        • 1. Re: Tabular form: how to dynamically enable/disable columns and rows
          Hi Ludovic,

          Your question is fairly simple.

          Place the following in the HTML Form Element Attributes (click on Period Column and enter edit mode):
          Also, make sure to look in the forum before asking because there are tons of questions similar to yours.

          • 2. Re: Tabular form: how to dynamically enable/disable columns and rows
            Hi Mauricio,

            Thanks for the answer - I had found this in a book, but it was said to apply to single-row forms, and to work only when a user would change a lov. I had no idea it could work with a multi-row form, and "on load", which surprises me (the 'on change' event fires? and for all rows?) - but I will try it asap (it's 9pm here I'm not in the office anymore :))

            As for searching, I had searched the forums, as mentioned. I looked at 230 thread titles and the contents of a quite few but to no avail. I most certainly do not want to waste other people's time, and sorry if I did. Mind you, the question about the rows remain.

            Thanks for your answer and I'll get back after I managed to try it.
            • 3. Re: Tabular form: how to dynamically enable/disable columns and rows
              No problem, I was just giving friendly advice. ;) You're not wasting anyone's time, this is what the forum is for.

              As for your question, I don't understand what you mean by multi-row form.

              Are you trying to do: if q then this gets disabled and if h then these other rows get disabled?

              if so:
              You can add functions after the ";" basically the equation is contained within the set of quotes.

              Final note: be sure to mark a response as helpful or correct, this ensures other users can find correct answers later.
              • 4. Re: Tabular form: how to dynamically enable/disable columns and rows
                By "multi-row form", I mean, tabular form.

                The onchange() bit ou provide I saw in John Scott's "Pro Oracle Application Express", but it mentioned it as a result of changing a LOV and disabling other items in a single-row form depending on the changed LOV value. I will try our method when I'm back in the office tomorrow, and, of course, I will mark our answer as helpful if it turns out to be so, no worries there!

                To repeat, I need to do, in a tabular form:

                1) when the report is fetched and displayed, enable/disable some columns based on another column value (identical for all rows), and then
                2) when users navigate from row to row in the tabular form, enable/disable a column based on another column than in 1), but this time the value can vary from row to row.

                • 5. Re: Tabular form: how to dynamically enable/disable columns and rows
                  You sound frustrated and I can understand - Forms is nice for this kind of thing and you are at square 1. First off I don't even use the built-in tabular forms - they are ok for simple things, but usually mine are built off multiple tables and I need to write my own processes. So my example are built manually using the apex_item api's.


                  1) Here is an example of disabling onload - you just need to do some conditional sql (case/decode) in the attributes part of the api. I'm using a with clause b/c I dont actually have these tables, but you get the idea.
                  WITH x AS (SELECT 'SALES' job, 500 commission FROM dual UNION ALL
                            SELECT 'ACCOUNTING' job, 0 commission FROM dual)
                  SELECT apex_item.text(1, commission, 4, 5, CASE WHEN job != 'SALES' THEN '"disabled=disabled"' ELSE NULL END, 'f01_#ROWNUM#') commission
                    FROM x
                  2) Here is an example of dynamic disabling - you'll need to write some javascript for it, but here you go:
                  WITH x AS (SELECT 'SALES' job, 500 commission FROM dual UNION ALL
                            SELECT 'ACCOUNTING' job, 0 commission FROM dual)
                  SELECT apex_item.select_list_from_query(1, -1, 'select job r, job d from (select ''SALES'' job from dual union all 
                                                                       select ''ACCOUNTING'' from dual)', 
                                                              'onchange="chgJob(this, #ROWNUM#);"',
                                                              'YES', -1, '-Select Job-', 'f01_#ROWNUM#') job,
                        apex_item.text(2, NULL, 4, 4, NULL, 'f02_#ROWNUM#') comm
                    FROM x
                  Put this JS in the header:
                  <script type="text/javascript">
                  function chgJob(pThis,pRow){
                    if(pThis.value != 'SALES'){
                      $x_disableItem('f02_'+pRow, true);
                      $x_disableItem('f02_'+pRow, false);
                  Basically this attaches an onchange event to each select list (similar to what Forms can do), and then you call a function and pass the row - #ROWNUM# gets substituted by Apex at run time. Since you defined your id of the commission field to be f02_#ROWNUM#, its an easy substitution.

                  That should give you an idea and you will need to combine some things - there are far more elegant ways to probably do this, but start with getting something working and then work on elegance later.
                  • 6. Re: Tabular form: how to dynamically enable/disable columns and rows
                    Hi Scott,

                    First off - thanks! The first solution you provided was what I was looking for, and I was able to adapt it to my needs. Even better, with a tweak, I was able to make it work for BOTH disabling rows (your solution) and columns. I say, hearty thanks!

                    My end result is thus (in case someone looks for it):

                    apex_item.text(p_idx => 40,
                    p_value => cq4,
                    p_attributes => decode(surveyed,0,'disabled',decode(locked,1,'disabled',NULL))) cq4,
                    from ...

                    where "surveyed" is the row attribute (varies for each row) and "locked" is the column attribute (same for all rows in a report).

                    My only problem is (of course there must have been one :)) the MRU process (which worked before the change) doesn't work anymore. It doesn't fail (no error message) but it doesn't succeed (no Process Success Message either, plus I have added a bit of code to the instead-of-trigger to log calls to it.)

                    I have recreated the page and the tabular form and its automated MRU process, and found that the problem was (at least I think so:)) the fact that the apex_item.text() bit above changes the column for a +"text field"+ to a +"standard report column"+ (at least it must be changed to, otherwise the apex_item.text() appears in the text filed itself...), which makes sense for it to then not be sent for updates, because I guess ApEx only tries to update, well, updateable columns and "standard report columns" aren't.

                    So, my next question is: does this mean I have to write my own MRU process? Is something like this (found here: [http://www.oracle.com/technology/products/database/application_express/howtos/tabular_form.html#DISPLAY]) the answer?

                    for i in 1..htmldb_application.g_f01.count
                    update emp
                    set job = replace(htmldb_application.g_f03(i),'%'||'null%',NULL),
                    mgr = replace(htmldb_application.g_f04(i),'%'||'null%',null),
                    hiredate = htmldb_application.g_f06(i),
                    sal = htmldb_application.g_f07(i),
                    comm = htmldb_application.g_f08(i),
                    deptno = replace(htmldb_application.g_f09(i),'%'||'null%',NULL)
                    where empno = htmldb_application.g_f01(i);
                    end loop;

                    My worry there is, even if I write this, will it actually work? I'm asking because I wonder whether htmldb_application.g_fxx have any actual session values when they're +"standard report columns"+ instead of +"text items"+.

                    (Or should I ask this in a new question because it's not really related to the original one and people who would want to find it couldn't? I will mark this as "answered" and created a new topic if you tell me it's better this way.)

                    (Last but not least - I am far more lost than frustrated :) Forms has many, many nice touches, but so does ApEx. It's just that the two sets of nice touches don't overlap and at my age I find it harder to learn new tricks :))
                    • 7. Re: Tabular form: how to dynamically enable/disable columns and rows
                      Glad to hear you're on your way Ludovic - What I've found is that the built-in tabular forms, although nice, are pretty basic. Meaning currently there is no easy way to do validations and basically you are expecting everything to go into one nice neat table. The ones I've typically built pull data from multiple tables, or require more than a simple insert/update, so I've written my own using apex_item.

                      That being said, I also typically write my own processes to handle the data - sort of like if you base a Forms block on a view, you either need to write an instead of trigger or handle your own insert/update/locks...etc. The downside is that you have to write your own code, the upside is that you have complete control and...well I like writing code :)

                      Now I've never used this, but apex_item does have a process called multi_row_update that would allow you to use the apex_item api's, but pass off processing to Apex. I've never actually used it myself, but I'm sure you'll find some examples in the forums on how to use it. That would sort of be the best of both worlds. Unfortunately I think combining the "regular" MRU process with apex_item may not work as intended as you are finding out.

                      Otherwise you would handle the processing by looping like
                      for i in 1..apex_application.g_f01.count loop
                        --do something
                        insert into table values(apex_application.g_f01(i));
                      end loop;
                      It is tough because Forms will nicely handle the looping for you and just drop you into the insert/update/delete triggers where you can write whatever code you want and don't have to worry about dealing with all the data yourself. The built-in tabular forms are a good start, but they are far from perfect. However given your ability to write pl/sql and some time, I guarantee you CAN do what you need using one of the methods :) If you need any more help feel free to post back.
                      • 8. Re: Tabular form: how to dynamically enable/disable columns and rows
                        Actually I forgot to mention one thing for what it is worth to be careful about. Fields that are marked as disabled in HTML (all web platforms, not just Apex) don't actually get posted. So I found out the hard way that if you disable a field in the middle of a row, that the arrays get confused and you may get data on the wrong row.

                        Instead I've found the following to be more helpful, and will serve the same purpose. Basically it renders the field as read-only, but if you click on it it immediately loses focus. It adds a little styling to make it appear as if it is disabled. Try it out and see what you think - you would put this entire string in your decode/case statement. You can give an HTML color to the background instead of just silver if you want, but thats up to you.
                        onfocus="this.blur();" readonly=readonly style="background-color:silver;"
                        • 9. Re: Tabular form: how to dynamically enable/disable columns and rows

                          I wasn't quite sure what your last post would do, because I liked the disabled better (it doesn't throw off tabbed field navigation, for instance, unlike your solution) - but, but, but: it magically made my self-written +"for i in 1..apex_application.g_f01.count loop --do something"+ loop work, which it wasn't doing just before and I was going to ask you about so you've been spared that one :) (I kept getting a "01403 no data found" error, and I guess it was indeed due to the arrays being messed up.)

                          This gets me to thinking (I do that occasionally): how about having the standard tabular form, THEN add two input fields that I disable/enable as per your first idea, and I fill these, and then when I leave any input field an onblur() event copies the field's value to the actual databse field in the tabular form? Is that a stupid idea as there is now way for javascript to update a field on the same row? (I wouldn't even be able to write the javascript if it was possible anyway, but I'm asking.)

                          (Speaking of javascript - is there anyway to replace the this.blur with something that jumps to the next item?)

                          (The only other idea I have is to set a process on submit that copies the display/input columns to the database ones, and the submit the page, but I'll bet the disabling will throw off the arrays anyway and this couldn't possibly work.)

                          Any more ideas? (Sorry for asking again and again.) (Luck would have you'd be near so I could buy you a drink.) (I don't believe in luck :))

                          Thanks in advance - thanks to you I now have a working solution (whose only drawbacks are the tabbed navigation and the fact that it updates all database rows and not only the updated ones, as the auto-MRU does - but a working solution that does these is better than a non-working solution that doesn't :))
                          • 10. Re: Tabular form: how to dynamically enable/disable columns and rows
                            Honestly the onblur piece isn't totally necessary, its more just to simulate something being disabled and not being allowed to click in it. You can try taking that out and see how if you like it better and then I think maybe the navigation will work better.

                            I didn't get time to read the other part - gotta run to a meeting.
                            • 11. Re: Tabular form: how to dynamically enable/disable columns and rows
                              I tried it... and I think I'll leave the decision to the users :) It does make navigation predictable, but it does make it longer. Given the small percentage of readonly cells I'm likely to get in real-world deployment, I think I'll suggest to leave the onblur() in.

                              (I clicked on the wrong bit ("helpful" instead of "answered" for your last post, so please write something more so that I can say you answered correctly :))

                              And, truly, many, many sincere thanks for your very patient and knowledgeable help. I now have a slight chance of making the Friday demo :) - and that's thanks to you. So, thanks.
                              • 12. Re: Tabular form: how to dynamically enable/disable columns and rows
                                You're very welcome. I got a lot of help in the beginning from this forum so I try my best to give back. :)

                                Good luck on the demo!
                                • 13. Re: Tabular form: how to dynamically enable/disable columns and rows
                                  I don't think I'll ever get to the level where I can give back, I'm afraid... ah well.

                                  Again, thanks for your invaluable help (and demo wishes :))