The Apricot Report Builder can be used to pull information out of the database for a variety of purposes - like reporting on a client's progress for a funder or to prove how many people were served by a particular program.
But it can also be used to create data sets that can be manipulated in Excel or another database program and re-entered into the Apricot system. These kinds of reports, used in conjunction with the Import Tool (see articles: Import/Update Tier 1 Records and Update Tier 2 Records), can be used to correct data collection errors or update records after program options have been changed.
Why Use Record IDs?
Every record created in Apricot has a unique identifying number, which is called a Record ID (see article: System Fields). It is automatically created every time you or one of your users creates a new record in Apricot. A unique Record ID (see article: System Fields) is never re-used for any other record.
These unique IDs are therefore the best tool to use to ensure accuracy when you are updating records that already exist in the system - or indeed if you are looking for an accurate count of records in the system. You may have a client with the same or similar name to another client or you may have a number of family members who share the same address or clients who share the same birthday. You could create a way to use these identifiers to create a clean data set, but a Record ID is already unique to every record in the system and is a short cut to create a data set that does not contain any duplicate entries or miss-matches.
Finding Record IDs in the Report Builder
The Record ID field is traditionally in the System Fields section of any record - Apricot automatically creates the Record ID field there when a new form is initially set up to collect data. However, an Administrator can decide to move a Record ID field to another place on a form (it cannot be deleted).
When using the Report Builder, expand the black arrow next to the name of the form you need.
Expand the black arrow next to the System Fields.
Click on Record ID and drag it into the body of the report.
In the report example above, we are looking at the Record IDs for every Client Profile record that has been created in the database.
Add Relevant Identifying Data
Record IDs on their own are useful because they are unique - but this report is missing any information that can help us identify which records need to be updated or reported on.
If you are updating client or person-based records, it might be useful to drag in their names next, as well as any of the fields we would like to update on these records. In the example above, we have brought in the name and gender fields - name fields will help us identify the person and gender is the field that needs to be updated.
Add Filters to Find Records Needing Updates
Some of the records have gender information entered, but some do not and these are what we need to update. We can find these records with a filter.
To add a filter, click the + sign to the right of Filters.
Under Rule, select the column to filter - in this case, gender - and a comparison value. For this example, where we need to update any records that are missing gender information, we select "is empty."
Now our report is showing us all the records that require updating.
Publish the report.
Run the report.
Export the report. Another pop up will appear where you will be able to choose additional options if needed. It will be downloaded to your computer according to your internet browser protocols.
Open in Excel and Update
Now we can open the report in Excel and update the rows with the correct gender.
The next step is to import the data into Apricot with the Data Import tool. For more information on using the Data Import tool, please see the article here