1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Validation on Tabular Form Fields

Discussion in 'Oracle Application Express (APEX)' started by Bharat, Oct 1, 2014.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi All,

    We have designed an Tabular Form with 2 Updateable fields and enabled Update Only Mode on this form.

    Two fields information:
    1. Valid
    2. Comments

    If field-1 (Valid) is Yes then Comments field should has to be mandatory.
    If field-1 (Valid) is No then Comments field should be optional.

    Is this scenario possible to do through Validations or by creating process ? Please suggest the suitable solution on this requirement.
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Just create a validation process on the Comments field, type PL/SQL Function returning Boolean

    IF valid = TRUE and comments IS NOT NULL then
    return TRUE
    else
    return FALSE
    end if

    I do not believe you can change the properties of the Comment field to make it required on the fly. Also, making the Comment mandatory is questionable. How do you prevent nonsense entries like "." or "none" or "qwerty" ???

    HTH

    CJ
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi CJ,

    we have now Created a Validation Process as suggested and when we try to open the page, its erroring out as following:

    failed to parse SQL query:
    ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

    Can you please suggest us on this.
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    If the error shows up when the page loads that sounds like you have the Validation set to Run Always. Attach the Validation to an event like a button click and that should go away.
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi CJ,

    Attached image contains the properties configured in the validation. We have created Validation with the condition called button press. But still we were able to see the error message as attached in the error.png file.
     

    Attached Files:

  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Under the Validation section...is Always Execute set to Yes or No?
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes CJ, Always Execute is set to No
     
  8. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Perhaps you should set up an example on Oracle's hosted APEX site...
     
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi CJ,

    Here the issue is due to the query we used on the Tabular Form. We used Max, Min and Count functions on the query to return these values for each record as we will have multiple records for each item. So this is the reason I think we are facing this issue. Can you please confirm this.
     
  10. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    So your query has functions whose values, if altered, don't necessarily belong to a row? Is the page item source a SQL Query or a Database Column? You may have to replace your Save button processes w/ a custom one to handle your Insert/Update processes.
     
  11. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes CJ, the alteration does not belongs to single row as we are retrieving the max, min values for set of rows. We used SQL Query source as page item.
     
  12. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Create your own Save processes then...and I was thinking about your mandatory comments again.

    I have dates that I want to be display only if they have values and if they don't then show the date picker. The only way I've found to do this was to create TWO date fields and a hidden item to hold the passed in value. Then create display conditions for both items based on whether the hidden item had a value or not. You might be able do the same w/ your Comment field using the hide/show abilities via Dynamic Action.
     
  13. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi CJ,

    This is achieved through Creating Process for validating all the rows in the tabular form with PL/SQL Programming and then populating an error message if validation fails for any one of the record. And Now if Valid flag is selected for any record and try to save the form, then the page is going to pull an error message saying "Please enter COMMENTS Field" as required.