• State Verified Answer
  • Replies 20 replies
  • Answers 1 answer
  • Subscribers 13 subscribers
  • Views 4079 views
  • Users 0 members are here
Related Discussions
Home » Discussions » New to Appian

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

immortalvirgil 

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?

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer

    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.

  • 0 in reply to Harsha Sharma 

    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? 

  • 0
    Certified Lead Developer
     in reply to immortalvirgil

    weird! is the input value having extra spaces in it (if yes, use trim(ri!username.username corresponding to filter value) or, if there is any case difference between input and the value in db? check that as well.

    If all is fine then may be value is not refreshing for the local!valid. To confirm that I would make some change to assure that local!valid is updated accurately every time. try the below code then!

    a!localVariables(
  local!valid,
a!formLayout(
  label: "Username",
  contents: {
    a!textField(
      label: "username",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.username,
      saveInto: {ri!username.username,
      a!save(local!valid,
      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())))},
      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=true(),
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:(ri!username)
           ),       
           ),
        
        submit: true,
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
         disabled: local!valid=false(),
        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
      )
    )
  )
 
)
)

  • 0 in reply to Harsha Sharma 

    a!localVariables(
  local!valid,
a!formLayout(
  label: "Username",
  contents: {
    a!textField(
      label: "username",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.username,
      saveInto: {ri!username.username,
      a!save(local!valid,
      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()))),},

      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=true(),
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:(ri!username)
           ),       
           ),
        
        submit: true,
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
        disabled: local!valid=false(),
        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
      )
    )
  )
 
)
)

    still same thing, i don't think the buffering speed is slow, perhaps other problem

  • 0
    Certified Lead Developer
     in reply to immortalvirgil

    Few things that I would have started with to find the issue is :

    • Is your cdt having "username" column in it and in the same way as mentioned for field:"username"?
    • Open a blank expression rule and test just the queryentity code. See what is the output returned for data as well as totalCount
    • entity:cons!usernameConstant, very less chances to miss it but still check if the data store is having the entity for username cdt and is published and verified successfully!
  • 0 in reply to Harsha Sharma

    totalcount always equal -1,i think this may be the issue

  • +1
    Certified Lead Developer
     in reply to immortalvirgil

    fetchTotalCount ... do you remember?

  • +1
    Certified Lead Developer
     in reply to immortalvirgil

    Update queryEntity() to have fetchtotal count to true() such as 

    a!queryEntity(
        entity:cons!usernameConstant,
        fetchTotalCount:true(),
        query:a!query(
          filter:a!queryFilter(
            field:"username",operator:"=",
            value:ri!username.username
          ),
          pagingInfo:a!pagingInfo(
            startIndex: 1,
            batchSize: 50
          )))

  • 0
    Certified Lead Developer
     in reply to immortalvirgil

    A yellow line ?!?!?

    a!queryEntity has that parameter. Set it to true.

  • 0 in reply to Harsha Sharma 

    a!localVariables(
  local!valid,
a!formLayout(
  label: "Username",
  contents: {
    a!textField(
      label: "username",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.username,
      saveInto: {ri!username.username,
      a!save(local!valid,
      if(isnull(ri!userName.userName),{},
      if(a!queryEntity(      
        entity:cons!usernameConstant,
        fetchTotalCount:true,
        query:a!query(
          filter:a!queryFilter(
            field:"username",operator:"=",
            value:ri!username.username
          ),
          pagingInfo:a!pagingInfo(
            startIndex: 1,
            batchSize: 50
          ),
        
          )).totalCount>0,true(),false()))),},

      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=true(),
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:(ri!username)
           ),       
           ),
        
        submit: true,
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
        disabled: local!valid=false(),
        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
      )
    )
  )
 
)
)

    still not work, buffer memory slow?

Reply
  • 0 in reply to Harsha Sharma 

    a!localVariables(
  local!valid,
a!formLayout(
  label: "Username",
  contents: {
    a!textField(
      label: "username",
      labelPosition: if(
        ri!readOnly,
        "ADJACENT",
        "ABOVE"
      ),
      value: ri!username.username,
      saveInto: {ri!username.username,
      a!save(local!valid,
      if(isnull(ri!userName.userName),{},
      if(a!queryEntity(      
        entity:cons!usernameConstant,
        fetchTotalCount:true,
        query:a!query(
          filter:a!queryFilter(
            field:"username",operator:"=",
            value:ri!username.username
          ),
          pagingInfo:a!pagingInfo(
            startIndex: 1,
            batchSize: 50
          ),
        
          )).totalCount>0,true(),false()))),},

      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=true(),
        saveInto:(
          a!writeToDataStoreEntity(
            dataStoreEntity: cons!usernameConstant,
            valueToStore:(ri!username)
           ),       
           ),
        
        submit: true,
        style: "PRIMARY",
      ),
      a!buttonWidget(
        label: "update",
        disabled: local!valid=false(),
        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
      )
    )
  )
 
)
)

    still not work, buffer memory slow?

Children
  • 0
    Certified Lead Developer
     in reply to immortalvirgil

    This is a lot of hardcoding of values.  Using local variables there are usually much more graceful ways to do this, namely using a technique that I named "variable chaining", i.e. setting up a few different local variables whose values will automatically adjust based on the values of previous ones in an informative way.  The advantage here is you only need to have your text field save into the "username" variable and not have a query hardcoded into its saveInto.  This should be easier to troubleshoot and easier to tweak in the future.  This is my own version of your above form but I believe it captures your general use case:

    a!localVariables(

  local!name,
  
  local!usernameTaken: if(
    isnull(local!name),
    false(),
    rule!yourQueryPersonRule(
      fetchTotalCount: true(),
      pagingInfo: a!pagingInfo(1, 0),
      queryFilters: {
        a!queryFilter(
          field: "username",
          operator: "=",
          value: local!name
        )
      }
    ).totalCount > 0
  ),
  local!disableButton: or(
    local!usernameTaken
  ),

  a!formLayout(
    contents: {
      a!textField(
        label: "Username",
        value: local!name,
        saveInto: {
          local!name
        },
        required: true(),
        validations: {
          if(
            local!usernameTaken,
            "Username Exists",
            null()
          )
        }
      )
    },
    
    buttons: a!buttonLayout(
      primaryButtons: {
        a!buttonWidget(
          label: "SUBMIT",
          value: "submit",
          submit: true(),
          style: "PRIMARY",
          saveInto: {
            /* etc */
          },
          disabled: local!disableButton
        )
      },
      secondaryButtons: {
        a!buttonWidget(
          label: "Cancel",
          submit: true(),
          validate: false()
        )
      }
    )
  )
)

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.