Letter: Resetting specific fields and improving reports

July 27, 2017

Today, we heard from Dave, one of our most prolific users. He has created a large app used by British truckers. They are expected to report details of their shifts using the app, which are then sent through email.

Dave asked if the fields reset by a reset button could be customized and also asked if there was any way to cut down on the number of fields included in reports. He received this reply from our co-founder David:

Glad to hear you like the latest update. You can’t currently customize what fields are reset by a reset button, beyond being able to specify whether all fields or just the fields of the current panel should be reset and whether persistent fields should be excluded. With that said, though, we added the ability not to reset persistent fields because we were faced with the exact same scenario you describe, that of users entering valuable information that should not only persist beyond the actions of a reset button, but also persist beyond the current app session, that is, should be restored the next time the app is opened. Have you considered simply marking your rate fields persistent?

We do want to enable more fine-granular control over which fields are reset, though. A user recently suggested that we add an option enabling only fields belonging to the same group as the button to be reset, and we’re considering that. However, we also want to enable you to specify the exact fields that should be reset through a formula. That would enable you to take values entered by the user into account when determining which fields to reset. One button could reset only fields holding negative values, for instance, and another could base its decision on the value of a drop-down field.

It will be a while before this more comprehensive solution will see the light of day, though. We essentially need to add two features to Calcapp formulas in order to make it happen:

Referencing individual fields

First, we need to enable you to reference individual fields. When you type Field1 in a formula, you’re actually referencing the value of Field1, and not the actual field. While it would have been logical for you to be able to reference a field by typing Field1, you would then have to reference its value by typing something like Field1.Value, which would have been awkward. We’re considering adding a property to fields (and other variables like buttons, groups, text boxes and list panel options) named “Id”, enabling you to reference a field by typing Field1.Id.

While we think that’s the course of action we’ll take, it will complicate our “type system.” There are three “types” in Calcapp at present, meaning that a value can either be a number, a logical value (TRUE or FALSE) or a text string. If you write something like IF("a text string", TRUE, FALSE) you will get an error in Calcapp Creator, because the first parameter to the IF formula function must be a logical value and not a text string. When you’re able to reference things like fields and buttons from formulas, we need to add these “types” as well.

(We’re actually anxious to enable you to reference fields directly for other reasons than being able to specify what fields should be reset when a reset button is pressed. Today, you can type Field1.FormattedValue to access the value of a field as a formatted text string, but we’d like to add a function which would format any value using the formatting rules defined for a certain field. Something like FORMAT(42, Field1.Id) would then format 42 using the formatting rules for Field1, and return something like “42.00” if at least two decimal places should be used. We think that would be a great alternative to the poorly-understood format codes of Microsoft Excel, which are accepted by the TEXT formula function.)

Referencing several fields

Second, in order to use a formula to determine which fields to reset, you also need to be able to refer to multiple fields from a formula. This isn’t supported at present, so we need to add this capability to Calcapp formulas.

Excel supports something called “arrays,” which you can think of as lists of things. To reference a list of numbers, you write something like ={23, 42} in a formula (notice the curly braces). Unless we see a very good reason to diverge from Microsoft’s formula syntax, we liberally borrow from it when extending Calcapp’s formula syntax, and chances are good that we’ll borrow the syntax for “array constants” as well.

Adding support for arrays will further complicate our type system, though, as you’ll now be able to reference not just, say, numbers and fields, but also arrays of numbers and arrays of fields. What you want for a formula determining what fields to reset is for it to only accept “arrays of fields,” meaning that an array of numbers will be rejected as will a single logical value, for instance.

Putting it all together, once we add support for referencing individual fields and for referencing several of them, we’ll be able to introduce a new property of reset buttons (named “IncludedFields”, perhaps), and you’ll be able to associate a formula like the following with it:

{ Field1.Id, Field2.Id }

Obviously, it would be nice with a shorthand for referencing all fields of a specific panel or group, so you don’t have to enumerate all fields individually. Also, we should probably add a more user-friendly interface as well, so there’s a graphical means of determining what fields to reset. That is probably what you’ll use if you don’t need to do anything overly complicated, like determining what fields to include using the IF formula function.

As you can see, adding this feature will require a lot of time, and there are other features we think are more important at this time.

On reporting

When we added support for reporting, we didn’t know to what extent it would be used. I’m happy to say that reporting is an integral part of a great number of apps built with Calcapp today, so improving reporting is something that we prioritize highly. Our number one priority is to enable you to customize not only what fields are included in reports but also their appearance (including custom images). There will likely be a stand-alone report designer living side-by-side with the app designer in the future, where you’ll be able to design reports like you would in a word processor.

We’re thinking of enabling you to intermingle Calcapp formulas with text in not only reports, but also in text boxes. (If you’re familiar with the Mail Merge process in Microsoft Word, this would be superficially similar.) You would then be able to design a report as if you were writing a document in a word processor and write things like “Name: {{NameField}}” to have the value of field named NameField inserted at that spot or use any valid Calcapp formula. That should give you a lot of flexibility.

Obviously, that’s a pretty big change and will also take time to come to fruition. We’re actively thinking of both issues you brought up, though, and both will be addressed in the future.