Tip – Quirky Queries

Tip – Quirky Queries

Queries are one of the best features of Master Builder. In case you don’t know, queries are found on most screens under either “File | Find” or – alternatively – by clicking on the binoculars icon. Queries are a way to get a list of records according to criteria you select – for example a list of AP invoices from the 4-2 screen. This information is then presented in a grid which can be drilled into for details or copied and pasted to a spreadsheet for further analysis. However, there are a couple of quirks with this process. Fortunately, these are easy to fix.

Beginner:

To create a query, go to a data entry screen such as 4-2 (AP Invoices) and click on “File | Find”. Enter a new name (the name of a new query) in the blank field, and click [New]. This screen will take you through a series of five screens that allow you to identify the information you want to display. The screens – in order of appearance – are:

Fields To Display
– these are the fields that will appear on the grid after the search.

Fields to Group By
– these are the fields that will provide the first level of sort for the results. Each group has a subtotal. You are allowed a total of 3 fields for grouping.

Fields to Sort By
– these are the fields that provide the second level of sorting of the results (after the groupings).

Fields to Select By
– these are the fields with which you can control the search. For instance, if you want to be able to select AP invoices with an invoice date of 12/31/07, you would need to include the field “acpinv.invdte” in the Fields to Select By list.

Selection Criteria
– for each field in your Fields to Select list, you can pre-define your default values. A good example of this is to filter out all void AP invoices when you search for invoices. To do this, you would need to include “acpinv.status” in your Fields to Select list, and then in the Selection Criteria you would select “Between” for the comparison, and then put a default of 1 to 4 (Open, Review, Disputed, Paid invoices).

The Issue – Intermediate:

So here is the rub. You may be irritated by fact that after you spend the time to enter your default selection criteria in a new query, when you run the query – the defaults don’t appear. Bummer. To fix this, immediately after you create a new query – before you run it – single click the new query name in the query list and click [Edit]. Then click [Next] through each of the five set up screens. Don’t change anything. Viola! Next time you use the query, the selection criteria defaults will be where they belong.

Related to this problem is the issue where queries that used to work suddenly and inexplicity crash. This takes you out of Master Builder in a particularly inelegant way. Usually, this condition is caused by someone starting to edit a query, deciding they don’t want to, and cancelling out of the edit screen.

Some people curse the system and try to re-create the query. No need. Just like above, single click the crashing query in the query list, click [Edit] and click [Next] through each of the five set up screens. This will fix the problem.

Advanced:

Queries are stored in the table “Qrylst.dbf” under each specific company directory. This means that if you have a set of really cool and useful queries that you have built up over the years and you start a brand new company (or get a new client) you must laboriously re-create each query manually.

A simple solution is to copy the files qrylst.dbf, qrylst.cdx, qrylst.fpt from the company data directory that has the queries you want to the new company data directory. All of the queries you had in the original company will now be in the new company. Beware – if you already created any queries in your new company, they will be overwritten.

It is also possible with a good tool such as Microsoft Visual Fox Pro to copy individual queries from one company to another in a selective way. Each record in qrylst.dbf represents a single query. The field “tblnme” will indicate which data screen the query will appear in. To move a specific query, copy the record in qrylst.dbf from the source company data directory to qrylst.dbf in the target company data directory. (law)