How to apply validation on excel sheet fields

Hi,

I'm using readexcelsheet() so my question is how to apply validation on particular field in excel sheet. Can anyone suggest me how to validate it?

  Discussion posts and replies are publicly visible

Parents
  • Hi  

    Following rules will help you to validate data retrieved from readexcelsheet(). Please note that I have handled most possible cases of excel validation. You might have to add logic to handle your application-specific use cases for each cell data.

    Code for TEST_getExcelValidations() <Master Rule>

    with(
      local!excelData: rule!TEST_readExcelSheet(
        document: ri!document,
        sheetNumber: 0,
        startRow: 1,
        numberOfColumns: 17
      ),
      rule!TEST_checkIfExcelDocumentHasValidData(
        excelData: local!excelData
      )
    )

    Code for TEST_readExcelSheet()

    with(
      local!excelData: readexcelsheet(
        ri!document,
        ri!sheetNumber,
        ri!startRow,
        ri!numberOfColumns
      ),
      if(
        toboolean(
          local!excelData.success
        ) = false(),
        null,
        remove(
          local!excelData.result,
          where(
            a!forEach(
              items: local!excelData.result.values,
              expression: length(
                fv!item
              ) = 0
            )
          )
        )
      )
    )

    Code for TEST_checkIfExcelDocumentHasValidData()

    if(
      isnull(
        ri!excelData
      ),
      {},
      with(
      /*Removing header row from the retrieved data. Write separate rule to validate header row if needed*/
        local!dataFromExcel: index(
          remove(
            ri!excelData,
            1
          ),
          "values",
          null
        ),
        if(
          isnull(
            local!dataFromExcel
          ),
          "All the cells cannot be empty.",
          reject(
            fn!isnull,
            rule!TEST_validateEachRow(
              dataFromExcel: local!dataFromExcel,
              userName: ri!userName
            )
          )
        )
      )
    )

    Code for TEST_validateEachRow()

    a!flatten(
        a!foreach(
            items:ri!dataFromExcel,
            expression:{
            /**Add Validation rules as per your need and call those here**/
            /*I have passed rowNumber, cellName as inputs to construct the error message to user*/
                rule!TEST_validateTextCell(
                    isRequired:true(), 
                    cellName:"First Column Name", 
                    rowNumber:fv!index+1,
                    cellData:index(
                        fv!item,
                        1,/*1 specifies column number to validate*/
                        null
                    )
                ),
                rule!TEST_validateIntegerCell(
                    isRequired:true(), 
                    cellName:"Second Column Name", 
                    rowNumber:fv!index+1,
                    cellData:index(
                        fv!item,
                        2,/*Passing 2nd column data to validate*/
                        null
                    )
                )
            }
        )
    )

    Logic behind TEST_validateTextCell() or TEST_validateIntegerCell()

    if(
        ri!isRequired,
        if(
            isnull(
                ri!cellData
            ),
            "Row Number: "&ri!rowNumber&" has no data for "&ri!columnName&" column",
            if(
                ri!cellData<=0,
                "Row Number: "&ri!rowNumber&" has 0 or lesser than 0 for "&ri!columnName&" column. Only positive values allowed.",
                {} /*I have validated for positive number*/
            )
        )
    )

  • How to display validation message at the time of uploading excel document?
  • Hi,

    Once the excel is uploaded and form been submitted, call the main rule provided above in a script task and store the o/p in a variable ( For Ex: validations[List of Text]). Next to script task have an XOR and check whether the variable is empty. If empty, then proceed with other operations. If there are some errors, then re-direct to UIT. In the interface, add a rule input for validations and insert below field

     

    a!richTextDisplayField(
      showWhen: not(
        isnull(
          ri!validations
        )
      ),
      value: a!richTextBulletedList(
        items: a!forEach(
          items: ri!validations,
          expression: a!richTextListItem(
            text: a!richTextItem(
              text: fv!item,
              color: "NEGATIVE"
            )
          )
        )
      )
    )

    Hope this helps!!

  • Hi,

    you can perform a certain amount of validation on file properties within the upload interface using fv!file.(properties). Refer here for some examples. For validation on data, you can perform the validation on file submission and display them back on the same interface with relevant validation messages, passing them as an input to the interface,

    Regards,

    Thoufiq.

  • Thank you Thasneem Nisa, It's working fine.
Reply Children
No Data