avoid insert duplicate information to database and give a hint to the user

Why not add a validation to the user name field which checks whether the entered value already exists?

would you give an example, i don't have sample code for "validation" part to study, i would be super appreciate

Please post your code using insert -> code

what do you mean

Without seeing your code I can not help.

a!localVariables(
  local!rule:rule!usernameRule(),
a!formLayout(
  label: "Username",
  contents: {
    a!textField(
      label: "username",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.username,
      saveInto: ri!username.username,
      refreshAfter: "UNFOCUS",
      validations: {}
    ),
    a!textField(
      label: "gender",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.gender,
      saveInto: ri!username.gender,
      refreshAfter: "UNFOCUS",
      validations: {}
    ),
    a!columnsLayout(
      columns: {
        a!columnLayout(
          contents: {
            a!textField(
              label: "Password",
              labelPosition: if(
                ri!readOnly,
                "ADJACENT",
                "ABOVE"
              ),
              value: ri!username.password,
              saveInto: ri!username.password,
              characterLimit: 255,
              readOnly: ri!readOnly
            )
          }
        )
      }
    ),
  },
 
  buttons: a!buttonLayout(
    primaryButtons: {
      a!buttonWidget(
        label: "Insert",
        
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:(ri!username)
           ),       
           ),
           
        submit: if(ri!username.username in local!rule.username
        ,false,true),
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:ri!username,           
          ),
        ),
        submit: true,
        style: "PRIMARY"
      ),
      a!buttonWidget(
        label: "delete",      
        saveInto:(
          a!deleteFromDataStoreEntities(
            dataToDelete: {
              a!entityDataIdentifiers(
                entity: cons!usernameConstant,
                identifiers:{ri!username.username},
              )
            }
          )
        ),
        submit: true,
        style: "PRIMARY"
      )
    },
    secondaryButtons: {
      a!buttonWidget(
        label: "Cancel",
        value: true,
        saveInto: ri!cancel,
        submit: true,
        style: "NORMAL",
        validate: false
      )
    },
    showWhen: or(
      isnull(
        ri!readOnly
      ),
      not(
        ri!readOnly
      )
    )
  )
 
)
)

I am going to create a back end system, but have to be without any duplicate primary key insert into, or for registration the user will not figure out. The question only for row of 52 to 67, i tried multiple ways not let back end update any function for duplicate primary key been inserted, and let user know he have to choose a different one. But know the error report as "Could not display interface. Please check definition and inputs. Interface Definition: Expression evaluation error at function 'if' [line 64]: Invalid number of parameters, function 'if' requires an odd number of parameters, passed 4.". I still have no idea what is the mistake. 

This is very confusing ...

ri!username.username in local!rule.username,

is no valid Appian syntax

I just want it figure out whether username fill in text blank section could be found under the database, that local!rule is searching all back end username create by a constant. What could be a standard way to do so?

Did you try adding the validation?

why my totalCount always -1, though the value do exist when use a!query?

This is when you set fetchTotalCount to false. As this is also the default, you want to set it explicitly to true.

This is when you set fetchTotalCount to false. As this is also the default, you want to set it explicitly to true.

a!localVariables(
a!formLayout(
  label: "Username",
  contents: {
    a!textField(
      label: "username",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.username,
      saveInto: ri!username.username,
      refreshAfter: "UNFOCUS",
      validations: {}
    ),
    a!textField(
      label: "gender",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.gender,
      saveInto: ri!username.gender,
      refreshAfter: "UNFOCUS",
      validations: {}
    ),
    a!columnsLayout(
      columns: {
        a!columnLayout(
          contents: {
            a!textField(
              label: "Password",
              labelPosition: if(
                ri!readOnly,
                "ADJACENT",
                "ABOVE"
              ),
              value: ri!username.password,
              saveInto: ri!username.password,
              characterLimit: 255,
              readOnly: ri!readOnly
            )
          }
        )
      }
    ),
  },
 
  buttons: a!buttonLayout(
    primaryButtons: {
      a!buttonWidget(
        label: "Insert",
        
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:(ri!username)
           ),       
           ),
           
        submit: if(rule!username_detail(ri!username.username)<>0,false,true),
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:ri!username,           
          ),
        ),
        submit: true,
        style: "PRIMARY"
      ),
      a!buttonWidget(
        label: "delete",      
        saveInto:(
          a!deleteFromDataStoreEntities(
            dataToDelete: {
              a!entityDataIdentifiers(
                entity: cons!usernameConstant,
                identifiers:{ri!username.username},
              )
            }
          )
        ),
        submit: true,
        style: "PRIMARY"
      )
    },
    secondaryButtons: {
      a!buttonWidget(
        label: "Cancel",
        value: true,
        saveInto: ri!cancel,
        submit: true,
        style: "NORMAL",
        validate: false
      )
    },
    showWhen: or(
      isnull(
        ri!readOnly
      ),
      not(
        ri!readOnly
      )
    )
  )
 
)
)

a!queryEntity(
  entity: cons!usernameConstant,
  query: a!query(
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 50
    ),
    logicalexpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "username",
          operator: "=",
          value: ri!username
        )
      }
    ),
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "username",
          isGrouping: true
        ),
        a!queryAggregationColumn(
          field: "username",
          alias: "number",
          aggregationFunction: "COUNT"
        )
      }
    ),
  ),
).data

Line 15

validations: {
  if(
    a!queryEntity(
      entity: cons!usernameConstant,
      fetchTotalCount: true,
      query: a!query(
        pagingInfo: a!pagingInfo(
          startIndex: 1,
          batchSize: 1
        ),
        filter: a!queryFilter(
          field: "username",
          operator: "=",
          value: ri!username.username
        )
      )
    ).totalCount > 0,
    "Username exists",
    null
  )
}

where should I put validation is appropriate?

"username exists" means it won't do any action, null means it won't prevent it implement action on insert or update delete right?

If the parameter "validate" for a button is set to true, it will perform all validations on the interface before submitting. In case there is any non-null validation it will not submit.

Check this and filter for "Validation" below "Search Patterns"

docs.appian.com/.../SAIL_Recipes.html

Thanks for your help, but seems lastly could you tell me how to get rid of error of "When the value of "operator" is "=" "value" must not be null or empty." from your validation part give me away? I think the value can't be empty, but i can't throw its error away. Thank you