Uniqueness data validation

Uniqueness data validation

Posted by: bsnthr on 4 July 2022, 8:14 pm EST

• 

  bsnthr

  • Post Options:
  • Link
    Copy

  Posted 4 July 2022, 8:14 pm EST

  Hello,

  I have a table that has several columns. Lets assume the table consists of addresses with name, zip and city.

  One of our requirements is not to have a duplicate entry inside the whole table (duplicate for combination of name, zip and city).

  How can this be achieved with SpreadJS? With the validators that are offered I was not able to achieve this. I am missing here also the possiblity to iterate over the whole data in the table.

  What I’d want is to have the possibility to validate based on a function that I am able to define, that either returns true or false as a validity check. This way I could then also hook into the ValidationError event via

  sheet.bind(GC.Spread.Sheets.Events.ValidationError, function(e, args) { // do some thing. });
  

  I tried searching here in the forum, and another user had a similar problem described here:
  
  https://www.grapecity.com/forums/spreadjs/custom-cell-validator-spre

  The proposed solution to use addCustomFunction does not make sense to me here, as this again, does not provide to iterate over the whole table and find duplicate entries accross several columns. The user itself said then that he used the editEvent, but here I am then missing the option to set the cell invalid programatically. How could this be done?

  Another thing I tried is to use e.g. CellChange event and do my validation there (where I am able to iterate over the whole table and find duplicates). But here I am missing now the possibility to set the cell to invalid via code (without using any DataValidation offered by the API (GC.Spread.Sheets.DataValidation).
  
  But then I’ll loose all the functionality that the DataValidation offers out of the box for showing users additional information (inputMessage, inputTitle, errorMessage, errorTitle and so on).

  So to summarize:

  1. how to solve the uniqueness validation across the whole table?
  2. how to set the cell valid/invalid programatically?
  3. how to use a custom data validation without loosing the frameworks functionality of setting e.g. error messages and error titles?

  Any help is appreciated.

  Thanks in advance.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 6 July 2022, 8:45 pm EST

  Hi,

  1. You can create your own custom function that checks if the rows of the table are equal. You need to iterate through the values of rows to check if two rows are not equal.

  2. You cannot set the valid/invalid state of a cell programmatically. You can define the available data validators/ create custom validators for determining the state of a cell.

  3. You can use the createFormulaValidator method to create a validator based on the formula. You can pass custom function inside the createFormulaValidator that returns true/false based on the conditions.

  For example, you can refer to the following sample that demonstrates the same: https://jscodemine.grapecity.com/share/-J1zfsJBokOpWdXRJo7SFw/

  Inside the TableValidator’s evaluate method, you can check for custom criteria. For unique rows in the table, you need to get the rows data and check if the row data are equal.

  API Docs:

  createFormulaValidator method: https://www.grapecity.com/spreadjs/docs/latest/online/SpreadJS~GC.Spread.Sheets.DataValidation~createFormulaValidator.html

  addCustomFunction method:
  
  https://www.grapecity.com/spreadjs/docs/latest/online/SpreadJS~GC.Spread.Sheets.Worksheet~addCustomFunction.html

  Regards
  
  Ankit

• 

  bsnthr

  • Post Options:
  • Link
    Copy

  Posted 1 August 2022, 5:54 pm EST

  Thank you very much Ankit. I was now able to iterate through the whole table and do my custom validation.

  One thing though - just with the documentation I would not have been able to set this up myself. Or can you point me to the correct area in the documentation where this could be found (like using prototype.evaluate and such things) and also how to setup the custom validator like this (specifically I mean where its documented how to provide a string with a function call)

  let dv = GC.Spread.Sheets.DataValidation.createFormulaValidator("TableValidator()");
  

  Thanks again. Have a good day.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 2 August 2022, 2:37 pm EST

  Hi,

  You can refer to the following documentation and demo on Custom Functions and Data Validations:

  Create Custom Formulas: https://www.grapecity.com/spreadjs/docs/latest/online/formulascustom.html

  Custom Functions Demo: https://www.grapecity.com/spreadjs/demos/features/calculation/custom-functions/purejs

  Function Class: https://www.grapecity.com/spreadjs/docs/latest/online/SpreadJS~GC.Spread.CalcEngine.Functions.Function.html

  Data Validation: https://www.grapecity.com/spreadjs/docs/latest/online/datavalidate.html

  Data Validation Demo: https://www.grapecity.com/spreadjs/demos/features/cells/data-validation/basic-data-validator/purejs

  Custom Validation: https://www.grapecity.com/spreadjs/demos/features/cells/data-validation/custom-data-validator#demo_source_name

  createFormulaValidator takes the formula condition. It can be a simple condition like “A1>0” or it can be function/customFunction returning true/false. You can refer to the above mentioned references and please let us know if you face any issues. We would be happy to help you.

  Regards
  
  Ankit