Flexible and Functional Formula Fields

By Jennifer Neighbors, Senior Consultant

The type of field I use the most has got to be the formula field. Formula fields are useful for a variety of purposes on table forms and their ability to trigger code makes them doubly useful.

Here is an example of a form with some formula fields. Can you tell which fields are formula fields?

There are eighteen field objects on this form. If you found sixteen formula fields and two fields that are not formula fields, you are correct. The nonformula fields are the choice field “LOCATION” and the date field “ESTABLISHED DATE”.

The field in the upper left corner contains an image. The image is stored in a table named Utils and the image is an image type field there named “Whale”. The formula to retrieve and insert that image in this form is:

							
					select Utils.Whale				
			

The form header field next to it is a formula that contains HTML and uses some internal style settings for the font size and color. The HTML code is this:

							
					html("<b>BLUE WHALE</b> <br>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspCanteens</br>")				
			

Here is the choice field called LOCATION:

The field named LOCATION NO refers to the choice field named LOCATION next to it. The formula retrieves the number of the choice, like this:

							
					number(LOCATION)				
			

The EMPLOYEE COUNT field is the count of employee records in a table named EMPLOYEES. To insert the count of employees, the formula is:

							
					count(select EMPLOYEES) [LOCATION = 2]				
			

To compute the number of years in business, the formula is:

							
					age('ESTABLISHED DATE')				
			

What about the header in the middle of the form that says “2020 GROSS REVENUE”? That, too is formula – a formula that just inserts text. A text-only formula places the text in double quotation marks and that’s all. The formula looks like this:

							
					“2020 GROSS REVENUE”				
			

The remaining fields on the form are formulas, too. Some are text only and these make for great field labels when you need greater emphasis that the usual field label provides. In this case, you will need to hide the field labels of the fields that correspond to your formula field labels.

As illustrated in these examples, formula fields can do a variety of interesting things on your forms. They can display images. They can create headers and labels. They can display calculations. Remember, too, that you can use them to trigger code. This shows the resulting form if the user clicks on the Total Gross Revenue amount.

The detail lines have disappeared and only the total is in view. If the user clicks again on the Total Gross Revenue, the detail lines appear again. This sort of “toggle” is created by first making a hidden Yes/No field and then turning it on and off using code.

In this instance my hidden field is called “Show Detail”. I made it a required field with a default value of “No”. The code I used in the ON CLICK function of the Total Gross Revenue formula field is this:

							
					if ‘Show Detail’ then
    ‘Show Detail’ := “No”
Else
    ‘Show Detail’ := “Yes”
End				
			
This bit of code forces the state of “Show Detail” to switch between true and false each time the field is clicked. The fields that toggle in and out of view have this line of code in their Display Only, If function:
							
					'Show Detail'				
			
By mastering these variations of the versatile formula field, you can go far toward gaining a full understanding of how to use fields on forms.

Jennifer welcomes feedback and can be reached at jennifer@nioxus.com.

en_US