A Guide to Query Builder

Verb:

The Verb tells Query Builder what action you want to take.

• LIST is the option to use.
• You can use SORT or SELECT but it takes longer. 
• If you do use SELECT you need to put in the “after” box SAVE.LIST name.  I find it is easier and quicker to sort my savedlist beforehand from the colon prompt.

Query Options

You have a choice in Query Builder between typing in information in the provided text areas OR using the supplied buttons and then picking information from a list box.

If you know the name of the file, field names, etc., it is MUCH FASTER TO TYPE THE INFORMATION INTO THE TEXT AREA rather than sifting through the lengthy lists that are provided when clicking on the buttons.

File:

This is the name of the file you are downloading information from.  Most of the time it will be PERSON.

Using our example above this is where you would type:

PERSON

If you are doing a SELECT (as opposed to using a pre-defined SAVEDLIST) this is where you would type WITH, WHEN, GE, LE, etc. You must select a field, an operator, and a value.

Using our example above this is where you would type:

WITH STATE='KY'

This is where you type how to SORT your data if it is not already sorted.  An example would be BY LAST.NAME BY FIRST.NAME for alpha order. 

Using our example above this is where you would type:

BY CITY BY LAST.NAME

This is where you list the names of the data fields you wish to download.  Ex: T52.BEST.MAIL.LABEL1 T52.BEST.MAIL.LABEL2 ADDRESS.1 ADDRESS.2 CITY STATE ZIP

Using our example above this is where you would type:

FIRST.NAME LAST.NAME CITY STATE

This feature only works if you are sending it to a printer.  It does not work if you are importing it to Word or Excel.

This feature only works if you are sending it to a printer.  It does not work if you are importing it to Word or Excel.

Grand Total:

Do not use this option.

Use this window to tell Query Builder where to send your output.

• More often than not you will check PC/Process to send it to Word or Excel.  If you choose Excel it will open up automatically when the process is done.  If you choose Word you must go and open the file once it has been downloaded to  your computer.

• Click the PC… button to get the Local PC Destination Parameters dialog box
• PC filename:
  Displays the current location of your file. To change locations simply type over it or use the Browse button.
• Application:
  If you are using this to create a CQO file for Word, you can use “None” but if you want it for Excel you need to choose “Excel 5.0” or more preferably "Excel 97".  Once you choose Excel, “Show as” will change and you will need to pick “worksheet”
• Show as:
  Use this window to select the file type for your output.  If using Excel there is only one type - "workbook".

Sample screenshot from How to Extract First-Time Freshman Into Excel

Suppress:

• Usually the only thing that ever needs to be suppressed is the Item ID.  Only do this if you do not want the person's Datatel ID to be extracted with the rest of the information you select.

Before:
Enter any valid Query Statement that you want to run before your Query Builder selections.  Most of the time you will be typing something in the format of "GET.LIST savedlistname" (without the quotes).

After:
If you do use SELECT instead of LIST at the top you can put in the “after” box "SAVE.LIST savedlistname".  Then if you need to do further work with the group you selected you can just use GET.LIST instead of re-selecting the group.  Most end-users will never use the "After:" option.

Clicking on the “CANCEL” button returns you to the colon prompt (wIntegrate) or back to your menu list (User Interface).

Clicking on the “SAVE” button allows you to save your Query Builder file as *.wis file to be used for later downloads/reports. (The next time you wanted to use it you would simply click on the “open” button and find it.)

Sample Benefactor Query Builder set-up