Bug report: Formulas now work better

Named values can reference anything in an app and are a great way to cut down on formula repetition. They now work properly even when pushed to the maximum. We have fixed other issues as well.

On occasion, we build apps for clients using Calcapp. This is often a great way for a client to get started, by having us convert a spreadsheet and demonstrating best practices in the process.

(Contact us if you’d like to learn more about our professional services.)

It’s also great for us, as we get to spend some quality time using Calcapp, and not just building and maintaining it. During the course of that work, we sometimes find problems with Calcapp itself that we then track down and fix.

This post details what we found and fixed this time around.

The Item formula parameter

In August last year, we introduced support for accessing the current item from a formula simply by writing Item.

Let’s say that you have this Valid formula connected to NumberField1:

NumberField1 > MaximumReadingNumberField1 > MaximumReading

Now, let’s assume that you have a hundred such number fields, which all should use similar formulas connected to their Valid properties (NumberField2 > MaximumReadingNumberField2 > MaximumReading, etc).

That quickly becomes hard to maintain and repetitive, as changing the formula now involves changing one hundred of them. Wouldn’t it be great if they all could use the same formula? Had that been the case, you could actually edit them all at the same time.

This is what Item facilitates. It’s a stand-in for the item itself, in this case NumberField1 and others. So you can use this formula instead:

Item > MaximumReadingItem > MaximumReading

While the formula above worked fine prior to this fix, there are other valid formulas that produced error messages until we released the fix today.

Specifically, consider a number field. There are properties that are specific to number fields (like MaximumValue) and there are properties that are supported by all kinds of fields, including text fields and date and time fields (like (Visible).

Associating a formula with a property specific to a number field would enable said formula to access an Item value offering the full set of properties supported by number fields. However, a formula associated with a property shared between all kinds of fields would have access to an Item value only offering access to the shared properties, not the ones specific to number fields.

In other words, formulas associated with the Visible property of a number field could not access properties specific to number fields, like MaximumValue.

That was an oversight on our part, and has now been fixed.

Named values

Named values have also been available since August last year. They can be used to represent any value a formula can represent. The new data editor uses them to great effect to represent tabular data.

However, named values can truly represent anything, and when we worked on our last client project we wanted to make full use of them. The only problem was that they did not appear to work right.

Here’s an app that demonstrates the problem, being edited in Calcapp Creator:

An app failing to calculate property in Calcapp Creator

Users enter miles traveled in the number fields of the app. Only number fields related to the selected state are shown, which is accomplished using a text drop-down field and the Visible property of the form groups housing the number fields.

To cut down on repetition, we use a number of named values to represent various parts of the app. First, there’s the Legs named value, which allows us to reference the relevant number fields from elsewhere in the app.

Here’s its formula:

IF(Wisconsin.Visible, Wisconsin.NumberFields, Missouri.Visible, Missouri.NumberFields)IF(Wisconsin,Visible; Wisconsin,NumberFields; Missouri,Visible; Missouri,NumberFields)

In other words, if the Wisconsin form group is visible, because the user has selected Wisconsin from the text drop-down field, Legs references the number fields of the Wisconsin form group. Conversely, Legs references the number fields of the Missouri form group if Missouri has been selected from the text drop-down field.

Next, let’s say that the only miles traveled that are deemed eligible are those that are 100 miles or less. To accomplish that, each number field uses a MaximumValue property which is set to 100.

The final named value is named EligibleMiles and includes only those number fields whose values are 100 or less. Here’s its formula:

FILTER(Legs, Legs.Value.Valid)FILTER(Legs; Legs,Value,Valid)

It filters the number fields identified by the Legs named value by only including those that are valid, that is, have values that are 100 or less.

(You might be tempted to write Legs.ValidLegs,Valid, but that would reference a Valid property belonging directly to the Legs named value. There is no such property — Valid is a property of fields. As such, the Value property of the named value must be accessed explicitly in the formula. As it references number fields, .Valid,Valid can be written directly following .Value,Value in the formula.)

Finally, the last number field displays the sum total of all eligible miles that have been entered, using this formula:

SUM(EligibleLegs)SUM(EligibleLegs)

The only problem with this app? Before our update this morning, it just plain didn’t work. As you can see in the screenshot above, the sum of all eligible legs was always zero.

There were other problems as well:

An app displaying invalid errors edited in Calcapp Creator

The app above contains only valid formulas, and yet, many of them are flagged as invalid.

The root cause was that Calcapp did not handle multiple levels of indirection well with named values. We have spent weeks completely overhauling this part of Calcapp and are happy to report that everything now works properly.

While the example above is admittedly somewhat contrived, we keep encountering situations where named values make for shorter, easier-to-read formulas. Even if you push them to the maximum, they should now work properly.

Other fixes

We also encountered a few other issues. The parameter popup, that displays information on the function parameter you’re editing, would sometimes fail to close when you moved away from the formula field.

Also, you have been able to determine the next screen of list screen navigators since our August release. Unfortunately, though, when moving between navigators, the next screen in the inspector would sometimes fail to update.

As of today, both issues have been fixed.

« Bug report: A grab bag of fixes Tip: Determine the number of toggled switch fields »