Calculation fields are useful for automating common mathematical functions, reducing data entry entry errors and providing more robust reporting capabilities. However, the calculations only run when new records are created or when fields used in the calculation are updated.
When a new calculation field is added to a form with pre-existing records, the calculations do not automatically update for the previous records. You'll need to take additional steps to update any records that were created before the calculation field was added to ensure all your data is consistent with future records that will contain the new calculation.
Follow the steps listed below to learn how to use the Import tool to update records with new calculation values.
Step 1: Build a Record ID report
Click here to learn how to build a report for data import with the Native Reporting tool.
The report should include the Record ID from the form you're updating, each field used in the calculation, and a Calculation Column that's set up to perform the same mathematical function as the Calculation field recently added to your form. The Calculation Column will provide the date values to be imported into your previous records' Calculation fields.
Note: We recommend hiding the column for the Numeric field used in your calculation so the exported file of this report only contains the information needed to import.
You'll then filter the report data for records with a default value of "0" in the Numeric field. This indicates the calculation has not yet been run on those records, and will give us the Record IDs that need to be updated with the import.
Publish, run, and export this report to use the file in a later step.
Step 2: Generate an Import template
Learn more about updating records with the Import tool in the below articles:
Click the Imports page under Record Manager on the Administrator tab's navigation bar and select "Prepare For Import" in the Import Actions palette.
On the next page, configure your import with the following settings:
Form: Select the form you're updating with the calculation.
Import Type: "Data"
Options: Uncheck "Enforce All Required Fields"
Record Action: "Update Existing"
Reconcile [Form Name] Records Using: "Record IDs"
Next, click the "Unselect All" button in every section.
Then, select only the Numeric fields used in the calculation and the Calculation field itself to be included in the CSV file with the Record IDs.
Select "Download CSV File" from the Import Actions palette to generate your template.
Step 3: Edit the import template
Copy and paste each column's data from the exported report in Step 1 into the the appropriate column of the CSV file downloaded in Step 2. Double-check that the template is filled in correctly and save the edited CSV file.
Step 4: Upload the import template
Navigate back to the Imports page and select "Upload File" in the Import Actions palette.
Then, click "Choose File" to select the edited CSV file from Step 3.
Click "Upload" then "Run All" in the Import Actions palette.
This will open the Assign Programs window to select which Programs the records you're updating should be assigned to. Select the program name under "Available Programs" then click "Add >" to move it to the "Assigned Programs" section. Click the Apply button to save your changes.
Once the Assign Programs window is closed, the import will run to update your records with the new Calculation field data.