This is a migrated thread and some comments may be shown as answers.

Custom Validation

12 Answers 665 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Christy
Top achievements
Rank 1
Christy asked on 22 Jul 2016, 06:06 PM

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!

12 Answers, 1 is accepted

Sort by
0
Dimiter Topalov
Telerik team
answered on 26 Jul 2016, 02:06 PM
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.
0
Christy
Top achievements
Rank 1
answered on 27 Jul 2016, 07:13 PM

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!

0
Christy
Top achievements
Rank 1
answered on 27 Jul 2016, 10:17 PM

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!

0
Christy
Top achievements
Rank 1
answered on 27 Jul 2016, 10:19 PM

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!

0
Accepted
Dimiter Topalov
Telerik team
answered on 29 Jul 2016, 02:41 PM
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.
0
Aliko
Top achievements
Rank 1
answered on 15 Sep 2016, 06:24 PM

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!

0
Christy
Top achievements
Rank 1
answered on 15 Sep 2016, 06:36 PM

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.

0
Dimiter Topalov
Telerik team
answered on 19 Sep 2016, 07:48 AM
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.
0
Christy
Top achievements
Rank 1
answered on 21 Sep 2016, 07:29 PM

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?

0
Dimiter Topalov
Telerik team
answered on 26 Sep 2016, 07:26 AM
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.
 
0
murali krishna
Top achievements
Rank 1
answered on 25 Nov 2020, 03:07 AM

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)

0
Dimitar
Telerik team
answered on 26 Nov 2020, 01:57 PM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Tags
Spreadsheet
Asked by
Christy
Top achievements
Rank 1
Answers by
Dimiter Topalov
Telerik team
Christy
Top achievements
Rank 1
Aliko
Top achievements
Rank 1
murali krishna
Top achievements
Rank 1
Dimitar
Telerik team
Share this question
or