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

Queries Overview

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, Customize, Windows 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.
      
      

Various Query Manager Functions

There are many setup windows contained within the Query Manager (most of which were seen in the walkthrough). This next section will go over each of these setup windows in detail.

Run Query Window

After selecting a Query from the Query List Manger (Figure 13-4), the following window will appear, allowing you to edit and/or run the selected Query.

                                             Figure 13-5

• Edit Record Filter – Allows you to view and/or edit the record filters (Figure 13-6).
• Edit Output Fields – Displays the currently selected output fields (Figure 13-7).
• Pick Output Type – Allows you to change the output type (Figure 13-8).
• Edit Sort Order – Allows you to edit the sort order (Figure 13-9).
• Browse Query Results –With this option checked, MicroBiz will display a preview window (similar to browsing the DBF with FoxPro) before the selected Output Type.
  • The selected Output Type report will be displayed after you close the preview.
• Hide Duplicate Records – If enabled, duplicated results will not be displayed on your report.
• Run Query – Runs the Query with the selected settings.
• Help – Displays the Query Help window.
• Done – Closes the Run Query window.
  
  

Edit Record Filter

Record Filters determine the results of your Query, based on the settings made here. You can keep things simple as a single filter, or string together multiple filters for more advanced searching.

                                           Figure 13-6

• Filter List – Displays the filters currently in use.
• Add –Allows you to add a new filter.
  • Multiple filters can be strung together with AND (both conditions must be true) or OR (either condition may be true) functions.
• Edit – Allows you to change the selected filter.
• Switch – Allows you to switch between AND/OR functions.
• Move –Allows you to change the order of filters.
  • This is used when grouping filters with brackets.
• Bracket –Used for grouping filters.
  • In Figure 13-6, the first two filters are grouped together with an AND function and the there is a third filter grouped to these with an OR function. This means that search results will be displayed if both of the first two conditions are met (the "AND" function), OR if the third condition is met.
• Delete – Removes the currently selected filter.
• Cancel – Closes the Edit Record Filter window without saving your changes.
• Done – Saves your changes and closes the Edit Record Filter window.
• Help – Displays the Query Manager Help window.
  
  

Edit Output Fields

This window is where you select the types of data that will appear on your report.

                                                   Figure 13-7

• Fields to pick from –Displays all of the currently available fields in the Data Dictionary that are not yet included.
  • Double-click on a field to add it to the list on the right.
• Fields to include in Query –Displays all of the currently included fields to be displayed in your Query Report.
  • Double-click on a field to remove it from this list.
  • You can change the order of the fields by "dragging" the button to the left of each field up or down with your mouse.
• Pick All – Marks all available fields to be included.
• Unpick All – Removes all included fields.
• Group – Allows you to set grouping wherein you can count records, add search results together, etc.
• Done – Saves your changes and closes the Edit Output Fields window.
• Cancel – Closes the Edit Output Fields window without saving your changes.
• Help – Displays the Query Manager Help window.
  
  

Pick Output Type

This screen is where you select the the type of report to use.

                                                      Figure 13-8

• Report –These output types are customizable and can be sent to the screen, to the printer or saved to a file.
  • The options can be configured after clicking Run Query at the Run Query window.
  • The "Browse records only" option is used if you do not want an additional report (no output type).
• Data Table –These output types are used for creating new DBFs based on Query results.
  • The file name must be specified in the lower-right hand corner.
• ASCII Text File –These output types are used for generating .txt files of various types.
  • The file name must be specified in the lower-right hand corner.
• Worksheet –These output types are used for creating Query results in proprietary formats, such as MS Excel worksheets.
  • The file name must be specified in the lower-right hand corner.
• File Name –Allows you to specify the name of the output file selected.
  • This is used for the "Data Table", "ASCII Text File" and "Worksheet" output types only.
  • It is recommended to use your "Local Files Path" (typically C:\BIZWIN) for saving these files.
  • It is recommended to leave the beginning of the file name as "QMF_", and only modify the remaining text. For example, "C:\BIZWIN\QMF_SALE" is a good name for a sales report.
• Done – Saves your changes and closes the Edit Output Type window.
• Cancel – Closes the Edit Output Type window without saving your changes.
  
  

Edit Sort Order

This window allows you to decide the order in which your data is presented. Note that some output types have a separate setting for this (such as the Report Form layout).

                                                       Figure 13-9

• Fields to pick from –Displays all of the currently available fields in the Data Dictionary that are not yet used to determine the sort order.
  • Double-click on a field to add it to the list on the right.
• Fields to sort records by –Displays the currently configured sort order.
  • Double-click on a field to remove it from this list.
  • You can change the order of the fields by "dragging" the button to the left of each field up or down with your mouse.
• Pick All – Marks all available fields to be included.
• Unpick All – Removes all included fields.
• Sort – Changes the sort order of the currently selected field (up or down).
• Done – Saves your changes and closes the Edit Sort Order window.
• Cancel – Closes the Edit Sort Order window without saving your changes.
• Help – Displays the Query Manager Help window.