By default, Excel will not prevent you from changing the value of any cell in the workbook, whether they are meant as inputs or outputs (i.e. containing formulas). Even though some applications have a linear flow where formula override is not helpful, the ability to change the value of a formula can also be useful in “guiding” the users by suggesting a possible solution, while still allowing them to overwrite the formula-generated values.
Introduced in version 6.18, the Formula Override feature – as the name suggests – aims to do just this, by allowing the users to override the value of a formula-generated output control during runtime. User interface controls with this feature enabled will look like normal input fields, but will display the calculated results as users enter data into other fields. This feature paves the way for many possibilities such as a “select all” button, or assigning default values to controls dynamically.
On the Designer, the Formula Override feature is enabled on a control-by-control basis. You can activate it by selecting the control in the User Interface Designer, and enabling this property. Currently, all input controls (including Input Grids, excluding File Uploads) support this feature.
If a control with Formula Override enabled is changed by the user during runtime, the control will display a “not equals sign” right next to it, indicating that the result shown on that control is no longer generated by the underlying formulas.
Clicking this button reverts the user override, and the application continues to use the formula-generated values for that cell instead.
Note that the controls with manual override only affect the active user’s session, meaning that these changes do not change anything for any other user in accessing the web application. In applications with database tables defined and save enabled, if the user manually changes the value of a Formula Override enabled control, the user’s value will be saved instead.
Let’s take a few examples to take a closer look at how the Formula Override feature works, and how it can help enhance the user experience. A very common example where this feature shines is address fields in order placement forms. In some cases, the billing and shipping addresses may not be the same, hence businesses typically give users the option to fill out two separate address fields for an order. Such applications usually also include a “billing address same as shipping address” checkbox that copies the data from one section to the other.
To do this in your SpreadsheetWEB applications, you can insert formulas for all controls in the billing section, so that the data is automatically copied over from the shipping address. You can then enable Formula Override for all controls in the billing section to allow the end users to modify this data if this address is different.
This way, you are making life easier for a lot of users that use the same address for billing and shipping, while giving other users the ability to change their information.
Let’s now take another example and consider an application where lending the end users a baseline they can start off of can be helpful. Here we have a financial model to demonstrate this scenario. This is a calculator that helps startups estimate their initial costs and breakeven point.
The users can enter their estimate for annual sales growth by entering it as a percentage into that field, which is reflected on the grid below as an annual increase in the unit sales. This number applies to consecutive years and is a good starting point. Enabling Formula Override in this input grid, on the other hand, will give users the option to keep these values, or manually adjust them for any number of years.
Finally, let’s take a look at a different application of the Formula Override feature. This property can also be used to create a “Select All” button, on which a group of checkbox controls can be dependent. This way, the checkbox fields would actually be targeting the Select All button, but users would be able to override their value to select or deselect all options during application runtime.
