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.
Discussion posts and replies are publicly visible
Hi prup first you have to write query entity to fetch table data and then a!queryentity().data.PR No hear you wil get list of {PN No} then by using contains() function you can search the entered PR NO
example:
load( Local!PRNoList: a!queryEntity().data.PRNO, a!sectionLayout( label :"", contents: a!columnsLayout( columns: { a!columnLayout( contents: { a!textField( label: "PR NO", value: ri!PRNO, saveInto: ri!PRNO, validations: if( contains( local!PRNoList, ri!prNo ), "PR No Exists", {} ) ) } ) } ) ))
Hi Krishna Chaitanya I have almost two hundred thousand (2,00,000) record in PR table and when i used the Query rule i was able to fetch only 2000 records. If i try to fetch all record i get "Function evaluation error : Memory threshold reached during output conversion". Is there anyway to fix this problem ?
Hi Krishna Chaitanya i know the concept now but need help with syntax. I have created the Query Rule CheckPRNumber() and if i pass the PR number as parameter then it returns the value.
The same Query Rule i am trying to call in my interface like this -
a!integerField( label: "PR", labelPosition: "ABOVE", value: ri!PRnumber, saveinto: ri!PRnumber, refreshAfter: "UNFOCUS", validations: {if(rule!CheckPRNumber(ri!PRnumber), {}, "Invalid PR Number")} )
But i am getting error - Error evaluating function 'queryruleexec' : No value received for input [PRnumber] in query rule [CheckPRNumber].
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"
)
) )
Hi Krishna Chaitanya ,
Thank you for your suggestion. I fixed the problem this code -
a!integerField( label: "PR", labelPosition: "ABOVE", value: ri!PRnumber, saveinto: ri!PRnumber, refreshAfter: "UNFOCUS", validations: { if( isnull(ri!PRnumber), {}, if( length(rule!CheckPRNumber(ri!PRnumber)) = 0, "PR Number does not exist.", {} ) )