Basic Reporting Part V FAQ: How to Adjust Table Joins (aka Why is This Report Showing Me All Contacts in My Database?)
- The tutorial will show you how to adjust table joins in a report
- When linking two tables together in a report, the report has the option to show you:
- All data in table A and matching records in table B
- All data in table B and matching records in table A
- Only records from both tables that have a corresponding record in the other table
For example, if writing a report using the donations and event registration tables, the report could show you:
- All donors and any event registrations they have
- All event registrants and any donations they have made
- Only donors who have also attended events (or only event registrants who have made donations)
Which of these options will be used in your report depends on the way the tables are JOINED. Table joins instruct the report how to pull the data from each pair of connected tables.
- If you are getting too many records in your report (such as all contacts, regardless of the other criteria you have put into your report), then very likely you need to adjust/change the table joins.
- Table joins can ONLY be adjusted on Standard reports. You cannot adjust table joins on Express reports (you would need to first convert the Express report to a Standard report).
Watch the Video (written instructions below the video):
- To get started, while in edit mode in the report, click on the Report Options button (gear) in the upper left hand corner.
- Select “Advanced” and then “Joins”
- In the resulting Joins window, select the boxes of the data you want to see (or unselect the boxes of the data you don’t want to see). In our example pictured below, we have written a donation report, which includes the Contacts, Donations, and Solicitor tables. Our Contacts table is connected/joined to the Donations table (first set of join options) and our Donations table is connected to the Solicitor table (second set of join options).
- The first set of options is how we want to join the Contacts and Donations table. By default (and which is not unselectable), the report is set to show all records that match between the two tables – that is, only contacts that have donations. The first selectable option allows us to also show donation records that doesn’t have contact data (there should be no such data in our database, as it’s impossible to save a donation without a donor/contact name). The second selectable option allows us to also show contact records that do not have donations (that is, all contacts).
- We are also presented with a set of options for the join between the Donations and Solicitor tables.
- Once you have selected the options for what data you want to see on your report, click “OK” to save your changes or “Cancel” to close the window without saving your changes.
- For advanced users, you can edit the way the tables are joined (the unique key/join fields in each table and the direction of the table join) by selecting the “Edit Row” button (pencil and paper icon). It is HIGHLY recommended that only advanced users who understand table joins use this feature.
- If you click the red “x” to the right of any table join, you will unjoin/unlink those two tables.
- If you delete the join between two tables, or a table isn’t showing up in your joins window, you will need to recreate the link/join between the tables. Select the “Recreate” button at the bottom to re-establish links between all of the tables in your report.