This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Sep 11, 2013 8:56 AM by labaublitz RSS

Validating Tabular Form Column Against Value From Another Table

labaublitz Newbie
Currently Being Moderated

Hi,

I am brand new to this forum, so please bear with me a little!  I only have a small amount of experience writing PL/SQL, and I've never written Javascript or JQuery before.  I am an Oracle DBA, and I have coding experience in C and PERL, so I do have a solid technical background.  But, I need some advice on the best way to approach a problem.

 

I have a database application in Oracle Apex (version 4.2) with a tabular form against a table: let's say Table #1 with cols 1A, 1B, and1C.  I need to ensure that the value entered into col B isn't greater than the value of a column in another table (let's say Table #2 col 2A).  Conceptually, the amount of money available is in Table #2, and the rows of my tabular form are an act of spending money (like orders or invoices), so I need to make sure we don't spend more than we have.  Does that make sense?

 

Does anyone have any advice for the best way to do this?  I'm figuring the biggest issue here might be that we have to account for people entering mutliple rows in the tabular form at one time, right?  So, if a person entered 3 orders/invoices, I need a total to make sure they didn't spend more than we have in Table #2.

 

I greatly appreciate your help! 

 

Best Regards,

Laurie Baublitz

  • 1. Re: Validating Tabular Form Column Against Value From Another Table
    Ramesh P, Oracle APEX Developer Journeyer
    Currently Being Moderated

    TABLE1

    COLS: A1, B1, C1

     

    TABLE2

    COLS: A2, B2, C2

     

    Lets say validate B1 aganist B2

     

    Create a validation by selecting the tabular form on your page from validations block

    Select PLSQL returning text.

     

    Select column B1

    DECLARE
    v_val VARCHAR2(100);

    BEGIN

    SELECT B2 INTO v_val FROM TABLE2 WHERE A2 = :A1;

    IF v_val > :B1 THEN

    RETURN 'The value you entered cannot exceed amount';

    ELSE

    RETURN NULL;

    END;

  • 2. Re: Validating Tabular Form Column Against Value From Another Table
    labaublitz Newbie
    Currently Being Moderated

    Thank you for the suggestion!  I'll give it a try as soon as I can.  But, unfortunately, it still doesn't resolve the case where a user enters multiple orders/invoices (rows in the tabular form) at a time.  Let's say they enter 3 rows.  The last row they enter might pass this validation, but they've really spent more money than we have because this validation doesn't account for other 2 new rows added.  I think I need to do some looping somewhere....

  • 3. Re: Validating Tabular Form Column Against Value From Another Table
    drama9346 Newbie
    Currently Being Moderated

    Hi!

    You need one process of type ajax callbacks like:

     

    DECLARE
       l_limit nubmer;
       l_number1 number := apex_application.g_x02;
       l_returnValue VARCHAR2(200);
    BEGIN
       select A2 into l_limit from table2;
       if l_number1 > l_limit then
          l_returnValue := 'LIMIT IS NOT SO BIG';
          if l_returnValue is not null then
             --this will write l_returnValue to the buffer, and the ajax callback will receive this
            htp.p(l_returnValue);
          end if;
      end if;
    END;
    
    

    Then you need one javascript on page, code is something like:

     

    $('input[name=your column in tabular which is change]').live('change', function(){
       //if value of changed field differs from an empty string
       if($(this).val()!=''){
          //put target element in a var to reference it in the ajax success callback
          var num  = $('input[name=your column in tabular with value]');
          $.post('wwv_flow.show',
                 {"p_request"      : "APPLICATION_PROCESS=your ajax callback function",
                  "p_flow_id"      : $v('pFlowId'),
                  "p_flow_step_id" : $v('pFlowStepId'),
                  "p_instance"     : $v('pInstance'),
                  "x01"            : $(this).val(),
                  "x02"            : $(num).val()
                  },
                  function(data){
                     if(data !=''){
                     alert(data);
                     };
                  }
                  );
       };
    });
    
    

     

    I can not guarante that code is 100% working, if not you need to do some changes or make an example on apex.oracle.com and provide credentials here.

     

    Regards,

    drama9346

  • 4. Re: Validating Tabular Form Column Against Value From Another Table
    NoGot Explorer
    Currently Being Moderated

    It's a very bad idea. Never trust the browser!!!

  • 5. Re: Validating Tabular Form Column Against Value From Another Table
    Tom Petrus Expert
    Currently Being Moderated

    It's true. Such javascript should not be your first and only line of defense - but it can make a nice addition. In this case you'll have to be aware of some pitfalls with it though. Numbers and formatting not in the least, but also handling the reduced amount when you increase an amount somewhere (what if changing the same amount twice? Three times?)

     

    It's probably more practical to loop over the arrays in a validation. The sort of validation Ramesh has suggested is relatively new, and works good for validating a single row of a tabular form, but you can still use the other notations. You'll need to work out what values are mapped to which array though, since values in tabular forms are stored in the apex_application.g_f## arrays.

    That way you could create a loop and thus keep tabs on the correct amount.

    Eg, the value is in array f03. Define a page level validation, plsql function body returning error message.

    DECLARE
      l_amount NUMBER := 0;
      l_max_amount NUMBER := 1000;
    BEGIN
      FOR i IN 1..apex_application.g_f03.count
      LOOP
        l_amount := l_amount + TO_NUMBER(apex_application.g_f03(i));
      END LOOP;
      IF l_amount > l_max_amount THEN
        RETURN 'You assigned more than the maximum allowed!';
      END IF;
    END;
  • 6. Re: Validating Tabular Form Column Against Value From Another Table
    NoGot Explorer
    Currently Being Moderated

    I don't understand, in Table #2 only one row or more? Do you also must filter rows depending on what row edited in Table #1?

     

    If there is only one row, and amount of money in table#2 doesn't depend on what row is edited at this moment, solution is simple.

    First, add hidden item, that will calculate current summ of orders. For example P1_SUMM. In source of the item set 0, always replacing value.

    Then add validation linked to your tabular form. Type: function returning boolean. Execution Scope: for created and modified rows.

    Source of this validation function includes two steps:

    Step 1. Get current value of the row. There is two ways:

       a). documented (by apex_application.g_f01, apex_application.g_f02)

       b). with some undocumented features (see link below)

       Both this features you may see at this link http://stackoverflow.com/questions/9367759/apex-tabular-form-with-row-selector-how-to-set-value-of-column-in-selected-ro

    Step 2. add current value to P1_SUMM, then validate value of P1_SUMM with value in the table#2, and if all ok, return true. If it's not ok, then return false.

    So, if you add several rows, this validation will loop over all edited/added rows, save summ of all rows and validate that summ.


    Sorry for my english, i hope my answer is clear.

  • 8. Re: Validating Tabular Form Column Against Value From Another Table
    labaublitz Newbie
    Currently Being Moderated

    Thank you so much for all of the suggestions!  I won't get a chance to try any of these until late tomorrow.  But, I'll make sure to update the thread as soon as I can and let you all know how it's going.  Thanks!!

  • 9. Re: Validating Tabular Form Column Against Value From Another Table
    Tom Petrus Expert
    Currently Being Moderated

    Hi NoGot,

    Honestly, initially I did not Then when you linked the question it popped to the fore again, yes. It didn't really spring to mind as I try to stay away from constructing my tabular form in such a way myself, and usually when I rummage through other's code I'm always seeing the f## loops.

    However as you can see getting the mapped values requires a row number, so you may need to have the for loop construct around the mapped value call. Still though, I agree it helps alleviate the array calls and makes it a lot less sensitive to changes in the report (change a column's place fe). Still unfortunate this is not documented though - using it and getting back for help to someone may lead to taking flak for it and people may want to avoid it. But hey - if you know what you are doing and you yourself document it: more power to you. I mean, if you implement it, put a couple of lines of commentary above it so you or someone else knows what is happening and you should be fine!

    More options: always good

  • 10. Re: Validating Tabular Form Column Against Value From Another Table
    labaublitz Newbie
    Currently Being Moderated

    Thank you so much Rmaesh!  I got this to work for a single row.  I'll just have to work on looping through the rows next.  But, I really appreciate you getting me off to a good start!!!

  • 11. Re: Validating Tabular Form Column Against Value From Another Table
    labaublitz Newbie
    Currently Being Moderated

    Thank you for the guidance on looping through the rows, Tom!  I'll try this next week.  I appreciate your help!!!

  • 12. Re: Validating Tabular Form Column Against Value From Another Table
    labaublitz Newbie
    Currently Being Moderated

    Hi Tom,

     

    I was able to get Ramesh's validation to work on a single row (just to ensure I knew I could do it and understood what was going on as a first step), and then I was able to get your looping example as a page validation on the tabular form to work as you wrote it.  I understand where you're going and what the code is doing.  But, I need to sort the values of the tabular form on apex_application array value f02 (for example).  So, let's say f02 is the name of something and f03 is the invoice value.  I need to add up the invoice values for all f02's that are the same.  Here's a sample of what I'm seeing on the page:

     

    Name    Invoice Value

    A          100.00

    B          150.00

    A          200.00

    D          325.00

    D          225.00

     

    So, your page validation idea is perfect, but I need to write the code and loop so that I add up all A's and compare them against the max value, then add up all B's and compare them against the max value, etc...  And, they could be in any order on the page.  If this was another programming language, I would probably use multidimensional arrays and have this finished already, but I'm not as familar with PL/SQL.  So, how do you recommend I approach this?  Is there something similar to multidimensional arrays in PL/SQL, or is that just way more complicated than is necessary?  Are Apex Collections appropriate here?  Or, can you create temporary tables in PL/SQL?  What do you think is the most efficient method?

     

    I greatly appreciate your patience and help!!!

    Laurie Baublitz

  • 13. Re: Validating Tabular Form Column Against Value From Another Table
    Ramesh P, Oracle APEX Developer Journeyer
    Currently Being Moderated

    Hi,

    You can do it for all the rows on the page in the same call, but little different.

    Create a page item, lets say :P1_MESSAGE

     

    Create a page process, after submit, before computations and validations

     

     

    DECLARE

        v_val VARCHAR2(100);

        v_msg VARCHAR2(32000);

    BEGIN   

        --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;   

    END;

     

     

    In validation, write pl/sql returning text, not based on plsql (page validation based on plsql returning text)

     

    BEGIN

        IF :P1_MESSAGE IS NOT NULL THEN

            RETURN :P1_MESSAGE;

        ELSE

            RETURN NULL;

        END IF;

    END;   

  • 14. Re: Validating Tabular Form Column Against Value From Another Table
    labaublitz Newbie
    Currently Being Moderated

    Hi Ramesh,

     

    Thank you very much for this suggestion!  I'm on the right track, but I'm still getting derailed a little.  I changed the page process code that you gave me to set P1_MESSAGE equal to v_msg because I think that's where we want to set it's value, right? 

     

    IF v_val > apex_application.g_f02(i) THEN

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

            END IF;   

     

    This seems to work, and if I set the Process Success Message to &P1_MESSAGE. then I see the error message.  (I did this just to see if it was working - testing purposes only.)  But, even after creating the page validation using the code you gave me, it still submits the record.  So, I suspect that I created the page item incorrectly.  I apologize in advance if I'm missing something obvious, but what is the new page item's source type (P1_MESSAGE)?  Is it a hidden page item or a display only?  Maybe I have the source type and page item type wrong?

     

    Thank you!!!

    Laurie

1 2 Previous Next

Legend

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