Custom Validation

11 posts, 1 answers
  1. Christy
    Christy avatar
    38 posts
    Member since:
    May 2016

    Posted 22 Jul Link to this post

    Is there a way to use validation on spreadsheet cells that includes a regex?  I have two validators I need to set up.  The first is just to make sure it is an integer, and the second needs a string with a max length on it.  For the integer, I started with dataType of "number", but it required a comparer.  If I included a comparerType of "greaterThan", I needed a from.  

    Also, is there a place where we can find the allowed fields for comparerType along with examples?

    Thanks!

  2. Dimiter Topalov
    Admin
    Dimiter Topalov avatar
    356 posts

    Posted 26 Jul Link to this post

    Hello Christy,

    Regex expressions are not supported in the Kendo UI Spreadsheet range validation, but you can customize the validation rules, as shown in the following how-to article from our documentation:

    http://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/how-to/validation-to-column

    You can also check out the following simple dojo example, demonstrating how to set the following rule to a cell - value must be a number with length at least 2, and less than 10:

    http://dojo.telerik.com/AyUtu

    I hope this helps.

    Regards,
    Dimiter Topalov
    Telerik by Progress
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  3. Kendo UI is VS 2017 Ready
  4. Christy
    Christy avatar
    38 posts
    Member since:
    May 2016

    Posted 27 Jul in reply to Dimiter Topalov Link to this post

    Is there a way to get that to work for a range of cells?  For example, I have a spreadsheet with a column that is numeric, except for cell A1 because that is the header.  I was trying to do the following:

    var ItemValidator = {
        dataType: "custom",
        from: "AND(LEN(A2)<56)",
        type: "reject",
        allowNulls: false,
        titleTemplate: "Invalid Item",
        messageTemplate: "Values must 55 characters or less"
    };
     
    for (var i = 0; i < spreadsheet._workbook._sheets.length; i++) {
        var sheet = spreadsheet.sheetByIndex(i);
        var rangeItem = sheet.range("A2:A1048576"); 
        rangeItem.validation(itemValidator);
    }

    If there is a way to do that on a column (minus the first cell), this would work great.

    Thanks!

  5. Christy
    Christy avatar
    38 posts
    Member since:
    May 2016

    Posted 27 Jul in reply to Christy Link to this post

    It appears that doing it as I did will work provided that there is only one sheet in the spreadsheet.  However, I am getting an "Uncaught TypeError: Cannot read property 'toLowerCase' of undefined" when I try to add the validation to the rangeItem of the second sheet.  

    I can get around this if I just add the validation to the active sheet, but then only one sheet will be validated.  If there is a way to trap the onActivate or onSelect of the spreadsheet's tabstrip, that would work too.

    Thanks!

  6. Christy
    Christy avatar
    38 posts
    Member since:
    May 2016

    Posted 27 Jul in reply to Christy Link to this post

    This has problems if I have multiple sheets in the spreadsheet.  I end up with an "Uncaught TypeError: Cannot read property 'toLowerCase' of undefined" when I try to add the validation to the rangeItem of the second sheet.  I can get around this by only validating the active sheet, but then I still need to validate the other sheets.  Is there a way to hook into the onActivate or onSelect of the spreadsheet's tabstrip?

    Thanks!

  7. Answer
    Dimiter Topalov
    Admin
    Dimiter Topalov avatar
    356 posts

    Posted 29 Jul Link to this post

    Hello Christy,

    There is no built-in special event that fires when the active sheet is changed, but you can store the current active sheet in a variable, and handle the render event where you can check whether the stored sheet is the same as the active one - if not, the active sheet is changed:

    http://dojo.telerik.com/AyUtu/2

    I hope this helps.

    Regards,
    Dimiter Topalov
    Telerik by Progress
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  8. Aliko
    Aliko avatar
    1 posts
    Member since:
    Jan 2016

    Posted 15 Sep in reply to Christy Link to this post

    Hi Cristy,

    I am facing the same problem where validation needs to be applied to multiple sheets. Were you able to figure out the solution?

    Thanks!

  9. Christy
    Christy avatar
    38 posts
    Member since:
    May 2016

    Posted 15 Sep in reply to Aliko Link to this post

    Hi Aliko,

    Unfortunately, I was unable to figure out how to get it to validate anything but the last sheet in the file.  When the file is loaded, the last sheet is automatically the active one.  I tried storing the current active sheet in a variable as recommended, but the validation method failed on rangeItem.validation(itemValidator).  The code I ended up with is as follows:

    var ItemValidator = {
        dataType: "custom",
        from: "AND(LEN(A2)<56)",
        type: "reject",
        allowNulls: false,
        titleTemplate: "Invalid Item",
        messageTemplate: "Values must 55 characters or less"
    };
      
    var sheet = spreadsheet.activeSheet(i);
    var sheetRows = sheet._rows._count;
    var rangeItem = sheet.range("A2:A" + sheetRows);
    rangeItem.validation(itemValidator);

    However, it only works on the last sheet.  It definitely would be nice if there is a way to do this.

  10. Dimiter Topalov
    Admin
    Dimiter Topalov avatar
    356 posts

    Posted 19 Sep Link to this post

    Hello,

    Using a forEach​ to loop through the sheets, setting the respective ranges, and validating them seems to be working on our end:

    http://dojo.telerik.com/EKosa

    Please note that the activeSheet() method is not used in this approach.

    Let us know whether this is the desired functionality.

    Regards,
    Dimiter Topalov
    Telerik by Progress
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  11. Christy
    Christy avatar
    38 posts
    Member since:
    May 2016

    Posted 21 Sep in reply to Dimiter Topalov Link to this post

    Unfortunately, that still gives me the same error of "Uncaught TypeError: Cannot read property 'toLowerCase' of undefined" when it starts to validate the second sheet.  It crashes right on the rangeItem.validation(itemValidator); the second time through the loop.  

    One thing that may help is the result I have from console.log(rangeItem.validation());   For the first sheet, the result shows values for comparerType, tooltipMessageTemplate, and tooltipTitleTemplate of undefined.  For to, it is listed as "undefined" - the string.  Is there something I should put in the "to" property of the itemValidator?

  12. Dimiter Topalov
    Admin
    Dimiter Topalov avatar
    356 posts

    Posted 26 Sep Link to this post

    Hi Christy,

    Can you please send us an isolated runnable project, similar to the working example, provided in my previous post, where the issue can be observed, so we can inspect it further, and determine what might be causing it? Thank you in advance.

    Regards,
    Dimiter Topalov
    Telerik by Progress
     
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
     
Back to Top
Kendo UI is VS 2017 Ready