Fields govern how data is collected and reported on in Apricot. A diverse selection of fields is available in Form Designer, some tailored to correctly collect very specific kinds of data. Here we will go over calculation fields. These fields can perform mathematical calculations within your form.
With in-form calculations, your forms have the ability to perform mathematical functions automatically, based on information that users enter into a record. This can reduce the time your users spend performing the calculations themselves and it can eliminate errors.
Adding a Field to a Form
Open the form you want to edit in Form Designer.
In the "Field Choices" palette on the right-hand side, find the field you want to add to your form.
Click and hold on the name of the field, then drag it into the main body of your form.
Using Calculation Fields
Most of the fields available to be added on a form are for data entry - a user enters relevant information into an empty field. In contrast, calculation fields take information that is added elsewhere on a form and use that to create their own data.
In the example above, the user enters information into the numeric fields and then the calculation fields use that data as the inputs to perform needed mathematical calculations.
Calculation fields are required to take information from either numeric fields or other calculation fields, so there must be at least two numeric fields on a form to use calculations.
Each calculation field can perform one mathematical function.
Important Notes:
Calculations only run at the time of entry when users add single or batch records. They will not update when a record is updated.
Changes are not retroactively applied to existing records when adding calculation fields to forms. But, if you have the Import Tool (a non-standard, Premium feature) you can update the records via Import and then run Post Processing after updating old records. Please keep in mind that if the calculations need to run in a specific order, the fields might need to also be placed in a certain order on the form itself in order for post-processing to work correctly.
When updating calculation fields using the Import Tool, Date/Time Calculations will not update with import post processing and values must be imported directly onto the numeric/date fields used in the calculation. The only other option would be to manually update the values in each record that existed before the calculation field was added.
Calculations can be combined to perform more complex mathematical calculations.
Calculations will re-run when the data changes in a field that feeds into a calculation field. For example, Field A + Field B = Field C. The calculation will only re-run if you change the value in Field A or B.
Calculation (÷) Divide
This field is used to divide one numeric field by another. Only two fields may be added to a calculation.
To select the fields to be used in your calculation, expand the drop down menu under "Component Fields." You will have the option to select any numeric or calculation fields already added to your form.
This field can be made Hidden and Searchable. It can be used in Quick View.
Calculation (x) Multiply
This field is used to multiply fields together. An unlimited number of fields can be added to this calculation.
To select the fields to be used in your calculation, expand the drop down menu under "Component Fields." You will have the option to select any numeric or calculation fields already added to your form.
To add more fields to your calculation, select the "+" to the right of "Component Fields."
This field can be made Hidden and Searchable. It can be used in Quick View.
Calculation (+) Add
This field is used to add fields together. An unlimited number of fields can be added to this calculation.
To select the fields to be used in your calculation, expand the drop down menu under "Component Fields." You will have the option to select any numeric or calculation fields already added to your form.
To add more fields to your calculation, select the "+" to the right of "Component Fields."
This field can be made Hidden and Searchable. It can be used in Quick View.
Calculations (-) Subtract
This field is used to subtract one numeric field from another. Only two fields may be added to this field.
To select the fields to be used in your calculation, expand the drop down menu under "Component Fields." You will have the option to select any numeric or calculation fields already added to your form.
This field can be made Hidden and Searchable. It can be used in Quick View.
Calculation Round
This field is used to round a numeric value collected elsewhere on your form. You will need to have at least one numeric or calculation field on your form.
To set up this field expand the drop down menu under "Input" to select the numeric or calculation field that will be used in the calculation round field. Under "Math Function," you can select round (whole), round (decimal), ceiling (round up), or floor (round down).
This field can be made Hidden and Searchable. It can be used in Quick View.
Calculation Date After
This field is used to calculate how many days, weeks, months or years after a specific date something should/will occur or has occurred by adding a number or sum to a date on the form.
To set up this field, you will need to have at least one date field on your form as well as one numeric field. Under "Initial Date," expand the drop down menu to see the date fields available to be used in the calculation. Under "Time Span," expand the drop down menu to see the numeric fields available. You can also choose the interval you would like your calculation to show - days, weeks, months, or years.
If your date calculation will always use the same number - for example, if your follow up appointments always take place the same number of days after your intake appointments - you can add a numeric field to your form and give it a default value.
This field can be made Required, Hidden, Searchable or Locked. It can be used in Quick View.
Calculation Date Before
This field is used to calculate how many days, weeks, months, or years before a specific date something should/will occur or has occurred by subtracting a number or sum from a date on the form.
To set up this field, you will need to have at least one date field on your form as well as at least one numeric field. Under "Initial Date," expand the drop down menu to see the date fields available to be used in the calculation. Under "Time Span," expand the drop down menu to see the numeric fields available. You can also choose the interval you would like your calculation to show - days, weeks, months, or years.
If your date calculation will always use the same number - for example, if your follow up appointments always take place the same number of days after your intake appointments - you can add a numeric field to your form and give it a default value.
This field can be made Required, Hidden, Searchable or Locked. It can be used in Quick View.
Calculation Date Difference
This field is used to calculate how many days, weeks, months or years have elapsed between two dates on the form.
To set up this field, you will need to have at least two date fields on your form. Under "Initial Date" and "Follow-up Date," you can expand the drop down menus to see the date fields available to be used in the calculation. You can also choose the interval you would like your calculation to show - days, weeks, months, or years.
This field will calculate the number of days between the two date fields you select. This will always be a positive number, even if the field under "Follow-up Date" occurs before the field under "Initial Date."
This field can be made Required, Hidden, Searchable or Locked. It can be used in Quick View.
Note: The "Interval" (Days or Years) is locked after you publish the form and cannot be changed.
Calculation Time Difference
This field is used to calculate how many minutes or hours have elapsed between two times on the form.
To set up this field, you will need to have at least two time fields on your form. Under "Initial Time" and "Follow-up Time," you can expand the drop down menus to see the time fields available to be used in the calculation. You can also choose the interval you would like your calculation to show - minutes or hours.
This field can be made Required, Hidden, Searchable or Locked. It can be used in Quick View.
Note: due to MySQL limitations within Apricot, something like: 1:00am (04/01/2017) minus 11:00pm (03/31/2017) will give an incorrect value. To ensure correct data entry this should only be used if you will be doing a time difference for the same day.
The time difference field will always calculate the time elapsed between the two time fields as if they happened on the same day or within the same 24 hour period. To create a form that is accurately shows time differences that cross into another day, follow the steps outlined in this article: Calculating Time Durations That Span Multiple Days.
Tips & Tricks
If you multiply, divide, add, etc. a Decimal field by a Numeric field the resulting value will not be a decimal and will be rounded to the closest two decimal places (0.01-0.04 will remain the same whole number while 0.05-0.09 will be rounded up to the nearest whole number). This also applies to calculations between a Currency field and a Numeric field.