1 2 Previous Next 19 Replies Latest reply on Sep 11, 2013 3:56 PM by 1036140 Go to original post
      • 15. Re: Validating Tabular Form Column Against Value From Another Table
        Ramesh P, Oracle APEX Developer


        Let me explain how it works,


        Create a page item P1_MSG;


        Lets say the user will key in values for F02 report text box


        Create a page process

        Name: SET_MESSAGE

        Process point: Before computations and validations



            v_val VARCHAR2(100);

            v_msg VARCHAR2(32000) := NULL;


            --apex_application.g_f02 is your input parameter name in tabular form, check gfrom browser

            FOR i IN 1..apex_application.g_f02.COUNT

                SELECT B2 INTO v_val FROM TABLE2 WHERE A2 = apex_application.g_f01(i);

                IF v_val > apex_application.g_f02(i) THEN

                    v_msg := v_msg||'The value you entered cannot exceed amount<br/>';

                END IF;   

            END LOOP;   

           IF v_msg IS NOT NULL THEN

                :P1_MSG := v_msg;

           END IF;




        Now create a validation on page level.


        Select pl/sql expression returning error message


        v_msg VARCHAR2(32000) :=   :P1_MSG;



            RETURN v_msg;


           RETURN NULL;

        END IF;


        • 16. Re: Validating Tabular Form Column Against Value From Another Table
          Tom Petrus

          Honestly, I'm not sure why you would need to go through the hassle of creating a page item just to be a temporary container between 2 processes, when you're just returning it as a message in a validation. Why wouldn't you simply write your required validation code in the validation?

          It also doesn't really help with what I seem to understand is the problem at hand. When OP reacted to my last post he mentioned he got the validation working without the item hoop?

          @OP: is this still the case? Your issue seems more to be about looping over the appropriate arrays and dealing with their data?

          You can go about this in several ways: use plsql tables, use a temp table (possible if contained within this one process) or a collection - all to store your data temporarily. If you want to do actual sorting you might want to use a collection so you can use some sql. But you'll first have to push the data from the apex_application.g_f arrays to one of those places then.

          Does order even matter then? Can you not just loop over each record and increment the total amount each time, once you hit the limit you can then throw an error.

          By the way, the error message can contain html, so you could very well create, say, an unordered list (UL element) with list items in them, refering to rows by using the index of the loop iteration.


          Tell me if I'm wrong on any count.

          • 17. Re: Validating Tabular Form Column Against Value From Another Table

            Hi Tom,


            You are correct that I got the validation working fine for a row at a time, but I need to sort the data in the tabular form.  Here is an example below of my tabular form:


            Task Name      Invoice Value

            Task A            50000

            Task B            100000

            Task A            800

            Task C            50000

            Task B            3000


            Another table holds the maximum amounts for Task A, Task B, Task C, etc...  I need to make sure that all invoices in the tabular form for Task A, B, C, etc... do not exceed the maximum amount for their corresponding task in the other table. (So, the sum of invoices in the tabular form for Task A can not exceed the max value for Task A in the other table, and so on.)  I need a good way to sort the tabular form data by Task Name.  So, since the order does matter, it sounds like a collection might be appropriate?  Do you have any examples or any advice for the best way to approach this?  I hope this makes sense....


            Thank you!!


            • 18. Re: Validating Tabular Form Column Against Value From Another Table
              Tom Petrus

              Ah, I wasn't totally with you on that count then. My validation works globally but it adds up the amount for each row disregarding task. Okay - that wasn't totally my solution but more a show of the technique you can use to validate your tabular form in that way.

              You could solve that issue with a collection sure, or with a plsql table where you add an entry for each task name and keep the amount stored in there.


              Either way it'd be best for you to spend a bit of time on reading up on plsql collections and/or apex collections!


              Mind you, this is a sample and you'll need to take stock of it. The best solutions are those where you need to experiment 8)

                TYPE tt_taskamount IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
                t_taskamount tt_taskamount;
                l_task VARCHAR2(20);
                l_amount NUMBER;
                l_max NUMBER;
                -- F02 is the taskname
                -- F03 is the amount
                -- adapt to your situation!
                FOR i IN 1..apex_application.g_f02.count
                  l_task := apex_application.g_f02(i);
                  l_amount := apex_application.g_f03(i);
                  IF NOT t_taskamount.exists(l_task) THEN
                    t_taskamount(l_task) := l_amount;
                    t_taskamount(l_task) := t_taskamount(l_task) + l_amount;
                  END IF;
                  -- it's not the best idea to perform a select each time and could be
                  -- done better but for the sake of the example it's fine
                  -- not too bad either if the rowcount is small.
                  SELECT maxamount
                    INTO l_max
                    FROM table2
                   WHERE taskname = l_task;
                  IF t_taskamount(l_task) > l_max THEN
                    -- returning will stop further processing and throw an error message as soon as a budget has been overrun
                    -- it might or might not be required behaviour but that's up to you
                    RETURN 'The max amount ('||l_max||') for task ' || l_task ||' has been exceeded. Reduce the total amount on associated tasks!';
                  END IF;
                END LOOP;
              • 19. Re: Validating Tabular Form Column Against Value From Another Table

                Thank you, Tom!!!   This is EXACTLY what I needed!  I read the documentation, and I was able to adapt the code to my situation.  It works like a charm.  I really appreciate your help!!!!!

                1 2 Previous Next