The Data Manager forms now have the ability to calculate values based on numbers entered into fields in the form. They can also have group record fields that allow a set of fields to be saved multiple times as records in a form.
Here is an example expense report form that calculates the total amounts and dollars based on the user entries and also includes a group records field:
Expense Form Template Example
Calculation fields
Calculation fields are text fields with a formula entered into the Field Value. Calculation fields automatically display the calculations of a formula and entry fields allow numbers to be entered into the form by the user.
To create a calculation field, set the Format As drop down to Numbers Only, and enter an equal sign into the Field Value followed by the formula to calculate. The equal sign should be the first entry into the field value, as in the example image below:
The calculation fields can be set to multiply, add, subtract, or divide numbers and values from other entry fields that contain numbers. For example, a calculation field in the form can multiply an hourly rate times the number of hours a user enters into a field in the form. The hourly rate can be hard coded into the field as shown above or it can come from another entry field in the form.
Calculation fields will appear in a form with a blue background in a readonly format. Users will not be able to alter or enter text in a calculation field. When the user types numbers into an entry field, the calculation field will automatically update with a value based on the formula entered in the Field Value.
To use values from other number entry fields in the calculation field formula, use the database field name that is displayed in the lower right corner of each field in the form. The image below shows the database field name highlighted. Just copy and paste the database field name into a calculation field to use it in a formula.
Calculation field values cannot be used in other field calculations. So just use the same formula as the basis for any other field that should use the same calculation value. Also, note the entry field must have the Format As drop down set to Numbers Only to allow calculations based on that field:
Calculation Entry Fields
Entry fields must have the Format As drop down set to Numbers Only as in the above image.
An example calculation field formula that uses the above entry field would look like this when entered into the field value:
=custominput14+custominput9
The formula can also have other mathematical entries, as in the following examples:
Multiply an entry field value by 0.21
=custominput14+(custominput9*0.21)
Divide an entry field value by 2
=custominput14+(custominput9/2)
Subtract one entry field value from another
=custominput14custominput9
Group Field Records
Group fields allow the user to enter a series of entries for a specific group of fields as shown in the example below. To create a group field area, add in a new field and select the Field Type drop down as Group Field.
Once you have created a group field, you can add individual entry fields that can be filled out for each entry record. To add a field to the group, add a new field and set the Group Field drop down to the name of the group field that was created. To allow a sum of the field entries, set the Format As drop down to numbers only. There can be multiple group fields in a single form. The image below shows an individual field that is part of the example group:
Each individual field in the group set to Format As = Numbers Only has a sum field that can be used in a calculation field formula. To use a group fields sum in a calculation field formula, use the normal database field name for the field but add the word sum to the end of the name. For example, the above Miles field that is part of the Expense Details group shown in the image above would have a sum field like this: "custominput9sum"
An example formula that uses the custominput9sum field would look like this:
=custominput9sum+custominput14
This would add the total number of miles for all records in the group field plus the other expenses entry field.
