The SQL Table Inquiry utility provides an expanded search tool for locating documents or selected tables for records matching criteria you specify. For each document type, there are database tables that maintain the information (data) required by that document (PS Invoices, AR Statements, etc.). We have also added a "tables" option for certain non-document data (accounts, items, and vendors currently). After selecting a document type (or table) and date range, users specify query* criteria using the fields available for that document. Once all the desired criteria has been specified, the user selects Process (F12) to view a grid displaying the results (if any). All inquiries display some basic information about the document (document numbers, date, branch, entry user, etc.). In addition, the values of each "criteria" field is displayed as semi-colon delimited text in the right-most column.

*The term "query" refers to commands used for accessing or changing information in a database. The Spruce database is a SQL (Structured Query Language) database (there are other kinds). This utility program builds and issues a SELECT type query command that retrieves information from the database using the criteria supplied by the user. "Select" type queries don't modify data, they just retrieve data.

What do I need this for?

Not everyone will need to use this utility; however, it's helpful for those who have no or limited experience with SQL queries and need a quick way of finding information using criteria that isn't available in standard reporting. For example, I might want to locate customer orders where the Order User and Entry User are the same specific person (or possibly only where they are not the same). In either case, this isn't possible using standard reports, but would be very easy to find using this utility. After selecting a document type or table, you have a wide range of fields to choose from (pretty much anything that's available in the data) and you can specify multiple conditions for your document or table search.

Tutorial:

Read through or follow along with this specific example to get an idea of how this utility is used. Let's take the example above where you're looking for orders where the Order User and Entry User are the same (or different) and follow it step-by-step:

•First, leave the default selection method as "Documents" under the Selection panel.

•Next, choose the PS Customer Orders document from the "Type" drop down control.

•Now, set a starting and ending date range that would return a reasonable quantity of orders for review (on an active system, we suggest shorter time periods). Dates can be set manually or by choosing one of the preset dates. Some preset dates (such as future periods) may not apply to all documents or table fields.

•Next, move to the Search Options panel to the right. For now, leave the radio button selection as "Header."

•Click on the drop down (labeled "Field") and choose "Order User" from the listing. Field names are listed alphabetically, not by their column order in the database.

•Choose the = choice from the drop down and enter a user who would have orders during the period specified. Note: no validation is done on what is entered for text fields, so if the user name doesn't match an existing user, the software isn't going to warn you (remember you can do partial matches depending upon your choices in the drop down).

•Click the Add button (substitute the user name shown with someone from your company who does customer orders). A grid is used to display the criteria for searching documents. Multiple criteria can be added to help narrow down your search. Criteria can be removed by selecting the row in the data grid and choosing delete (or pressing the Delete key).

•To see what would happen at this point, choose the Process (F12) function. At this point, one of 2 things will happen. If documents matching your criteria and selection parameters (dates, etc.) are located, a grid (see the example below) is displayed showing those results. If no matches are located, a message dialog alerts you and the grid is not displayed.

Adding Additional Search Criteria

Ok, we've returned some documents, but let's say we wanted to narrow down our search even further. Perhaps we want to only view orders that have items which not been sold over our selected time span.

•Press the Retry (F5) function. This will redisplay our selection criteria.

•Next to the grid displaying our criteria, you should see two radio button options named "AND" and "OR." Choose the radio button next to "AND." When conditions are added they generate a query for the database. "AND" type conditions require that the data returned meet all criteria. "OR" types return data meeting either condition/criteria as well as those that meet both.

•This time, we will add some criteria using the Details selection. Click the radio button labeled "Details" and choose "Qty Sold" from the drop down.

•Choose the not equal (<>) operator and enter a value of 0 (zero). Click the Add button.

The grid displaying your search criteria should now look similar to the example below:

The Value2 column is reserved for "between" type conditions where two values are specified.

•Now, choose Process (F12) again to view the results.

At this point, you could add even more search criteria such as Qty Remaining>0 and/or Backorder = True, etc. Use the Retry (F5) function to return to the search criteria grid at any time and choose Process (F12) to refresh your results. To start over, use the Cancel (F9) function to clear all selections.

Removing or Modifying Criteria

Previously selected criteria can be modified by double-clicking on the row header you'd like to change. To remove a row, click on the row to select it and then press the Delete key (or select Delete from the data grid's context menu... usually a right-click).

Search Criteria Formats

There are many different types of data maintained in the SQL database tables. This utility is a bit simplified and deals with 5 specific types: string, number (integer or decimal), date, Boolean, and control-based fields. The type of value expected is listed below the search field after a selection is made ("is a text value," for example).

Dates

Dates should be entered in MM/DD/YYYY format.

Control Managed (Identity Values, Enumerations, etc.)

Control-based fields offer a selection list of valid and specific choices to choose from. In some cases, fields may have numeric values that are codes referring to something that may not be obvious. In these cases, we help by providing a selection control that lists the defined (friendly) names associated with the actual codes in the database table. Some values refer to other data such as accounts, jobs, items, and documents that have selection controls using in other areas of the software.

Boolean (True/False)

Boolean means "true/false," so the user selects one value or the other. These are typically maintained as a "bit" value in the database that is either a 0 (false) or 1 (true) value. In some cases, a NULL or nothing value is also permitted.

Numeric (Integers and Decimals)

Integers (or decimal) fields are numbers either whole numbers (integers such as 1 or 234) or numbers that contain decimal values (1.83, for example) such as a price or cost. Most decimal values are defined with a precision of 4 decimal places.

Text (String)

String types represent fields that contain text. Some string data is limited in the length (as far as the number of characters). The utility doesn't restrict or warn if you enter more text than a particular field's limit (in this case, processing would just generate a "no records located" type message).

Viewing the SQL Statement

The SQL (F6) function can be used to display a SQL statement that matches the criteria you've selected. This statement can then be copied to your desktop's clipboard and pasted into another application such as Microsoft® SQL Management Studio or Crystal Reports® software, for example. This can be useful for external queries or custom reporting. Queries don't allow direct changes in the form by the user. To copy the query text, select the text using your mouse and then press CTRL-C (or choose "Copy" from the context menu... usually a right-click).