how to distinguish between error and calculated value when calcQuick is used.

[hui zheng]

Hello,

We use Calculate's calcQuick to parse and calculate formulas. What we found is: when something goes wrong with calculation, syncfusion library returns either 1) undefined value, or 2) some human readable string such as "#name", "#value", or 3) some descriptive text such as "wrong number of arguments".  This, however, causes a problem for us. When something is returned from syncfusion calculate library, e.g #value, we cannot tell if it is normal calculation result or it is an error. Ideally  we would like to get an error code (machine code) from formula calculation, so that we can return the calculated result to upper level function for further handling if calculation is ok. If there is an error, we pop up an error dialog window asking user to check error(typo) in supplied formula. Is it possible to achieve this? Can you show us example for error handling?

        var calculator = new CalcQuick();

        calculator.getEngine().setExcelLikeComputations(true); 

        calculator.setAutoCalc(false);

        calculator.setKeyValue("formula", "=" + expression);

        calculator.refreshAllCalculations();

        var val = calculator.getKeyValue("formula");

        if (there_is_an_error) {

          show_error_dialog_window;

        } else { 

         return val;  }

[Mohanraj Gunasekaran]

Hi Hui, 

Thanks for using Syncfusion product. 

We can understand your scenario. By default, we have handled the error in our source and return the value like (#Name, #Value, (etc)) like MS-Excel. So, there is no support to get the errors in formula calculation. 

By default, following error strings are available in ejCalculate, 

• Error Stings (Occurs due to pass the invalid parameters)
• Formula Error Strings (Occurs to give the invalid formulas like missed parenthesis, invalid formula, (etc))

You can get this error string from CalcEngine using formulaErrorStrings property and getErrorStrings method. Please refer to the below code example, 

 

Code example 

var formulaValue = document.getElementById("txtBoxA").value; 

var index = calculator.getEngine().getErrorStrings().indexOf(formulaValue.toString()); 

var index2 = calculator.getEngine().formulaErrorStrings.indexOf(formulaValue.toString()); 

if (index > -1) 

  window.alert("Enter the valid parameters"); 

else if(index2 > -1) 

   window.alert("Enter the valid formula"); 

 

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/CalcQuickDemo-2055860083  

 

Note: 

There is no support to get the reason of the error strings. This errors strings implemented based on the MS-Excel. So please refer the below link to get the definition of error strings. 

Regards, 

Mohanraj G 

 

[hui zheng]

Hello,

Thank you for the example and sample link. However, it is not fully working. For example, when a formula "SQRT(1 + ad2 )" is used, i got the error message "TypeError: args.split is not a function" and this error is not actually caught (index and index2 is -1).

I wonder if calcQuick(ejCalculate) can be configured to throw exception in case of  any error (instead of checking the calculated value against several error lists as you suggested in your example).

Thanks!

Hui

[Mohanraj Gunasekaran]

Hi Hui, 

Sorry for the inconvenience caused. 

We can understand your scenario. As we updated earlier, we don’t have support to get the root cause of the thrown errors (#VALUE!,  #NAME?,..). So that we have provided the customization to get the error messages from (ErrorStrings and FormulaStrings) collection for the errors (#NAME?,#VALUE!,..). If you use “1+AD2” string SQRT formula, “AD2” considered as sheet reference. But in provided sample, we do not have any sheet reference so only this issue occurs as this is invalid use case.  In our previous suggested options (Error Stings and FormulaErrorString) for some valid and defined use cases. But in your scenario, you have tried to calculate the SQRT for mismatched value which is not a valid case.  

Regards, 

Mohanraj G