DAT Query and DAT Filter Common Mistakes
The following are common mistakes related to creating a DAT Query with  Data Analytics Tool (DAT).

- DAT Filtering Differences: Must Have (A and B) versus Must Have A and Must Have B.  
    - Mostly, your DAT Filters will be of the Must Have (A and B) variety.  The conditions A and B are related and must be tested together.  Example:  Inquiry Must Have Action Description Application Received and Academic Year 2015/2016.  Both the action description and the academic year are checked for the same action record. If you add two separate filters, one filter for inquiry Must Have Academic Year = 2016/2017 and the second filter for inquiry Must Have Action Description = Application Received, you won't be getting the results you expect.  You will get a list of all applicants who have any action for the academic year 2016/2017, and have another Application Received action for any year.  This means someone who applied 3 years ago and re-inquiried this year would meet the criteria.  The conditions for the Action Description and Academic Year must be conditions on the same filter. 
    - Sometimes, your DAT filters will be of the Must Have (A and X) and Must Have (B and Y) variety.  In this case, the test for A and B are supposed to be independent.  Example:  The student Must Have Course Name “English 12”, and the student Must Have Course Name “AP Calculus”.  
        i. The natural inclination is to create a single filter for the student Must Have Course Name in English 12, AP Calculus.  The way filters work is they check each record for a match.  Does the student have “English 12”?  Yes?  Then the student passes the condition.  This filter acts like an OR condition, listing students who are taking either English 12 OR AP Calculus since either one is sufficient to pass the condition.  You will have too many results. 
        ii. The next try:  the student Must Have Course Name = English 12 And Course Name = AP Calculus.  Now, suppose a student has English 12.  Again, each record is checked for a match: The first condition is a match, but the second is not - English 12 is not AP Calculus.  This second attempt return no results.  
        iii. So how do you correctly check that a student has both?  You need two separate filters: the student Must Have Course Name = English 12 And the student Must Have Course Name AP Calculus.  Note:  Schedules have school years.  These filters are not complete without specifying the school year that class is for.  As with “Actions have Academic Years” and “Schedules have a school year” above, the school year must appear in both filters. Note: (i) uses a range, which when used outside of a filter can be used for DAT Fields - Step 2 - Pivoting.  Notice that the values for the pivot are turned into columns, and if the record matches one or more of those columns, those matched columns have values.  If no columns are matched, the record does not appear at all.

- Student's Status:  The “Student Info” used to have Status and Substatus fields, but these were only there for backward compatibility.  To ensure you get the correct status, link Student Info to “Statuses By Division”.  This table will have the statuses for each division and current/next year.  Make sure to specify the status you want, for the school divisions you want, in the school year you want.

- Inquiry's Status: The “Inquiries” table used to have Status and Substatus fields, but these were only there for backward compatibility. To ensure you get the correct status, link “Inquiries” to the “Inquiry Divisions” table. This table has the statuses for each division and academic year.

- If you link students (or inquiries) to households, and only want one row per student returned, add a condition limiting the Primary Indicator to P for the “Relations - Student/Inquiry/Teacher” to only get the primary households.

- Generally, it is not recommended to attempt creating filters when the relevant data is not present as the process of constructing conditions may be challenging. In particular, since the system automatically presents available options using the data in the database, the functionality of specifying conditions is limited.

- It is not recommended to place a DAT Query as a DAT Dashboard Element if it is expected to return more than 20 records because it may affect performance and take up a large amount of real estate on your dashboard. 

- Checking for the absence of a record:  To check for the non-existence of a record, you must use a Must Not Have filter.  Example: You want a list of Applicants who have not completed a Student Visit.  Construct your filter exactly as if you were looking for all the Applicants that did complete the Student Visit, but use Must Not Have for the filter instead of Must Have.  Example:  You want to find all students who do not have an action of Field Trip.  The common mistake is to search for all student who “Must Have” and action with action description <> “Field Trip.”  If a student has any other action besides “Field Trip” they will match the criteria for this filter.  An “Enrolled” action has an action description unequal to “Field Trip” and matches the erroneous criteria.

- Actions have Academic Years: When looking for Inquiries/Applicants or Students who may have or have not completed certain actions, remember to include the Academic Year.  Inquiries may have previously inquired and completed various actions, but chances are you are only interested in actions for the year they are currently applying for.  Similarly, Students may enroll in your school one year, withdraw another year, and then come back years later.  

- Schedules have a school year: When working with Scheduling tables, most of them have a School Year which should be specified.  These tables would include Student Courses, Course Sections, Selections, and more.  Before you start scheduling for next year, your queries will appear to work fine without a school year.  However, once you start building next year schedules, if you haven't been specifying a school year, then both current year, next year, and What If Scenarios may start showing up.  Note: School Years in scheduling are designated 0 for the current year, 1 for the next year.

- For Development > Donation, records may have donation types of Gift, Pledge, Pledge Payment, Matching, or Soft Credit.  If you are totally actual giving amounts received then exclude donation types of Pledge, Matching, and Soft Credit.  Note:  If you include both Pledge and Pledge Payment in a DAT Query, then you may double count the amounts: once for the original Pledge and again for each pledge payment to that pledge.  Note: Donation types of Matching are a special kind of Pledge and should be treated the same as Pledges.  If your report excludes Pledges for the purposes of reporting then it should exclude Matching as well.

- For Daily and Period attendance no record is saved for students who are present.  If you want to create a “Perfect Attendance” report, you will want to only select student fields and then apply a Must Not Have filter to your list of students.  Example: You want a list of all students who received no discipline points during the marking period.  You would create a filter connecting to Combined Period Attend And Discipline.  Then add a condition for your date range and for points > 0.  Save your conditions, click add, and change the filter type to Must Not Have.

Schedule Demo
Please, make sure that all required fields (marked with *) are completed.
    School Name *
    Relation to School *
    select
    Last Name *
    Your First Name *
    Your Email *
    Your Phone # *
    PCR Educator School Information System is an online database engineered for schools and universities to deliver the highest level of flexibility, unique experience, transparent communication and customized design.