DAT Query - Step 1 - Connecting Tables
Posted November 09, 2018
A DAT Query allows you to pull data from multiple tables. The Connecting Tables mechanism allows you to connect (join) different tables in your DAT Query. To connect one of the tables in your DAT Query to a new table, navigate to DAT Query - Step 1 (DAT > Queries > Query).
1. Click the [+] icon next to the table you are connecting to.
2. Choose whether you will connect “with” or “with or without”.
- If you choose “with”, the newly connected table must have data for the original table's record to show up.
- If you choose “with or without”, the original table's records will always show; however, the newly connected table might not have any data for a particular record - and those columns are empty.
3. Choose the table to connect. The list shows the tables that already have a defined connection to the table you clicked. When the table you need is not in the list, see Connecting to Other Table below.
Example: Your initial table is students, and you choose to join to actions. For the actions, you are going to include DAT Field Conditions only for Enrolled actions in 2014/2015. If you choose “with”, only students who have such an action appear in the result set. If you choose “with or without”, then all students appear in the result set, but action fields are empty for students who do not have an Enrolled action for 2014/2015.
Note: If you link to a table “with or without”, and additional tables connected to that table will also be linked as “with or without”.
Note: Tables that are connected as “with or without” show as italicized.
Note: It is entirely possible to add the same table multiple times. Suppose you want to see all your applicants for the 2014/2015 school year. You also want to see their inquiry action date, apply online date, and copied date. Many of your inquiry records for the year may or may not have apply online or copied dates. So, you connect Inquiry Actions to your inquiries three times: Once for action types of Inquiry for academic year 2014/2015; once for action types of Applicant; once for action types of Copied. Each of these links would be made with the “with or without” option.
Connecting to Other Table
Other table... connects two tables on fields you choose, even when no connection is defined between them. For example, when a household attribute holds a fund code, you can connect Household Attributes to Funds on the attribute value and the fund code.
- Click the [+] icon next to the table you are connecting from.
- Choose “with” or “with or without” as described above.
- Choose Other table... at the bottom of the list, then choose the table in the second list. The second list shows every table your roles allow you to report on.
- Under Join on, choose a field from the table you are connecting from and a field from the new table, then click Add.
- Add more pairs when records only match on a combination of fields. A record connects only when every pair matches.
- Add conditions and display fields as usual, then click Save.
Note: When one field holds text and the other holds a number or a date, the text is converted to match. An attribute value of 4 connects to a Fund Id of 4, and a value that cannot be converted connects to nothing. Two fields that cannot be compared, such as a number and a date, cannot be added as a pair.
Note: A table connected through Other table shows (custom) after its name, with a Joined on line listing the matching fields. Click the table name to change the matching fields.
Note: Only the tables and fields your roles allow are listed, and your access is checked again when you save.
Related articles
More from the Knowledge Base
Still have questions?
Live chat is staffed weekdays 8 am to 7 pm ET. Tickets get a response within one business day.
New to PCR Educator?
If you landed here from a search and want to see what PCR Educator can do, the demo is the fastest way in. Thirty minutes, no commitment.