3 Replies Latest reply: Apr 3, 2014 6:39 AM by Bhramar RSS

    Tabular Form Duplicate Value Validation

    olivares

      Hi Everyone:

      I am using APEX 4.1 and I am trying to create a validation for my tabular form.  Before adding the records to my table I would like to check if any of the rows have duplicate values.  I would like to use the tabular for validation to be able to highlight the duplicate rows.

      I have the following code validation:

      DECLARE
      error_msg VARCHAR2(200) := NULL;
      BEGIN
      --Loops through all items
      FOR i IN 1..APEX_APPLICATION.G_F02.COUNT LOOP
        IF (APEX_APPLICATION.G_F02(i) IS NOT NULL) THEN
          FOR j IN 1..APEX_APPLICATION.G_F02.COUNT LOOP
            IF (APEX_APPLICATION.G_F02(i) = APEX_APPLICATION.G_F02(j)) AND
               (i != j) THEN
              error_msg := 'The column string ('||APEX_APPLICATION.G_F02(i)||') has multiple entries.';
              RETURN error_msg;
            END IF;
          END LOOP;
        END IF;
      END LOOP;
      RETURN error_msg;
      end;

      Also, I know when you create a tabular form it ask you to identify the validation level (column/tabular for row).  Is it possible to use the column instead of the APEX_APPLICATION arrays?

      PS: I am familiarizing myself with the new features for APEX 4.1

      Any help will be greatly appreciated.

      Thank you,

      Olivares

        • 1. Re: Tabular Form Duplicate Value Validation
          sect55

           

          FOR i IN 1..APEX_APPLICATION.G_F02.COUNT LOOP

            IF (APEX_APPLICATION.G_F02(i) IS NOT NULL) THEN

              FOR j IN 1..APEX_APPLICATION.G_F02.COUNT LOOP

                IF (APEX_APPLICATION.G_F02(i) = APEX_APPLICATION.G_F02(j)) AND

                   (i != j) THEN

                  error_msg := 'The column string ('||APEX_APPLICATION.G_F02(i)||') has multiple entries.';

                  RETURN error_msg;

                END IF;

              END LOOP;

            END IF;

          END LOOP;

          You need to check if APEX_APPLICATION.G_F02(j) is not null before you compare the two values. So it should look like this:

           

           

          FOR i IN 1..APEX_APPLICATION.G_F02.COUNT LOOP

            IF (APEX_APPLICATION.G_F02(i) IS NOT NULL) THEN

              FOR j IN 1..APEX_APPLICATION.G_F02.COUNT LOOP
                IF (APEX_APPLICATION.G_F02(j) IS NOT NULL) THEN

                   IF (APEX_APPLICATION.G_F02(i) = APEX_APPLICATION.G_F02(j)) AND

                     

           

          (i != j) THEN

                     error_msg := 'The column string ('||APEX_APPLICATION.G_F02(i)||') has multiple entries.';

                   

           

          RETURN error_msg;

                

           

          END IF;
                END IF;

              END LOOP;

            END IF;

          END LOOP;

          Also you can use the :column_name instead of APEX_APPLICATION.G_F02 in APEX 4.1 and above.

           

          Robert

          http://apexjscss.blogspot.com

          • 2. Re: Tabular Form Duplicate Value Validation
            olivares

            Hi Robert,

             

            Thank you for the input.  I was able to figure it out.

             

            Thanks,

             

            Olivares

            • 3. Re: Tabular Form Duplicate Value Validation
              Bhramar

              If you want to use the label of your Tabular Form Columns, you can do this:

               

              v('ITEM_NAME')