Hello is there any way I can validate my formula before attaching it on a grids column?
I want to prevent unhandled for CalcManager situations like "Integer + String" or "String + String" instead of using concatenation.
I tried to use CompileFormula but I caught only SyntaxErrors.
Any Idea?
Hello Adamantidis,
Thank you for contacting Infragistics. The best way to validate a formula is to use one of the events exposed on the UltraCalcManager. Which ones have to tried?
FormatValue may be very helpful since the CalcManager can't naturally anticipate invalid formulas until after you supply your values. You can be use this event to convert values based on the expected type the user should enter and avoid exceptions ever being made.
FormatValue: Fired before a CalcSettings sets a value on a control. Note, this is used for type checking before a formula results in an error when situations like mixing matching strings.
eg
private void ultraCalcManager1_FormatValue(object sender, Infragistics.Win.UltraWinCalcManager.FormatValueEventArgs e) { // FormatValue gets fired on all controls that are target of formula evaluations. if ( e.Control == this.textBox1 ) { if ( e.CalcValue.IsError ) { // There was an error in formula evaluation. e.Value = e.CalcValue.ToErrorValue( ).Message; } else if ( e.CalcValue.IsDBNull || e.CalcValue.IsNull ) { // Result is DBNull or Null. e.Value = "(NULL)"; } else { double doubleVal; if ( e.CalcValue.ToDouble( out doubleVal ) ) { // ToDouble returns true if the value was a double. e.Value = ((decimal)doubleVal).ToString( "c" ); } else { // Otherwise convert the value to a string using current culture. e.Value = e.CalcValue.ToString( System.Globalization.CultureInfo.CurrentCulture ); } } } }
Here are some other events that will help you as well.
FormulaCalculationError: Fired any time the attempt to calculate a formula results in an error. FormulaReferenceError: Fired when calculating the formula involves referencing an object that could not be found. FormulaSyntaxError: Fired when parsing the formula expression fails because the expression contains typographical errors or uses operators incorrectly.
There is also a topic about Validating Character Entries in the UltraGrid, part of our Data Validation section.
Let me know if you have any questions.
Hello,
Thanks for your response, however your answer does not help me.
First of all I am working on Infragistics Ultragrid now, so I am not able to use calcSettings.
I override and tried to catch all the bellows event (FormulaCalculationError, FormulaReferenceError, FormulaSyntaxError).
Only FormulaSyntaxError were triggered correctly.
All the other were not being triggered and when I was applying the formula instead of result I had columns with "#XXX".
Hi Adamantidis,
There really isn't any way to do what you are asking for here. When dealing with formulas there are basically two steps. The first is compiling the formula. That's where the CalcManager will detect things like syntax errors. But in the case of a formula like:
"a" + "a"
there is no syntax error. The syntax here is correct, it's just that the arguments are of the wrong type. That type of error doesn't get detected until the second phase, which is calculation.
So this returns an UltraCalcValueError as the result of the formula.
Of course, if you are writing formulas in your application, this shouldn't be a problem, because you could detect this and fix it, so presumably, you are allowing your users to create custom formulas and plug them into the grid? In that case, there's really only two things I can think of that might help:
1) Write your own custom functions that are more forgiving of errors. There's a sample (UserDefinedLibrary) which demonstrates how to do this. You can add your own functions to the calc engine or override existing ones (by registering a custom function with the same name as an existing one). You could then handle the code that performs the calculation yourself and so you could override the addition function and have it check for strings and then call the ConCat function if any of the arguments are strings. Of course, you would have to do this for every possible function and depending on how far you wanted to go with it, it could be a pretty massive undertaking.
2) Educate your users about the difference between '+' and Concat functions. This might not be as bad as it seems, since the CalcManager is based on Excel and Excel does the same thing - it displays an error if you try to use the '+' operator on two strings.
I took another look, just to see if I could provide you with a sample of overriding the "+" operator, but it looks like I was mistaken. You can override the named functions, but apparently, it is not possible to override the unary operators like "+'. Sorry for the confusion.
Hello Mike,
Thanks for your response.
So there is now way of preventing errors right now?
Can you share the link of UserDefinedLibrary sample?
I cannot find it.
Thanks