Question regarding forms and field validation

Certified Senior Developer

need a quick solution on one of the problem statement in Quantum
Form -> fields validation to be done from the pre-configured table validation queries
example:-
- lets assume a form has 5 fields
- 5 fields has 5 different validations
- validations are stored in the database in the form of 5 distinct queries
1. Form Name
2. Field Name
3. Validation ( queries, which is stored in validation table against each field in the below form:
ex : to validate age field the query in the db : select age from table1 where formName = "form1"
and age > 40)
(there will be 5 different, queries like this for each form elements)


Question:-

Is there a ready made solution in Apian for ...

OriginalPostID-191735

OriginalPostID-191735

  Discussion posts and replies are publicly visible

  • 0
    Certified Senior Developer
    ...the above problem?

    Validating a form based elements against queries stored in database?

    Executing queries stored in the db as validator for form elements in UI.
  • Hi Ajoy,
    If I have understood your problem statement correctly then I think you can use query entity to get the validation stored in the database, below mentioned is the reference :

    forum.appian.com/.../System_Functions.html

    You can add multiple filters based on your validation criteria in queryEntity() itself. Then pass on the form field values in the rule so as to validate the field.
  • @ajoyr To answer your question straight to the point, I don't think there is a ready made solution available.

    If I understand correctly, the 'Validation' field in database table contains a value in form of Text, which could be a typical native SQL statement. If so, this kind of facility isn't available OOTB in the product. But I think you may need to write expression rules in Appian to parse these native SQL statements or write a function which executes SQL statements and obtain the results accordingly. From my perspective, this kind of approach may not be great, because ultimately the performance of the SAIL interface goes down upon the increase in the validations and their associated complexities. Still I would leave this to other practitioners as I haven't dealt with this so far.

    As suggested above, you could use queryEntity() but I think it requires a revision of database structure as well. For instance, if we need to validate against the age when it's below 40, then we need to store the same fact in the database as well.
  • Hi ajoyr, AFAIK currently there is no OOTB feature available, and if I read the requirement, basically everything on the form need to be dynamically driven. the form name, the field labels and validation rules.. in this case, as sonalk suggested we can have a form created, on the form are fixed, inside the load, define your local variables and retrieve the validation limits and then use them in the validations part. if they are dependent then use the rule to get the value dynamically in the validation through query entity rules. I am hoping this will not have the dynamic components and the components are fixed.
  • @ajoyr Additionally I would like to suggest to validate the approach with someone who has an experience in implementation along same lines. From my perspective, rather than speaking about the technical possibility, it would be worth considering the pros and cons of the approach.
  • 0
    Certified Senior Developer
    Thanks all for the valuable responses, just to give a background this is an existing solution developed in Sybase/Java. So chances of looking for a pros/cons in almost nil. Need a Appian solution, which can build in a validation module as per the initial requirement. I am fairly new to Appian, I was initially thinking on the direction of Narasimha, also I liked the option suggested by Sonal.
    I would appreciate if you guys can provide some code snippets with your implementation thought.
  • 0
    Certified Senior Developer
    Hi Sikhivahans, thanks for your both solutions "expression rules in Appian to parse these native SQL statements or write a function which executes SQL statements and obtain the results accordingly".
    Do you by any chance to help with a code snippet? or any forum reference?
    How can we execute SQL statements with in a expression rule and return the value? I found there is an option using Query Database smart service, is this what you referring to?
  • @ajoyr May I please ask you one simple question? - Does the 'Validation' column in the database holds native sql statements in form of text? I would like to answer from my experience re the approaches discussed here based on your answer.
  • 0
    Certified Senior Developer
    Yes, you are correct. For example, if there a form field named [Age] in the Interface and the validation for the field age, the user should restrict enter age less than 18.
    The database will have a text query like for the [Age] field
    "select 1 from table where age>=18) - the query returns true or false OR 0 or 1 ,
    the validation should fire if the end user enter age less than 18.
    Please feel free to ask if you have further questions
  • @ajoy, just a curious question, if they are simple validation rules, why can't they be implemented using an expression rules? Secondly if they are complex rules involving multiple fields comparision, Can we try something like a decision table as described in /search?q=OriginalPostID-164004.