Backward compatibility and our new formula engine

We work hard to ensure that your apps continue working, even as we add features to Calcapp. With our new formula engine, we have made some minor changes, but they should not affect your existing apps.

We take backwards compatibility seriously. What that means is that we work hard to ensure that your apps continue working, even as we add features to Calcapp.

We realize that the apps you build with Calcapp are often used in business-critical situations, and the last thing you need is to have — say — a production line grind to a halt just because we made some changes. We also realize that many apps are built by staff members who may no longer be with an organization a year or two down the road, and that making changes to existing apps can be difficult for many organizations. In other words, apps should never break.

This release does bring with it a completely revamped formula engine, though, and there are some rare edge cases where formulas now behave differently.

We don’t think that this will affect your apps, though. We have manually tested most public apps on a paid plan with both the old Calcapp version and the current version, and the apps behave identically. We have also used automated tools to scan your formulas for potential problems and have turned up empty-handed.

Had Calcapp been a conventional desktop application, things would have been different. We wouldn’t have had access to your apps and your formulas, and making any backward-incompatible changes — however minor — would have been unthinkable. We’re glad that we do have access to the apps run on our platform, because that gives us the opportunity to make small changes to the way apps work.

In other words, we don’t think that this new release should cause your apps to perform differently. If that isn’t the case, though, and you do experience issues, please contact us immediately. We’ll work with you to resolve any issues — at no additional cost to you.

Backward-incompatible changes

This is a list of changes that result in formulas behaving differently in edge cases. If you’re not particularly interested in formulas, and your apps seem to work just fine, this is probably dry reading.


We pride ourselves on being able to detect lots of formula errors at an early stage. What that means is that you get clear error messages directly in Calcapp Creator, instead of getting puzzling behavior when you run your app.

If you, for instance, try to associate the formula "test""test" with the value of a number field, Calcapp Creator would let you know that it expected a number, but unexpectedly found a text string. (It will then offer to convert your number field to a text field.)

Prior to this release, Calcapp supported only numbers, text strings and logical values. You had to express a date or a time as a number, and that was also true for colors.

The problem with using numbers for things that aren’t strictly speaking numbers is that you lose Calcapp’s ability to flag formula errors early. If you set the value of a date and time field to 42, Calcapp won’t complain, as that’s a valid sequential serial number. Likewise, if you set the background color of a screen to Field1 * 100Field1 * 100, an older version of Calcapp would have accepted the formula.

We have seen lots of formulas likely intended for the value properties of number fields erroneously associated with color properties instead. That leads to colors being off and to formulas carefully written by app authors seemingly not having an effect.

To solve this problem, colors are no longer numbers in Calcapp, they are colors (a separate type). Functions like COLOR now return colors and not numbers, and functions like HUE accept color parameters, not number parameters. Any attempt to associate a formula like Field1 * 100Field1 * 100 with a color property will now be flagged as an error.

If you know what you’re doing, treating a color as a number can be useful. For that reason, there is a new function, TOCOLOR, which allows you to convert a number to a color. Use TONUMBER to convert a color to a number.

Truth be told, we undertook this ambitious change with a view towards making dates and times distinct from numbers too. That would have made it illegal to do something like taking the square root of a date and time value, which is never useful.

However, we came to realize that advanced spreadsheet users (some of whom are our valued customers) are very much familiar with how date and time values work. (The integer part of the number represents the number of days that have elapsed since December 31, 1899.) Some advanced users put numeric values representing various dates in formulas directly, without using functions like DATE and TIME. As a result, we abandoned that part of the plan — date and time values will remain numbers.

So what happens if your perfectly-functional app uses colors in a way that Calcapp now considers invalid? They have automatically been rewritten to use the TOCOLOR and TONUMBER functions, meaning that your apps will continue working exactly as they did before.

Renamed properties

This release brings with it various name changes. Notably, panels are now named screens and list panel options are now named navigators. Those name changes affect the naming of two properties.

Specifically, the form screen and text screen property NextScreenAvailable used to be named NextPanelAvailable. Also, the NavigatorLabelColor property of screens used to be named OptionLabelColor.

These properties are rarely referenced from formulas. However, it does happen. This formula could be associated with the Visible property of a text box to only show it if the next screen is not available:

(Perhaps the text box has information on how to fill out the form so that the user is allowed to progress to the next screen.)

If you referenced any of these two properties from your formulas, these formulas have automatically been rewritten to use the new names.

The == and != operators

Prior to this release, the = and == operators were identical, as were the <> and != operators.

Our new release makes them behave slightly differently, especially in regards to arrays. These two formulas are not equivalent:

{ 1, 2, 3 } = { 1, 3, 2 }{ 1; 2; 3 } = { 1; 3; 2 }
{ 1, 2, 3 } == { 1, 3, 2 }{ 1; 2; 3 } == { 1; 3; 2 }

The first formula returns the array { TRUE, FALSE, FALSE }{ TRUE; FALSE; FALSE }, indicating that the elements appearing in the first position of both arrays are identical, but that the others are not. To determine if all elements match, you need to wrap the result array with the AND function:

AND({ 1, 2, 3 } = { 1, 3, 2 })AND({ 1; 2; 3 } = { 1; 3; 2 })

An easier way to achieve this is to use ==. The == formula above returns FALSE, indicating that the two arrays are not identical. The == operator does an element-for-element comparison of the two arrays and returns TRUE only if all elements match.

From a backward compatibility perspective, this is not relevant, as prior Calcapp releases did not support arrays. However, = and == differ in one respect that is relevant for existing apps.

Specifically, when these operators are used to compare text strings, = is case-insensitive, meaning that “A” and “a” are considered equal. This is the traditional spreadsheet behavior, necessitating the use of the EXACT function to determine if two text strings are truly identical.

== is now case-sensitive, meaning that “A” and “a” are not considered equal. This would break backward compatibility, which is why we change all formulas so that they use = instead of ==, and <> instead of !=.

Some properties no longer return blank values

Color properties have, since their inception, never returned blank values. If you access the background color of a screen, it returns the background color it uses, even if there is no formula associated with the BackgroundColor property of that screen, or if this formula returns a blank value.

The Visible, Enabled and Valid properties now behave in the same way. That means that if a formula references the Enabled property of a field, the value of this property always reflects whether the field is enabled, even if no formula is associated with that property or if that formula returns a blank value.

This change could theoretically break apps, but we have reason to believe that no app on a paid plan is affected.

Decimal separators and the & operator

When the & operator, which joins text strings together, is used to join a text string together with a number, the decimal separator preference of the language the app has been configured to use is now taken into account.

Consider this formula:

"The answer is: " & NumberField1"The answer is: " & NumberField1

Let’s say that the value of NumberField1 is 1.1. If the language of the app is set to US English, the result is “The answer is: 1.1”. If the language of the app is set to French, the result is “The answer is: 1,1”.

Previously, Calcapp would always use a decimal point in this context. With this change — and the ability to use decimal commas in formulas — decimal commas are now used everywhere if that is your preference (provided that your apps use a language that uses a decimal comma).

This change also applies to functions like CONCATENATE and CONCAT. With these functions and & now converting numbers to text and respecting the decimal separator preference of the app, you may no longed need to use the FORMATNUMBER function, if you don’t need the specialized features offered by that function (like support for thousands separators).

Technically, this change could break your apps, if you explicitly access a textual version of a number and expect to find a decimal point, even if the language of the app uses a decimal comma. We have reason to believe that none of our paid apps do this, though, so this change should be safe.

« Feature: No more typos when referencing colors Sample app: Convert spreadsheet tables to XLOOKUP formulas »