In the case that you need to report on data recorded in two different Tier 2 forms, the forms must already be linked to each other with linking fields to prevent duplicating rows of data in the Report Builder. To learn more about linking fields in forms, click here.

Continue reading below to learn why linking is necessary in reports and how to denote a linking relationship in the Report Builder.

To provide more context, we'll use an example applicable to many Apricot organizations. Our participant's demographic information is tracked on the Tier 1 Participant Profile form. Upon viewing their Document Folder, they also have records for two Tier 2 forms: Program Enrollment and Service Tracking.

We can click their Enrollments tab to see they're enrolled in two different programs, each with a matching Program Enrollment record already created as seen in their Document Folder.

We'll assume each of the services that were tracked for this participant relate back to one of the two programs they're enrolled in. To confirm this, we'll click on one of the Service records to open it.

A linking field named 'Related to Program' already exists in the form, and the user who filled out the record has added a linking relationship to this Service record with the participant's Counseling program enrollment record.

Now, let's build a report to display this information. To learn the basics of creating a new report, click here.

We've started our report by pulling in basic identifying fields from our Tier 1 Participant Profile form and Tier 2 Program Enrollment form, such as Name, Program, and Start and End Dates. The report correctly displays one row for each of the participant's Program records.

Our end goal is for the report to show what services were provided to the participant in each of these programs, so pull in a field from the Tier 2 Service Tracking form next. We chose to pull in the Record ID field for our example, as it's an identifying number unique to every record created, though you may pull in Service Date or Service Type in your report instead.

After adding a Service form field, you'll see the updated Forms structure at the top of your report section. Both Tier 2 forms are indented under the Tier 1 form, and the Tier 2 forms are currently inline with each other.

This means that both Tier 2 forms are accurately associated with the Tier 1 form, but the report does not currently recognize the linking relationship between the Service form and the Enrollment form (otherwise the Service form would be indented as well).

The incorrect form structure inadvertently causes duplicated rows in the report, as shown by the Record ID field. This occurs because the Report Builder displays every possible combination of records between any unlinked Tier 2 forms in the report section for each Tier 1 record. The logic behind this is that, by default, all Tier 2 forms will relate back to their Tier 1 form, but individual Tier 2 forms will not always be linked to each other.

When reporting on Tier 2 forms that are linked to each other, especially with larger data sets, this may display inaccurate results.

Note: Depending on the rest of your report configuration, you may still experience duplicated rows even with linking fields.

To remedy this, click the name of the Tier 2 form that should be indented under the other to open its Form Properties.

In the 'Connected To' section, select the Tier 2 form it's linked to. The 'Using Field' section will appear, and you'll select the name of the field users create the linking relationship with. Click Apply and refresh the report to view your changes.

Note: Some forms may be linked to multiple forms or have multiple linking fields, so the correct options to select in the Form Properties may vary.

Now, the Service form is indented under the Enrollment form, the only records displayed are those with an Enrollment record selected in the Service form's linking field, and no Record IDs are duplicated.