A "Query" is an advanced method of searching and reporting on data within MicroBiz. This article will go over the many features of the query reports and provide you with a sample walkthrough.

Figure 13-1

Here is a brief description of the Query process:

1. First, you create your Query by selecting the MicroBiz data file that contains the pertinent data.
2. Next, you tell the Query what it is you are looking for.
3. Finally, you tell the Query how you would like to see the results of the search.

Data Dictionary

The first step to creating a query is building a data dictionary. The data dictionary is where you decide which MicroBiz data file (DBF) you will be using and the fields contained within. To view the Data Dictionary Wizard from the Main Menu, choose Tools | Queries | Data Dictionary.

Figure 13-2

• Defined Dictionaries – Lists the currently saved Data Dictionaries.
• Current Dictionary – Displays the currently selected Dictionary.
• Fields in Dictionary – Displays the number of fields available in the currently selected Dictionary.
• Fields List – The biggest part of this window lists all of the fields currently available in the selected Dictionary.
• Add Field –Allows you to create a new field in the Dictionary.
  • This is an advanced FoxPro feature and is not generally used by MicroBiz.
• Edit Field – Allows you to edit the currently selected field.
• Browse & Edit –Lists all of the currently available fields and their values in a table that can be edited directly.
  • Because any values in the table can be directly edited, it is recommended not to browse the fields in this way unless it is absolutely necessary.
• Delete Field – Allows you to remove a field from the Dictionary.
• Import Fields –This is the main way to create your Dictionary.
  • See the section below on database file examples that can be used.
• Transfer Fields –Allows you to copy or move fields from one Dictionary to another.
  • This is an advanced FoxPro feature and is not generally used by MicroBiz.
• New Dictionary – Allows you to create a new Data Dictionary.
• Switch Dictionary – This drop-down menu is where you select the desired Dictionary.
• Done – Saves and exits the Data Dictionary window.
• Help – Displays some basic tips for using the Data Dictionary Wizard.

Database File Examples (dBase files, or DBFs)

DBFs are the files used by MicroBiz to store your data. When creating a Data Dictionary (which is the first step to creating a Query), the most important step is selecting the correct DBF.
The majority of your DBF files will be stored in your Business folder (or whatever specified for your "System Files Path" under Management | Custom System Settings | File Data Path Settings). There are data files for your customers, products, departments, purchase orders, etc. Many of the DBFs are named something very close to what they contain, but some are not.
Here is a small table with some of the most popular DBFs used in Queries:

Figure 13-3 

Query Manager

The Query Manager is where you add, edit, save and run your various Queries. With the Query List Manager window (Figure 13-4 below) opened, you will have the following options:

 

Figure 13-4

• Query List – Displays all saved Queries for the currently selected Data Dictionary.
• Select –Opens the currently selected Query to your last save point.
  • If you have run this Query before, it will bring you to the screen where you can run it again.
• Add – Allows you to create a new Query using the currently selected Data Dictionary.
• Edit –Provides you with a shortcut to edit the record filters.
  • The Query can also be edited by choosing Select first.
• Copy – Allows you to duplicate the currently selected Query.
• Rename – Allows you to change the name of the currently selected Query.
• Delete – Allows you to remove the currently selected Query.
• Done – Closes the Query List Manager window.

Sample Query Walkthrough

In the following example, we will cover the Query process step by step for creating a specific report. This process should be useful for devising your own Query Report through similar means. Because there are various ways to do this, commonly used options will be selected and following this section will be detailed descriptions of each window used in the process.
Our sample Query will be designed to report on all customers who haven't purchased anything since a specified date. We will specify the MicroBiz data file (DBF) that contains this data, set our search parameters, set the information we want in the report, and have MicroBiz create a Report Form layout for this data that we can view on the screen and print out.

1. From the MicroBiz Main Menu, choose Tools | Query | Data Dictionary.
2. Click on the New Dictionary button and give it a two character name. The name is up to you, but for this example the guide will use "C1".
   • You will be prompted with the first of many helpful messages on the Query process. Read it if you like, but this guide will continue through these messages as usual.
3. Click on the Import Fields button and select the DBF desired.
   • For this example, choose CUST.DBF, which is found in your System Files Path (the Business folder by default).
   • A confirmation message will display the number of fields imported. Click OK.
4. Click Done in the lower-right to save this Data Dictionary and move on.
5. From the MicroBiz Main Menu, choose Tools | Query | Query Manager.
6. Since no Queries exist, the Query Manager will prompt you to create your first one. Enter a name for this Query (This guide will call it "Where'd you go?") and then click OK.
7. The Record Filter window will appear on "Step 1: Pick a Field." Since we are looking for customer's who haven't purchase since a given date, select "Last act" (last activity) and move to the next step.
8. For "Pick an Operator", choose "Less than or equal to" and move to the next step.
9. For "Enter a Value", we can either enter a specific date, or choose to have the system prompt us for the date each time. The latter option offers more flexibility with this report, so click on the "Ask Later" button in the lower-left.
10. The "Edit Record Filter" window will now appear with our single filter displayed. Although it is possible to create a much more advanced filter by adding to this one, click on the Done button to continue for now.
11. When asked if you want to run the Query now, choose Yes.
    • The Query Help window will be displayed. You can read these contents now or at any time by choosing Help. Click OK when ready to proceed.
12. On the "Edit Output Fields" window, you can choose the fields you want displayed on your report by moving them from the left side of the screen to the right. This can be done for individual records by double-clicking on them, or you can use the Pick/Unpick all buttons at the bottom of the screen.
    • The "fields" are the types of information stored in the DBF.
    • For this example, choose: Customer Number, Last Name, First Name, Company, Last act, Phone, Total Purchases, Internet (e-mail address).
    • Click Done when ready to continue.
13. From this window, all aspects of the Query (except the Data Dictionary) can be accessed and edited.
    • Choose "Edit Sort Order" and double-click on "Last act" so our report will be sorted by the date of last activity.
14. Choose "Pick Output Type" and select "Report in Form layout" from the upper-left, then click Done.
15. Uncheck "Browse Query results" so that only our report is displayed (otherwise a preview window will be displayed first).
16. Choose "Run Query" and enter the date of last activity desired, then click Done.
17. There are many options that can be configured here as well, but for now just choose "Screen" from the left and then click on the "Start report" button.
    • Your Query Report with all search results should now be displayed on the screen.
    • You can print the report from this preview window if desired.