How to validate user input with MS SQL Table ?

Question

Hi Community, 
 I have a user input task on my site. User fills the data and the data goes into MS SQL table. 
 I have a field (say PR No.) in my form. I want to validate the the data entered by user in the PR filed on the fly with my table to check whether PR No. entered by user exist in my table or not ? 
 How can i do it ? 
 
 Thanks for your help.

Krishna Chaitanya · Accepted Answer

create query rule as CheckPRNumber with below logic. 
 a!queryEntity( entity: cons!(Constant which refers your Pr NO Entity), query: a!query( filter : if( isnull(ri!PRNo), null, a!queryFilter( field :"PRNo", operator:"=", value :ri!PRNo ) ), pagingInfo:a!pagingInfo( startIndex: 1, batchSize: - 1 ) ) ) 
 this rule " rule!CheckPRNumber(ri!PRnumber)" returns empty data if data is not their for that PRNo,Now you can check with length or totalcount for validation 
 a!integerField( label: "PR", labelPosition: "ABOVE", value: ri!PRnumber, saveinto: ri!PRnumber, refreshAfter: "UNFOCUS", validations: if( isnull(PRnumber),{}, 
 if(rule!CheckPRNumber(ri!PRnumber).totalCount=0,{},"Invalid PR Number" 
 ) 
 ) )

chandu · Answer

The below code might help you, I didn't concentrated the validation part when user enters other than number you can validate when user enters other than numbers in the text field. let me know if you face any issue. 
 note: I took the rule input as Integer array type you can try with text also. 
 rule!getPNData(PN:{local!list})/* please replace here with your query entity rule this rule input need to take list*/ =load( local!list, local!dBlist, local!notInDB, a!sectionLayout( label: "Lorem Ipsum", firstColumnContents: { a!textField( label: "Lorem Ipsum", labelPosition: "ABOVE", value:ri!task, saveInto: {ri!task, if( rule!APN_isBlank(ri!task), {}, { a!save( local!list,fn!split(ri!task,",") ), a!save(local!list,tointeger(local!list)), a!save(local!dBlist, rule!getPNData(PN:{local!list})/* please replace here with your query entity rule this rule input need to take list*/ ), a!save(local!dBlist,tointeger(local!dBlist)), a!save(local!notInDB,difference(local!list,local!dBlist)) } ), }, refreshAfter: "UNFOCUS", validations: { if(rule!APN_isBlank(local!notInDB),"","Cannot able to fetch data for "&local!notInDB) } ), a!textField( label:"DB Data", value:local!dBlist ) }, secondColumnContents: { /* Add components here for a two-column section */ }, validations: {} ) )

sureshg57 · Answer

Hi prais1852 , 
 
I believe it is good to use fn!executestoredprocedure function. As you have lot of data in DB it is probably best to allow DB side to do all validations. You can pass all the IDs as a string and validate each in side stored procedure and return value that you can use for validation in the field. This way you can hand over the processing of the validation to the DB rather than doing in Appian. 
 
Regards 
Suresh