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 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.