Create Direct SQL queries

Direct SQL mode queries search for records based on your own self-defined criteria, not the system’s tables, fields, and operators. You use SQL syntax to define the criteria; therefore, you are not limited by interface restrictions.

For example, create a Direct SQL queryClosed a request to find a group of records that match specific criteria at a particular time to search for all students who have more than one absence this year.

To create a Direct SQL query:

  1. Go to a list page, such as the Student List.
  2. On the Options menu, click Query. The New Query pop-up appears.
  3. In the upper-right corner, click the drop-down to select Direct SQL Mode.
  1. In the box, enter the query in SQL:

  2. Note: When running a saved SQL query, the Prompt for Value token can be entered into the SQL in place of a comparison value. This allows users to enter values on the 'Search criteria' pop-up at run time. See Tips for entering the "Prompt for Value" token for more details.
  1. At the Search based on field, select the records you want to search on to determine which records are included in this query.
  2. Select the Cache results to increase performance checkbox if you want the system to cache the results of a large query to make scrolling through the pages of results faster. Essentially, this prevents the system from having to re-run the query each time you view another page of results.

  3. Click Validate to check the syntax of your statement for possible errors.

  4. Click Search to use the query, or Save As to save the query for future use.

Tips for entering the "Prompt for Value" token:

  • Use brackets and the keyword "prompt" to indicate a prompted value the user will be asked for when the query is used.
  • The general format for a prompt token is:

    { prompt :  field ID : default type : default value : label }
  • When entering a prompt value for a text field, enclose the prompt with quotes just as you would enclose a constant value in quotes.

    For example: Where PSN_NAME_FIRST = '{prompt:psnNameFirst}'
  • When entering a prompt for a numeric value, the prompt should not have quotes. It should appear just as a number would in the query.

    For example: Where ACT_PENALTY_TIME '{prompt:actPenaltyTime:default:2}'
  • In general, the query should be written as if the user-entered text would be placed exactly where the prompt command is.

  1. SQL without

    "Prompt for Value" field

    SQL with

    "Prompt for Value" field

    SELECT * FROM STUDENT

    WHERE STD_HOMEROOM = ’101’

    SELECT * FROM STUDENT

    WHERE STD_HOMEROOM = ’{prompt:

  1. SQL Syntax for…

    Function

    Prompt
    • Constant, must be "prompt"

    Field ID

    Text: Input field for text

    Character:     Input field for text

    Integer:     Input field for an integer

    Number:     Input field for a number. Decimal places are allowed.

    Date:     Input field for a date, with date picker

    Time:     Input field for a time value

    Logical:     Checkbox field. This field will produce a zero (0) or one (1) in the generated SQL, which is appropriate for all logical database fields. It might not correspond to other Boolean operations in SQL.

    Default type (optional)
    • Type of default value that should appear in the prompt screen.
    • Allows users to keep this value or replace it with their own.
    • Can be blank if there is no default; or can be either const or default.
    • See the following table on Default type = default.

    Default value (optional)
    • Default value to appear in the field’s prompt.
    • If default type is const, this is a suitable input value for comparison.

    Note: Default type and Default value can be left blank if they are not needed, but their positions should be marked with a colon ( : ) if the label is used and they are not.

    Label (optional)
    • Label displays when user is prompted for input value.
    • If this is not present, the label will come from the user label on the field Id.

When Default type = default, a relevant value is looked up and placed as the Default value:

Default Value

Function

today

current date

now

current date (same as "today")

districtYear

 last day of district school year

districtYearEnd

last day of district school year

districtYearStart

first day of district school year

firstOfMonth

first day of current month

firstOfWeek

first day of current week

lastOfMonth

last day of current month

lastOfWeek

 last day of the current week

Note: The following are only useful in the School view and retrieve values for the current school:

schoolName

 current school name

schoolOld

 previous school name

schoolStartGrade

school start grade

schoolEndGrade

school end grade

Examples of prompt commands in SQL:

Prompt

Function

{prompt:stdHomeroom}

  • prompts for homeroom value with label from homeroom field

{prompt:psnLastName:

  • prompts for last name
  • include the default of "Jones" when prompting user

{prompt:psnDob:default:today}

  • prompts for date of birth
  • displays the default value of current day

{prompt:psnEmail01:::Main Email Address}

 
  • prompts for an email address
  • use the label "Main Email Address" in prompt entry screen

{prompt:Text:const:Mary:Student first name}

 
  • prompts with field for text entry
  • default with value and label

{prompt:Date:default:today}

 
  • prompts with field for date entry
  • default value for today