Define Selection Criteria Formulas
One of the first steps in creating a new standard report or performing an advanced search is to define the selection criteria formula. The following is an example of how to build a formula for a set of criteria.
Find all students whose birth date is before June 30, 1995, and whose initial classification was during 2003.
There are two parts to this qualification.
The first is that the student's birthday must be before June 30, 1995. In the PowerSchool Special Programs formula language, this can be written as:
BirthDate < 6/30/1995
Dates can be compared using the less than (<) and greater than (>) signs as well as the less than or equal to (<=), greater than or equal to (>=), equal to (=), and not equal to (<>) signs. As you would expect, a date is less than another date if it is earlier chronologically than the other date (e.g., April 1, 2000 is "less than" May 1, 2000).
The second part of our example states that the student's classification date must be during 2003 assuming that this date is stored in a field named "InitIEPClassifDate". Because this is also a date, you can again make a comparison the way you did with the "BirthDate" field. In this instance, you need to make two comparisons: one to make sure the date is on or after January 1, 2003, and another to make it earlier than January 1, 2004.
You can do this by joining two comparisons with an AND formula as shown below. This formula states that the classification date must be both on or after January 1, 2003 and prior to January 1, 2004.
InitIEPClassifDate >= 1/1/2003 AND InitIEPClassifDate < 1/1/2004
If you had instead written the formula below (with an OR instead of an AND), then any date that was either on or after 1/1/2003 or before 1/1/2004 would meet the criteria. In that case, every date would meet the criteria, because every possible date is either on or after 1/1/2003 or before 1/1/2004 (and some are both).
InitIEPClassifDate>=1/1/2003 OR InitIEPClassifDate < 1/1/2004 (Incorrect Example)
This is the key to understanding the distinction between 'AND' and 'OR'. Joining criteria with AND makes the formula more restrictive; a student must meet all criteria to be included. Joining criteria with OR makes the formula less restrictive; a student meeting any of the criteria is included.
Now combine the two statements you wrote:
BirthDate<6/30/1995 AND InitIEPClassifDate>=1/1/2003 AND InitIEPClassifDate<=12/31/2003
Notice that the conditions are combined with an AND statement. Thus all of the criteria must be fulfilled for a record to be included.