Skip to main content
August 30, 2021
Solved

validation problem on avoid duplicate value for insert and non-exist value for update

  • August 30, 2021
  • 20 replies
  • 0 views

a!localVariables(
  local!insert:false,
  local!update:false,
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: {
        if(
          a!queryEntity(
            entity: cons!usernameConstant,
            query: a!query(
              fetchTotalCount: true,
              pagingInfo: a!pagingInfo(
                startIndex: 1,
                batchSize: 1
              ),
              logicalexpression: a!queryLogicalExpression(
                operator:"OR",
              filters: {
              a!queryFilter(
                field: "username",
                operator: "=",
                value:(ri!username.username),
                applyWhen:not(isnull(ri!username.username))               
              ),
              }
              )
            )
          ).totalCount > 0,
          local!insert:true,
          local!update:true
        ),
      }
    ),
    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)
           ),       
           ),
        disabled:local!insert,  
        submit: true,
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:ri!username,           
          ),
        ),
        disabled:local!update,
        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
      )
    )
  )
 
)
)

For line 17 validation part nearby, i wish the button insert will not work if it find the duplicate username already in database, and disable update button for the username can't be found, that is what i put in the validation part of if condition sentences, however, it report error with "Expression evaluation error at function a!textField", then how can I do?

    Best answer by stefanhelzle0001

    fetchTotalCount ... do you remember?

    20 replies

    Participating Frequently
    August 30, 2021

    Hi [mention:96cf11e6657d4c49afc95dac553d36d5:e9ed411860ed4f2ba0265705b8793d05] - You are trying to assign value in validation statement, hence the error. Try using below code and hopefully it should resolve your error.

    validations: {
      if(
        or(
          a!queryEntity(
            entity: cons!usernameConstant,
            fetchtotalcount: true,
            query: a!query(
              /*fetchTotalCount: true,*/
              pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1),
              logicalexpression: a!queryLogicalExpression(
                ignoreFiltersWithEmptyValues: true,
                operator: "OR",
                filters: {
                  a!queryFilter(
                    field: "username",
                    operator: "=",
                    value: (ri!username.username)
                  ),
                  
                }
              )
            )
          ).totalCount > 0,
          isnull(ri!username.username)
        ),
        "Validation Message",
        {}
      )
    }

    August 30, 2021

    i wish i can find a way to let the button being disable once found duplicate errors

    Participating Frequently
    August 30, 2021

    Try below code:

    a!formLayout(
    label: "Username",
    contents: {
    a!textField(
    label: "username",
    labelPosition: if(local!readOnly, "ADJACENT", "ABOVE"),
    value: ri!username.username,
    saveInto: {
    ri!username.username,
    a!save(
    local!totalCount,
    a!queryEntity(
    entity: cons!usernameConstant,
    fetchtotalcount: true,
    query: a!query(
    /*fetchTotalCount: true,*/
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1),
    logicalexpression: a!queryLogicalExpression(
    ignorefilterswithemptyvalues: true,
    operator: "OR",
    filters: {
    a!queryFilter(
    field: "name",
    operator: "=",
    value: (ri!username.username)/*applyWhen:not(isnull(ri!username.username)) */

    ),

    }
    )
    )
    ).totalCount
    )
    },
    refreshAfter: "UNFOCUS",
    validations: {
    if(
    local!totalCount > 0,
    "Validation Message",
    {}
    ),

    }
    )

    },
    buttons: a!buttonLayout(
    primaryButtons: {
    a!buttonWidget(
    label: "Insert",
    saveInto: (
    a!writeToDataStoreEntity(
    dataStoreEntity: cons!usernameConstant,
    valueToStore: (ri!username.username)
    ),

    ),
    disabled: local!totalCount>0,
    submit: true,
    style: "PRIMARY",

    )

    harshas2775
    Brainy
    August 30, 2021

    There are few things you need to update in order to fulfill your usecase. 

    1. Declare one local variable say, usernameCheck and call the a!queryEntity() that you are currently calling within validation block, here, corresponding to this local variable.

     

    a!localVariables(
    local!userNameCheck:a!refreshVariable(if(isnull(ri!userName.userName),{},if(a!queryEntity(
    entity:cons!YOURENTITY,
    query:a!query(
    filters:a!queryFilter(field:"username",operator:"=",value:ri!userName.userName),
    pagingInfo:local!pagingInfo)).totalCount>0,true(),false())))
    /*Rest of the code*/
    )

    Here this local can help you disable or enable the Insert/Update buttons based on its value i.e. true/false. So when local!userNameCheck is false means username doesn't exist in db so Update can't be enabled. vice versa for Insert button can be done!

    2. As per my understanding you don't need the validations block if you don't want to send out a message to the users of any sort. If my understanding is correct, you can remove the validations block entirely. Just add a showWhen in Insert/Update buttons such as

    a!buttonWidget(
    label:"Update",
    showWhen:local!userNameCheck=true(),
    /*Rest of the code*/)
    a!buttonWidget(
    label:"Insert",
    showWhen:local!userNameCheck=false(),
    /*Rest of the code*/)

    Also, I would suggest you to go through documentations and training materials available in Appian Academy as those will help you better understand how to work with different appian form fields or smart services.

    August 30, 2021

    a!localVariables(
      local!valid:a!refreshVariable(
        if(isnull(ri!userName.userName),{},if
        (a!queryEntity(
        entity:cons!usernameConstant,
        query:a!query(
          filter:a!queryFilter(
            field:"username",operator:"=",value:ri!username.username
          ),
          pagingInfo:a!pagingInfo(
            startIndex: 1,
            batchSize: 50
          ))).totalCount>0,true(),false()))
      ),
    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",
          
        ),
        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",
            disabled: local!valid,
            saveInto:(
              a!writeToDataStoreEntity(
                dataStoreEntity: cons!usernameConstant,
                valueToStore:(ri!username)
               ),       
               ),
            
            submit: 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 did as what you said, but once i put some duplicate value in the username, but why the insert button still not disabled? 

    harshas2775
    Brainy
    August 30, 2021

    Can you update your disabled attribute in buttonWidget to disabled: local!valid=true()  and check again?

    Also, keep a check of the value present in local!valid. you can check it in the right hand side, below rule inputs section in the interface.