This topic will outline the use of the Ad-hoc enquiries program which appears in all reports and enquiries menus.
Ad-hoc enquiries is a simple yet powerful feature which allows users to construct queries and display, print or graph the result of the queries. Once the query has been formulated, it can be saved for future use. A query can be saved as a personal item or as an enquiry which is globally available for all to use.
An enquiry is formulated by completing a simple set of verbs and field names. There are some special features available to you, all of which is explained in this topic.
An on-line help function is also provided which allows you to either display or print the Ad-hoc help dialogue.
Earlier in this on-line help we explained that the system functions are split into Modules. The Sales Ledger is a module. Daily Rentals is a module, Marketing is a module.
Each of the modules on the system has a set of menus which follow a certain pattern. They will all have a main module menu. This holds most of the record maintenance functions and allows the module to be used by a normal user.
There is other menus which are accessed from the main module menu. The most common of these being the Miscellaneous menu, the System Maintenance menu and the Reports and Enquiries menu. The Miscellaneous, and System Maintenance menus are normally a high priority and may not be seen by some users. The Report and Enquiries menu however is a lower priority and accessible to most people.
The Ad-hoc enquiries option is available on the Reports and Enquiries menu. It is represented in one of two ways depending on the module selected. Two examples are given here.
In the first place the Sales Ledger enables you to enter an Enquiries menu. Ad-hoc enquiries is then available on this menu in two ways. You may take the Ad-hoc enquiries item on the menu and then select the file to be used by the enquiry.
On the other hand you may take the Account header option which will load the Ad-hoc enquiries program and automatically select the Account header file as the one to be used for the enquiry.
Other entries on the menu enables you to make an enquiry using multiple files. An example is the Transaction file option, which will allow you to search though the Sales Ledger Transaction file while also having access to associated fields in the Sales Ledger Accounts file. There are a number of these multiple file enquiry options on the Enquiry menu. You should experiment with them in order to find out which ones are most useful to them.
See linked files.
Other smaller modules will not have a specific Ad-hoc enquiries menu, they will simply show the Ad-hoc enquiries option as an item on the Reports and enquiries menu. Where this is the case, you will always need to select the file to be interrogated.
Tip: Selection of the correct file for the enquiry is paramount and should be considered carefully.
It will take a little time for you to become familiar with the data recorded on the system. It will also take time to become aware of the location of the data required for the enquiry. In general terms only a small number of files are used on a regular basis for user based enquiries.
The system will show a list of the possible files as soon as the Ad-hoc enquiries option is selected from the menu. You may choose from any of the files on the list.
This on-line help is not the right place to detail which fields are found in which files. System experience and a little experimentation will soon provide you with the practical knowledge required to find the right information. In this topic we is using the Sales Ledger files to illustrate use of the Ad-hoc enquiries software.
You should remind yourself of the phrases which is used in this topic. Some of them have already been outlined in the topic AUTOLINE Help Topic Conventions.
Module: A module is made up of a set of files, all of which relate to one subject. All files which relate directly to the Marketing function is stored and accessed in the Marketing module.
Files/Tables: A file or table is stored within a module. The files/tables hold the records which relate to a particular function within that module. A Sales Ledger transaction file/table for example will hold all transaction records, (invoices and credit notes), for the Sales Ledger.
Record: Records are stored in a file/table. A record is made up of a number of individual fields, each of which hold a piece of information belonging to that record. A Sales Ledger transaction file/table for example may hold the following fields:
and many others
Fields/Columns: A record is made up of a number of fields, or columns. The small list shown above is an example of the sort of fields/columns you may expect to find in a Sales Ledger transaction record. Fields/columns are one of three types, Numeric, Date or Alpha. Numeric fields/columns can only contain numbers, date fields/columns hold a day number and alpha fields/columns hold either letters or numbers.
'Occurs' fields: Any fields could be defined by the programmer as an 'Occurs' field. This simply means that one field name is required in the place of many. A good example of this is an address. If each line of the address is going to be stored separately then several different field names are required to tell them apart:
If the address is stored as an 'occurs' field, the field name is ADDRESS(x), where x is a number from one to five.
Key-paths: A key-path is an index. The key-paths are used by the system to allow you to look up a record in an efficient manner. A file may contain up to nine key-paths giving you many different ways of accessing records within a file.
When Ad-hoc enquiries is selected from the menu, you is invited to select the file to be used in the enquiry. Make the selection as required using the form in the manner described in an earlier topic.
Once the file has been selected, the Ad-hoc enquiries main form will appear. The form is split into four main areas.
The query is typed into the top formulate query box. As soon as it is entered, the interpretation section in the execute query box will display the systems view of the query, and the attributes section will show the attributes or characteristics which is applied to the query and how the data is accessed.
The formulate query box will present the word DISPLAY. This may be changed. You can type in a query or you can use the buttons in the assistance box to formulate the query.
To finish and return to the previous menu you must click the Exit button.
The centre box is used by the system to display what it thinks you is trying to say, and the bottom box is used to display system information relating to the way the data is going to be presented.
The name of the presently selected file is displayed on the banner line at the top of the form.
Construct the query by entering a verb, e.g. DISPLAY or PRINT, followed by a list of fields, each of which must exist in the selected file's dictionary. The query may be selective by using relations, e.g. = or <>, and connectives, e.g. AND or OR. Any words not recognised is ignored, any incomplete or ambiguous logic is rejected. All words must be separated by spaces.
The query field is not case sensitive.
Where possible the software will accept queries which have been entered almost in plain English. There are a number of words with which you iscome familiar in order to direct output or to sort the data into a particular order before displaying or printing the results of the query. Take as much space as necessary to type the query in. Normal text editing keys may be used when amending previously typed in data.
The system will ignore words it cannot understand. The words it does know are split between field names and enquiry Reserved-words. Apart from the field names these are the words that the system considers to be Reserved-words.
|
DISPLAY |
|
TOTAL |
|
GRAPH |
LABEL |
EXPORT |
|
SPREAD |
EQ |
NE |
|
GT |
LT |
GE |
|
LE |
CO |
AND |
|
OR |
FORWARDS |
BACKWARDS |
|
BLAST |
PATH |
SORT |
|
SORTED |
BREAKON |
STOPAT |
|
DIMENSION |
DATE |
SUPPRESS |
|
CSV |
NODUPS |
CAMPAIGN |
|
PAGEBREAKON |
|
|
Before we detail the Ad-hoc features in full, the following simple example will show how easy it should be to use Ad-hoc enquiries. If you have access to the Sales Ledger module you may wish to type the example in and see the results on your own form.
The file selected should be Sales Ledger Accounts. If taken from the Sales Ledger Enquiries menu, it is the one called Account Headers. We are going to display the account code, the account name, the outstanding balance and the credit limit if the outstanding balance is greater than the credit limit.
Type in the following, making sure there is a space between each word. The system enables continuous typing and will automatically display text on the second line when it reaches the end of the first line. A word must not be split between two lines.
ACCOUNT NAME BASETOTL CRDLIMIT IF BASETOTL GT CRDLIMIT
Once the text above is typed in click the Check query button.
The first Execute query box on the form will show how the system interprets your query. If it looks correct, and provided the system understands what you have typed in, the Proceed button should be enabled. If this is the case then click the Proceed button.
If there are errors and the system cannot compile the query a box will appear displaying the message Unable to compile query. You can then choose to Resume and then correct any mistakes or View errors.
Once you have clicked the Proceed button, the system will scan the accounts header file and display any records which match the parameters just input. The command line will show how many records have been accessed, how many of those have been printed. If a scroll down bar is displayed then there are more records than those currently displayed on the form. You should use the scroll down bar to display more records.
Before printing anything from the system, you should make sure that the correct printer is selected. While you are on a menu, the bottom left hand corner of the form will display the name of the currently selected printer. Use the Change printer option if a different printer is required.
The example just described can now be typed in again, the only difference is that the first word typed should be PRINT. The query will now be produced on the selected printer instead of the form.
It is often worthwhile displaying the results of a query before printing it in order to make sure that the right number of records are going to be selected. As long as the expected results are achieved, you can then insert the word PRINT and re-produce the listing in the knowledge that the results are as expected.
A good shortcut to remember here is that the letter 'P' will work as well as the word PRINT. Both is interpreted by the system as a request to print the query instead of displaying it.
A query is displayed by the system unless you specifically request an alternative type of output. The full list is as follows.
Display: This is the default output type. The word DISPLAY or the letter 'D' may be entered as the first word in the query, although if omitted, and in the absence of any other, the query will be displayed in any case.
Print: Enter the word PRINT or the letter 'P' at the start of the query in order to produce the output on the currently selected print device.
Total: Use this command to display one line of totals only. Any alpha fields included on the query will not be printed.
Graph: This will present the results in a graphical format. See the Graph option.
Label: This will produce the results on the currently selected printer in label format. See the Label option.
Export: This will export the results of the query into the file in the Unix environment. The system will then offer you the chance to export the resulting Unix file into DOS in the form of a .prn or print file.
Spread: This option will output the results of the query straight out to an open Excel spreadsheet within the windows environment using the DDE protocol. Microsoft Excel works particularly well with this function.
Raw: This option will scan the raw file very fast but in no particular order.