Calculated Fields

Authors can use calculated fields to auto-populate a field with data. A few ideas of what can be accomplished using calculated fields are listed below:

• Perform numeric calculations between integers or decimal data types.
• Modify text strings such as combining a first name field with a last name field to create a full name field.
• Pull data from another field from the current record a record in another collection tab linked to the current record.
• Date calculations such as adding a duration to a date or finding the duration between two dates.
• Create complex if-then-else logic so a field displays a different value based on the defined condition.

You can set up a calculated field through the following steps:

1. Add a widget to the form canvas. The widgets which can become calculated fields are the Input Box, Text Area, Checkbox, Radio Button, Date Picker, List Box, and Combo Box. Local Grids can also use calculated fields in their columns.
2. In the Properties section of the Form Editor select the Calculated radio button.
   
3. The Expression Builder can be used to define the calculation which will be performed. When you select a function in the Expression Builder information about that function is displayed in a yellow bubble.
   

Aggregate Fields

Aggregate fields can be used to help summarize other collections by performing specialized functions that are performed on the records on another collection. For example, you can have a field in a record tab which displays a count of the number of records in a collection tab. Aggregate field calculations can only be performed using the Input Box widget. There are five types of calculations that may be performed using Aggregate fields:

• Count - Calculates the number of records in the selected Collection. This function can be performed on any type of collection field.
• Sum - Sums the numeric values of a field for all the records in the selected Collection. Only a numeric field (decimal or integer) can be used for this function.
• Avg - Calculates the average of all the numeric values in a field of the selected Collection. Only a numeric column (decimal or integer) can be used for this function.
• Max - Displays the maximum value from a field in the selected Collection. This function can be performed on any type of field. String values will display the last value from an alphabetical order sort starting from capital A and going to lower case z.
• Min - Displays the lowest value from a field in the selected Collection. This function can be performed on any type of field. String values will display the first value from an alphabetical order sort starting from capital A and going to lower case z.

You can set up an aggregate calculation through the following steps:

1. Add an Input Box widget to the form canvas.
2. In the Properties section of the form editor select the Aggregate field radio button. The properties section of the form editor will change, allowing you to set up the aggregate field.
   
3. Click on the button to the right of Linked Collection. The Select A Collection window will appear.
   
4. Select the collection that has the field you wish to perform your aggregate calculation on and click Select. The name of the application and collection will update in the Properties section.
5. Under the Choose Function select the function that you wish to perform. The functions Count, Max and Min can be performed on a field of any data type however, the Sum and Avg functions can only be performed on fields that have a data type of integer or decimal.
   
6. Now we will select the field in the linked collection which the aggregate function will be performed on. Click on the button to the right of Filter to bring up the Filter and Sort window.
7. To set up the filter we will need to use the Advanced QBE filtering feature. Click on Advanced QBE Filtering.
   
8. To set up the filter we will need to use the Advanced QBE filtering feature. Click on Advanced QBE Filtering.
9. Set up a variable (in this example $Project) which will define the field that our aggregate function will be performed on. In the Expression Builder a variable is a user-defined name for a field which begins with a $, for example $var is a variable named 'var'. Variable names cannot contain spaces. This variable will be used in the Value field for the aggregate field's properties.
   
10. The Advanced Search Window is also where you can define other filtering criteria to be used in the Aggregate field. For example, if we only want to count the project records that have the status of 'Started'. Complicated filtering expressions can also be built using the Expression Builder in the Condition portion of the Advanced Search window.
    
11. Click Done to close the Advanced Search Dialog and then click Done to close the Filter and Sort window.
12. Enter the name of the variable you defined in step 9 in the Value box for the widget's properties.
    
13. Click on Save and Exit to save your changes and exit the Form Editor.