How to validate user input with MS SQL Table ?

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.",
        {}
      )
    )

Hi kchaitanyam  I have changed my rule input to array now. But the problem is on the interface. How should i take the user input for PR field. Earlier i was using a!IntegerField() but i can not use it now because if there are 2 PR nos then user will enter both numbers using comma(,) as separator.

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.",
        {}
      )
    )

What changes should i do to make it work ? Also please help me with creating the other expression rule to detect duplicate numbers if possible.

Hi kchaitanyam  I have changed my rule input to array now. But the problem is on the interface. How should i take the user input for PR field. Earlier i was using a!IntegerField() but i can not use it now because if there are 2 PR nos then user will enter both numbers using comma(,) as separator.

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.",
        {}
      )
    )

What changes should i do to make it work ? Also please help me with creating the other expression rule to detect duplicate numbers if possible.

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: {}
)
)