Uniqueness data validation

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

  • Posted 4 July 2022, 8:14 pm EST


    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:

    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.
  • Replied 6 July 2022, 8:45 pm EST


    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:

  • Replied 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.
  • Replied 2 August 2022, 2:37 pm EST


    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.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels