Calculation fields can be very useful in Form Design when there is a need to perform specific mathematical equations on records. Here we will go over the steps to update previous records of a form when a calculation field is added to an existing form after records have been created.
Calculations will not automatically run on existing records in your Apricot system. When calculation fields are added to existing forms that records have already been created from, there are certain steps that must be followed in order to update the previous records.
Adding and editing the fields needed for calculation
The first step is to add in the fields that are needed for the calculation to your desired form. A description and more detailed overview of the different types of calculation fields available can be found in our Calculation Fields article. If you have already added your Calculation Field and just need guidance on updating your existing Records, please proceed to the "Updating previous records" portion of this article.
In this example, we will be using a "Calculation Date After" field to determine the date which is 30 days after the "Intake Date". For this example, we are assuming there is already a date field in the form for the Intake Date. In order to do this calculation we will need to add in a numeric field and the appropriate calculation field. Follow the steps below once you have opened the form you wish to edit.
Locate and pull in a Numeric field
Locate and pull in the Calculation Date After field
Once you have added in the fields necessary for the calculation, you will need to adjust the properties for each field. We will go over editing the numeric field first.
Click on the green gear to open the field properties for the numeric field.
Adjust the Display Name as desired.
Adjust the Standard Properties as desired. We often see clients hiding or locking fields used in calculations so that data entry users are not able to change the values in the field and consequently alter the calculation.
Input the default value that will be used in your calculation. In this example, the default value will be 30 since we want to find out the date 30 days after the Intake Date.
Click Apply.
Now you will need to set up the Properties for the Calculation field.
Click on the green gear to open the field properties for the Calculation field.
Adjust the Display Name as desired.
Adjust the Standard Properties as desired. The most common property applied here that we usually see is Quick View.
Select the date field to be used in the calculation. We are using the Intake Date.
Select the Numeric field, "Intake date calculation", that we set up to be used for Time Span.
Select the interval you wish to use. We are using Days to determine 30 days after Intake Date.
Click Apply.
Now that we have all the fields set up for our calculation, Publish the form!
At this point, any NEW records entered into Apricot will automatically calculate the date for 30 days after the Intake Date. Next we will go over the steps required to update this calculation field for any pre-existing records within your system.
Updating previous records
The easiest way to do this is by updating existing records via Import. You can also do this manually one record at a time but that is tedious. In order to do this via Import, we will need to build a Report based on Record IDs that contains the required information we will need to Import. This will need to include a Calculation Column in the report that will give us the date value needed to import into the Calculation Field. Please reference these articles on Building a Record ID-Based Report for Importing Data and Calculation Columns for additional information regarding that. Below is an example of the information you will need in your report.
The Record IDs from the form you wish to update
The Column for the Numeric Value used in your Calculation. In this example, we renamed the "Intake date calculation" Numeric field to be "No default value on Intake date calculation".
The Initial Date Field used in your Calculation. This field can be hidden so that when the report is run and exported, only the information needed for the import will be in the exported file. This will make setting the template up easier since you can copy and paste the information.
A Calculation Column which will be set up to calculate the date 30 days after the Intake Date(Initial Date) which will give you the date value to import into your new Calculation Field.
A filter to narrow down the Records IDs to only those whose Record contains a Default Value of "0" in the Numeric Field. This will give us the Record IDs with no default value entered that we will use in the import template.
Finally, you will want to publish, run, and export this report. We will come back to the data in the exported report a little later.
Preparing for Importing
If you have never used the Import Tool to update records, please review the appropriate article before moving on:
Once you have accessed the Import Tool, select the option to Prepare for Import.
Now we can move on to creating the template we will use to update existing records.
Select the Form you wish to update.
Select "Data" as the Import Type.
In this case, we do not need to enforce Required Fields so leave this unchecked.
Select "Update Existing" as the Record Action
Select to Reconcile using Records IDs
You can now go through and click the "Unselect All" button in every section so we can select only the fields we will be updating.
For this example, we only need to include the "Intake date calculation" and "30 days after Intake" fields. After selecting those fields, the CSV preview looks like this.
The next step at this point is to download the CSV file which you can do by selecting that option in the "Import Actins" menu on the right hand side of the screen.
Entering the data into the CSV template to upload and running the Import
Now you should have an exported file from the report as well as the exported CSV template that was designed during the import preparation. You will want to copy and paste each columns' data from the exported report file into the appropriate column in the CSV template. Once that data has all been transferred to the CSV template, you are ready to return to Apricot and finalize this update.
Navigate back to the Imports page and select the action to "Upload File".
Click on "Choose File" then find and select the CSV template file that you created
Once you have located and selected your CSV file, then select "Upload"
Select to "Run All"
This will open the Program Assignment window where you can select which Programs you would like to assign to the records in question that are being updated. Locate the Program you wish to assign and click on it and then select to "Add" and "Apply" the changes.
You are finished!! Once you have selected "Apply" the import will run and process updating your records!! You will receive a confirmation message that the import was successful.