Reporting with Multiple Tier 2 Forms and Linking Fields
Taylor Roderick avatar
Written by Taylor Roderick
Updated over a week ago

We will be going over an advance style of reporting where you may need to report on data that lives on two different tier 2 forms. This will require that the two tier 2 forms are currently linked together (using linking fields) and that the records were saved with the linking fields to report on this data without duplication of rows. For more information on how to setup linking and how to use the fields please see the articles:

  • Linking

  • Linking Records

Real World Example

This concept can be very abstract and since all Apricot databases may be setup differently we are going to use a "real world" example that should be easily relatable to your own database design. Let's say we are working with a client called Sam Doe. His demographic information is tracked on the Tier 1 Client Example form. He also has 2 Tier 2 forms as well: Enrollment and Services. Each with a different number of records.

In the above screenshot we can determine the following about Sam:

  1. Sam is enrolled in two different programs:LegalEmployment

  2. Sam has 2 records of the Employment form, one for each type of program

  3. Sam has been helped three timesLegal Counseling onceResume Building Workshop twice

  4. We can also assume that each of those services relate back to only just one program

Next, let us take a look at one record of each form to examine their setup from a data entry user's perspective.

We can see in the above screenshot one of Sam's Enrollment records. This one is showing he started on 04/01/2017 (and left the program on 04/14/2017) and the program is Employment.

In the screenshot above we can see that Sam came in for service on 04/10/2017. Below the date field is a linking field with the title of "Related to Program" which ties this service record directly to his Employment Enrollment record. We also see that the Service Provided is called Resume Building Workshop.

Now that we have a general overview of the site setup we can move forward with building our report. Let's first start with a fairly basic report which references one Tier 1 form and one Tier 2 form.
​

In the above screenshot we started by pulling in the First and Last name of the client in the report from the Client Example form. Then, we added the Start Date, End Date, and Program fields from the Enrollment form. Examining our preview results, the report does look correct as Sam had only 2 Enrollment records: one for Employment and one for Legal. However, we know that we want to show what services Sam received for each of these programs as well which will require us to bring in one more Tier 2 form: Services.
​

Now we have two Tier 2 forms in our report (highlighted in red). Let's pause and examine what has happened to our report because it appears we are getting some duplication of results.

  • At the top, highlighted in red, we can see that there are two Tier 2 forms and they are inline with each other. This means they both relate directly to the Client Example form only. This means that Apricot is currently ignoring the link between the two tier 2 forms. This is the cause of our duplicated tier 1 information, and we'll fix this later on in the article.

  • We can tell there is duplication because we brought in the Record ID field from the Services form, the last column in the report. Remember, each Record ID in Apricot is unique, so since there are Record IDs appearing multiple times we know that we are getting some duplication. To point this out, we have highlighted those that are duplicates in the same colored highlights.

You may be wondering why Apricot is doing this as we know first hand that there is a link between the tier 2 forms. The reason is that, by default, Apricot uses the Tier 1/Tier 2 structure whenever new data is introduced into a report section because this will work in each and every case. Separate tier 2's will not ALWAYS be linked, but they WILL always relate back to the tier 1 form. So, we get the duplication because without utilizing the link between the tier 2 forms, Apricot must show each and every possible unique combination of records between those two Tier 2 forms. We know Sam has:

   Number of Records Enrollment Form 2 Services Form 3 Multiply the Records X Total Unique Combinations (Rows) 6 

So, the general rule will be for every Tier 2 form under the Tier 1 form in a report section you will need to multiply the amount of records for each Tier 1 which will determine the amount of unique combinations. This particular report has a very small sample size but if there were a lot of clients it would quickly grow and become difficult to read for accurate results. Now that we know what (and why) Apricot is displaying the results like this, let's continue to adjust the report and tell Apricot that the Enrollment and Services are related via a linking field.
​

  1. At the top of the section under "Forms" click on the form name that has the target link (usually this is not the Enrollment type of form)

  2. In the "Connected To" area tell Apricot that it relates to the Enrollment form by clicking on the radio button next to it

  3. Choose Apply

  4. For the changes to take affect you will need to refresh the browser (depending on your browser the icon may look different)

After the page refreshes and the report loads we will see a drastic change in the results. We can now see there are only 3 rows of data (which is expected since Sam only received 3 services). We can also see that it does appear that the Services Provided line up perfectly with which Program (Enrollment) they actually relate to. Some other items to point out:

  • Up near the top, highlighted in yellow, we can see that Enrollment and Services are no longer perfectly aligned. Instead, they have a cascade look to them. This tells us that Apricot is referencing the linking fields between those two forms.

  • We highlighted the Services Record ID column to also show/verify that each Record ID is showing up just once, as expected.

Hints and Tips

  • It is possible to build reports that use multiple Tier 1 and Tier 2 forms, this article/example was just to give you the tools/skills needed to build such a report

  • Some forms may relate to multiple forms so picking the correct "Connected To" form may be more difficult within your own site

  • A form may also have multiple linking fields that connect it to another form, in which case you will need to pick the correct "Using Field" radio button as well (this is a less common scenario)

  • With some configurations, even with linking, you may still get some duplication (this is when not every form has a cascade)

  • If you have Dynamic Fields (a premium, add-on feature) most of these also work as links and can be used in place of a standard linking field (the skills/concepts in this article will remain the same)

Did this answer your question?