The limit feature can also be used for alias reporting which can compare records of the same form on a single row within the report builder.
Please note that auto-run is not available when editing reports with limits applied. While working with this type of report it will be necessary to publish and run your report in order to view how your edits are impacting your reporting results.
What is a Limit?
There are many pieces to understand when working with limits, and many key ideas to understand regarding how they function within your report. In this section, we will give a brief overview of all of the pieces and then go into more details in later sections.
- In the "Forms" area of a report section you will find a list of the forms referenced by our reports. The "Client Profile" is our "root" form, the form from which every other form relates. The "Limit Examples" form is a Tier 2 under the "Client Profile". Please note that the "root" form can never have a limit placed onto it.
- Clicking the name of a secondary form in the section, for example "Limits Examples (Always)" will open the Form Properties pop-up box. This box allows you to place a limit on, or remove a limit from, a form referenced by your report. Just as a reminder, the "Always" means, in this example, that every Client Profile must have at least 1 "Limits Examples" record. You'll note a "Limit" area within the properties pop-up with 3 options:
- None: this is the default, this means that every record of the form will be displayed as long as they fit the filter criteria of the report section.
- Last Created: this will display only the last created record for the form
- First Created: this will display on the first created record for the form
3. When you choose a limit other than "None", you'll see the name of the form and the type of limit in parentheses within the "Limit Sections" area of the report section
4. If we pause, we can see that our client "First Limits" is only present in one row of the report, this represents the "first created" record within their document folder of the "Limits Examples" form
5. We can see how the system is determining this by clicking the "Show" button. In the above screenshot, "Hide" is displaying because we have already clicked the "Show" button. When we click "Show", a Limit Section will automatically appear just below our report section with a limit applied
6. We can see here that the row that is displaying in the report section is the top row in the Limit Section and is not grayed out. This section will always group by a Name field from the "root" form and the record id as we see in the "Client Profile" column. We can also see that it lists 4 other records that are grayed out. Because limits are based off of record creation date/time (which usually corresponds to the Record ID, the highest being the most recent) we can also see that, since we are looking for "First Created", the lowest Record ID (399) is at the top of the results and the other rows aren't displayed in the report section.
To best understand how Limit Sections work, and to understand how filters function in congruence with limits, we need to understand the Tier 1-Tier 2 structure as well as what Apricot considers the creation date order.
- Our (Tier 1) Client Profile in this example is an anonymous client with the birth date of 01/01/1991
- Anonymous Client has 5 "Limits Examples" (Tier 2) records within their document folder
- The "Date" field is entered by a data entry person as well as "Made" and "Points (of 100)" while "Record ID" and "Creation Date" are system fields
- For this example, we were very careful to enter the dates in order. We also filled out the "Made" field appropriately in order to reference this field in our example. Within the document folder, the "last created" record is at the top of the list. Not only does this record have the highest "Record ID" but it also has the latest "Creation Date".
- Notice Record IDs 400, 401, and 402 all have the same exact Creation Date. In this case, Apricot determines which is the first record using the Record ID.
- The dates we will be working with in this example are 01/01/2016 through 01/05/2016
- This is the only client in the database for this example
Apricot, show us the first record created which falls within a given time range within a client's document folder.
As this section title states, we want to see the first created record within a client's document folder within a stated time range. So, let's say we want to see each client's first record that was created between 01/02/2016 through 01/04/2016. We know our example client has one record on each date (as well as one record before/after that date range). But, we only want to see the first record created in that time range. You may wonder why we would want this type of information: one example is that we can use this information to determine which of those 3 days a majority of clients received their first service.
- Click on the form that we want to limit
- Choose the "First Created" limit
- Click Apply
- Click "Show", this will show us our limit section which is where we will need to do our filtering
Now we will begin working within the limit section itself. This is very important because if we filter in the "main" report section we will see a different set of results, which may be helpful in other scenarios that we will cover later on in this article. For this scenario it is extremely important that we filter within the limit section for any data on the limited form. It is also important to tie date filters in the limit section to Global Values (please see the Filtering Reports article for more information).
- Click the "+" to add a filter
- Check the Global checkbox
- We know that we want to filter on the date field in this example. Notice that within the limit section the format is "Form Name - Field Name." For our example we need to select "Is Between" the "start" global value and the "end" global value
- Click Apply (you might need to refresh or publish the report in order to see this change)
Now we can examine the results. In our screenshot above we can see that the "limit section" is filtering for the dates, just as we said (01/02/2016 - 01/04/2016) and we can see the "Record ID" and "Creation Date" information as previously explained. Notice that the "main" section shows the first created record from our date range, even though that client's very first record was on 01/01/2016. This is the expected result and what we wanted for our example, as we wanted to know which record was created first within the date range we chose. Additionally, if our client had no records created within that time range we would see 0 results.
Apricot, show us the first record created within a client's document folder IF that particular record falls within a given time range. If the first record created within a folder does not fall within our time range, exclude the client from our report.
This example is slightly different. Now we want to see only those clients for whom the first created record of a tier 2 form falls within the time range that we specify. This would be useful if you need to know whose first Service was May of 2016, for example, and if you want to exclude every other client profile record from the report. There is a very important difference in our filtering though, we will be filtering in the "main" section. Note: this type of filtering with limit sections is more rare than our first example, and usually produces less results due to the exclusionary nature of this filtering structure.
- Click the name of the form which we want to limit
- Choose the First Created limit
- Click Apply
- Click the "+" to add a filter
- Choose the Date field to narrow down our results "Is Between" (notice we used Global Values again) 01/02/2016 through 01/04/2016
- Click Apply
As we can see, we have no results. This is because our client's first record was created on 01/01/2016 as seen in the limit section. But, we asked Apricot to show us who's first created record was between 01/02/2016 and 01/04/2016. If our client didn't have an 01/01/2016 record they would have shown up.
Hints and Tips
- For our two examples above you can replace First Created with Last Created. The same concepts apply.
- If you choose to filter within the limit sections (especially date filters) without using Global Values you will not be able to change them in Run mode for the reports unless you un-hide the section (we do not advise this as it might be confusing for most end users).
- By default, limit sections are not visible within Run mode of a report. However, you can make them visible like any other section: Click "Show" to un-hide the Limit Section, click on the gear for the Limit Section, and then un-check the Hidden box.
- By Default, limit sections are not exported; however, you can include them in your export. When you click the Export Report button you will have an extra section titled "Limit Sections" just check the "Include Limit Sections" checkbox and it will export into it's own tab.
- If your organization relies on the imports tool heavily to create records, data limits may not be in your favor. This is because all data in an import will have the same creation date/time, at which point the system falls back on sorting by Record ID. The chances of the imported records ending up in the correct order (even if you sort them correctly in the CSV template) are extremely slim. If you know you will need to use limits for reporting we must advise to manually enter the data in the proper order instead.
- It is possible to use limit sections for other scenarios as well, for example, you have a Donor Tier 1 form with a Donations Tier 2. You want to send thank you letters out so you need each donor to appear only once in the report, even if they have 10 donation records. You will be able to use a "Last created" limit on the Donations Tier 2 form in the report and just get one row for each person. In this scenario make sure to put the filters in the limit section.
- Each limit applied to a report section in a report is treated as an additional report section altogether. Additionally, reports with a large number of sections tend to also run more slowly. For this reason, we do not recommend using limit sections as a workaround in order to pull multiple un-linked tier 2 forms into a single report section in the interest of achieving a single row per client.
- When a limit is added, the fields that appear in the limit section vary based on the form you're limiting. If you place a limit on a Tier 2 form, the record IDs for both forms and the first five quick-view fields will be pulled into the limit section. If you place a limit on a Tier 1 form linked to another Tier 1 form, it will pull the record IDs for both forms and the field which determines the document folder name for the form being limited - if this is a name field, it will pull both the first and last name.