Custom Validation

[Christy]

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!

[Dimiter Topalov]

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

[Christy]

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!

[Christy]

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!

[Christy]

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!

[Dimiter Topalov]

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

[Aliko]

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!

[Christy]

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.

[Dimiter Topalov]

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

[Christy]

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?

[Dimiter Topalov]

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

 

 

[murali krishna]

Hi I am trying to add list values for different columns range. I am facing below issue. Same is working fine with adding validator for one cell. Thanks in adavance.

Code:

  var ItemValidator1 = {
            dataType: "list",
            showButton: true,
            comparerType: "list",
            from: '{ "A333/API 5L", "A350", "A53/A106/API SL" ,"A563","A671"}',
            allowNulls: true,
            type: "reject"
        };
        var ItemValidator2 = {
             dataType: "list",
            showButton: true,
            comparerType: "list",
            from: '{ "1/8", "1/4", "3/8" ,"1/2","3/4","1","1.1/4"}',
            allowNulls: true,
            type: "reject"
        };
        var sheet = spread.activeSheet();
        var sheetRows = sheet._rows._count;
        var rangeItem = sheet.range("A2:A" + sheetRows);
        rangeItem.validation(ItemValidator1);

        var rangeItem1 = sheet.range("B2:B" + sheetRows);
        rangeItem1.validation(ItemValidator2);

 

Issue:

kendo.all.js:5315 Uncaught TypeError: Cannot read property 'indexOf' of undefined
    at r.set (kendo.all.js:5315)
    at r.set (kendo.all.js:5506)
    at kendo.all.js:161694
    at init.forEach (kendo.all.js:135954)
    at init._sheetChange (kendo.all.js:161682)
    at init.trigger (kendo.all.js:164)
    at init.triggerChange (kendo.all.js:140862)
    at init._set (kendo.all.js:137980)
    at init._property (kendo.all.js:138002)
    at init.validation (kendo.all.js:138138)

[Dimitar]

Hi,

Testing the given snippet, it works as expected on my side - Dojo. There are two different lists for A column and B column.

If the issue is reproducible with it on your side, it would be helpful to provide steps for its reproduction.

Regards,
Dimitar
Progress Telerik