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

Spreadsheet data validation with custom formula

4 Answers 303 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Kocsis
Top achievements
Rank 1
Kocsis asked on 14 Jan 2021, 01:45 PM

Hello.

We have to implement a functionality, where the user can create multiple spreadsheet objects via drag and drop.

This part works perfectly, but we got a report regarding Data Validation using custom Formula. 

If the user writes a correct formula, everything is fine, but when it is an invalid one (e.g. >3), we receive a ParseError exception. The message is this: "Parse Error (input >2)".

I tried to validate the formula in the Changing event, but there is no interface to directly call the validation without further implications.

My first try was to create a formula using defineName and if it does throw an exception I stop the process via the preventDefault function. In the case of a ParseError it worked, but it created other exceptions as we have a lot of code attached on nearly all of the events and the defineName function seems to create more events, which will eventually fail (for us at least).

My second try was to create a formula on a cell and as in the solution before, in case of exception, abort the process, otherwise allow it to go on. However this wasn't a good idea because it can corrupt the already existing data.

 

So my question would be, is there some way to validate a custom formula in the Data Validation and stop it from causing further problems? To catch the exception is quite difficult, because of the dynamic allocation of the spreadsheets, or maybe there is a certain point where this can be caught?

 

Thank you for your answers, 

Norbert Kocsis

 

 

4 Answers, 1 is accepted

Sort by
0
Plamen
Telerik team
answered on 18 Jan 2021, 12:28 PM

Hello Norbert,

The exact scenario is not quite clear. Would you please elaborate a little bit the exact steps to replicate the issue - maybe by using a dojo similar to this one or some of our demos so we could review the issue once again and think of a possible solution?

Regards,
Plamen
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/.

0
Kocsis
Top achievements
Rank 1
answered on 19 Jan 2021, 08:11 AM

Hello.

Yes, sorry for the unclear steps. The spreadsheet configuration is not relevant, here if you go to Data->Data Validation->Custom Formula (Ignore blank, Reject input), open the console, than if you enter an invalid formula (<4, >A3 or something like this) and press Apply, in the console you can see an exception (kendo.all.min.js:104 Uncaught init {message: "Parse error (input: >A1)", pos: 1}). And this is our problem, we need to catch somehow this bastard. The configuration does not have an effect, because it's dynamically checked.

 

Furthermore in this example, the configuration contains 1 cell with an incorrect formula. Using the try-catch block, the exception can be caught (message is displayed in the console), nevertheless the rendering is interrupted and the grid part is not displayed, but if the formula would be correct, and a new custom formulation would be introduced, the try-catch block does not catch that exception.

Our problem is that uncatchable exception, is there a way to detect/silence that one?

 

Regards,

Norbert

0
Plamen
Telerik team
answered on 21 Jan 2021, 07:56 AM

Hello,

Thank you for elaborating the issue. We will need some more time to investigate it deeper and will get back to you as soon as we have more information about it.

Regards,
Plamen
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/.

0
Plamen
Telerik team
answered on 22 Jan 2021, 07:45 AM

Hello,

Thank you for your patience and please excuse us for this answer delay once again.

It seems like a bug indeed - I have logged it in our system here . As a small token of gratitude for reporting it I have updated your Telerik points. 

One possible workaround is provided in this dojo - https://dojo.telerik.com/@zdravkov/adEWOlOk

If you have further questions or you observe other issue please let me know.

Regards,
Plamen
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
Kocsis
Top achievements
Rank 1
Answers by
Plamen
Telerik team
Kocsis
Top achievements
Rank 1
Share this question
or