This topic goes into more detail regarding the Ad-Hoc enquiry facility.
|
Click on the option in the box you want to know about:
|
|
Click the Verbs... button in the assistance box to allow you to select the verb required for this query. See the output types. The first column displays the command or verb, e.g. DISPLAY or PRINT. The second column displays the full description of the verb.
Select a verb by clicking it. This will highlight the line and then by clicking the OK button this will enter the verb into the formulate query box.
The second button in the assistance box allows you to select the fields to be used on the query. Once Fields... is clicked, it will display a Field Selection - Click in sequence order form in the centre of the screen.
This shows the list of fields belonging to the file that has been chosen for the query. The fields are listed in alphabetical order of the field name. If the query is using multiple files, the fields is in the same order but listed one file at a time.
A single character will appear next to each field description, it is either C, N or D and may have an asterisk next to it. The letters signify whether the field holds alpha characters, numbers or dates. An asterisk (*) next to a letter signifies an 'occurs' field.
The field name is made up of up to 8 characters. This is followed by a 20-character description of the field. Some of the field names are quite obvious, but some of them will need to be seen alongside the 20-character description to become fully obvious.
To select fields click on them.
Tip : Use the scroll down bar to display further fields which are not currently displayed in the window.
Each field selected is displayed in the formulate query box.
When all fields required have been selected click the OK button.
The fields are displayed in the order in which they were selected.
If any fields are no longer required then click into the formulate query box and delete them.
If the order of the fields is not as required, then use the DELETE key to remove the ones which are in the wrong place and then type the name back into the correct place once you have created enough space with the INSERT key.
The system will presume that 'Occurs' fields selected are the first occurrence. If a later occurrence is required, type (x) directly next to the field name (no space between), where 'x' = the number of the occurrence.
Key-paths have already been mentioned in this on-line help. They are simply indexes to the file. Each file can have up to nine indexes. They are used to provide easy access to the data by the various applications in the system. They are used in the enquiry system to produce the lists in a particular order without the need to sort the data.
If you wants to produce a list of Sales Ledger accounts in account code order, it could be done in two ways. The key word SORT could be used followed by an equals sign and the field name ACCOUNT, (SORT=ACCOUNT). This will cause the whole of the file to be searched, the required items to be selected and then sorted into account code order before either printing or displaying them. No output is seen until the whole file has been searched and the sorting has taken place. On a small file this will only take a few seconds, however it is not a very efficient way of doing it.
The Sales ledger account file has a key path which is in account code order, it is key-path number 1. Instead of using the Reserved word SORT we should use PATH1 which makes the system search through the file using the first index. This index is in account code order, which means that no sorting is required and the results can be displayed as the enquiry progresses.
The fourth button in the Assistance area is called If... By clicking this button it helps you to build a selection.
The And... and Or... buttons are used in conjunction with the If... button. These buttons are explained in more detail in Selecting records.
The last button in the Assistance box is called Commands. This option displays how each command is used, e.g. EXPORT, SORT, LABEL, NODUPS, etc. These are explained in more detail further on in this help file.
We have already seen how easy it is to select the fields you want to see on the query, but we should consider how we limit the number of records the query will include. The selection process enables us to show only those records that are important. In simple terms the selection process is invoked by use of the Reserved word IF.
In our previous two examples we have selected records with the following selection criteria.
ACCOUNT NAME BASETOTL CRDLIMIT IF BASETOTL GT CRDLIMIT
ACCOUNT BASEVAL BASEPAID [BASEVAL - BASEPAID]
IF BASEVAL GT 1000 AND BASEPAID LT BASEVAL AND DOCDATE LT 01/03/95
SORT BREAKON
Note : The following points are mandatory.
The selection is invoked by the word IF.
All fields to be displayed are typed before the word IF.
Individual parts of the selection are separated by the word AND, (OR can also be used).
Variables may be used instead of, or along with field names. (i.e. BASETOTL GT 1000)
Operators may be used in selection. The only operators allowed are:
(The 'contains' operator will not function with numeric or date fields.)
The variables used in selection may be text or numbers or dates. Dates may be entered as dd/mm/yy or dd/mm/ccyy format or a blank date may be referred to as zero. Thus to select a record which has no payment recorded IF PAYDATE EQ 0 would be the correct method to use.
Text fields may be enclosed in quotes if required. Thus ORIGIN EQ 'VS' is just as acceptable as ORIGIN EQ VS. However, if you want to make a selection based on the fact that a text field is empty, you must surround an empty space with quotes. Thus REFERENC EQ ' ' would be correct.
Any text which is included in selection is matched regardless of case. NAME EQ SMITH would select records with a name of SMITH, Smith, smith or even SmItH.
To make the selection of records easier the If..., And... and or... buttons in the Assistance box can be used.
After selecting the fields to be displayed or printed, you should click the If... button. The Build Selection form is displayed. You can either type in the field name, or click on the Ellipsis button. Select the required field by clicking on it and selecting the OK button or alternately double click on the highlighted line.
Tip : Click on the Ellipsis button
to search.
After a field has been selected, click in the Operator box, and a Drop Down button will appear. Click the Drop Down button and select the appropriate operator criteria by clicking on it or by using the <up> and <down> arrow keys.
Tip : Click on the Drop down
to select.
After selecting an operator, you must enter a comparison.
If more criteria are required for the selection then you can click the And... or or... buttons and follow the above process.
In simple terms the above points are complete. However they do not explain some of the options that can be employed to make the enquiry more versatile. The following examples may be used to make stored queries easier to use whenever they are loaded.
Case1. A query is constructed which looks for all transactions valued at over 1,000 pounds which have been raised today.
DISPLAY ACCOUNT DOCNUM DOCDATE BASEVAL IF DOCDATE EQ 01/03/99
This query will work well, but if it is going to be run on a regular basis it will require the date to changed each time it is run. It would be better to have the query always relating to the current system date. Although a date is normally entered in the format dd/mm/yy or dd/mm/ccyy, the Ad-hoc enquiries system will recognise input of --/--/-- as today's system date. Therefore try this example on your system.
DISPLAY ACCOUNT DOCNUM DOCDATE BASEVAL IF DOCDATE EQ --/--/--
When the query is formulated by the system it will show today's system date in the centre box instead of --/--/--.
Case2. The previous case is fine if you are looking for today's date. However, it is common to want to look at transactions which have taken place over the last few days. Let us take Case1 and adapt it.
Instead of IF DOCDATE EQ 01/03/99 we want the last seven days, so it would be IF DOCDATE GT 22/02/99.
DISPLAY ACCOUNT DOCNUM DOCDATE BASEVAL IF DOCDATE GT 22/02/99
Once more, this is fine for a one off query, but if it is going to be stored and used on a regular basis then try the following.
DISPLAY ACCOUNT DOCNUM DOCDATE BASEVAL IF DOCDATE GT [ --/--/-- - 7 ]
This derives the date seven days ago by taking today's date, signified by --/--/--, and then using the minus sign to take away seven. The derived calculation is called by surrounding the calculation with square brackets.
When you enter a query which displays several fields, they is formatted on the screen or paper starting from the left margin. Text and date fields are left justified beneath their headings while numeric fields are right justified. If you select more fields than can be fitted onto the format then the query is truncated. The system will display or print as many fields as possible but ignore the ones that cannot be printed. The 'attributes' box will show the word TRUNCATED to illustrate that this is about to happen.
If the fields will fit onto the format but the headings are too long, the headings are reduced in length by the system until there is enough space to display or print the query.
Any numeric fields which the system can total will show totals at the bottom of the print. Section totals will also be shown where the reserved word BREAKON is used in the query.
If no sort order is defined by the user then the data is displayed or printed in whatever order the records are found in the file. This is known as blasting through a file. There are two ways of providing a sorted output, either use the Reserved word SORT or use PATH.
Once a query is constructed, use the Reserved word SORT to arrange the data in an acceptable order to make it easier to read.
If the word SORT is used in isolation then it will sort the query by the first field on the query format line. In the following example the data would be sorted by the document date.
DOCDATE ACCOUNT BASEVAL BASEPAID
SORT
To sort the report into account code, follow the Reserved word SORT with the field name for the account code.
DOCDATE ACCOUNT BASEVAL BASEPAID
SORT=ACCOUNT
Notice also the use of the equals sign between the SORT and field name.
This will sort the report by account code in ascending order. To sort into descending order add the Reserved word BACKWARDS to the query.
DOCDATE ACCOUNT BASEVAL BASEPAID
SORT=ACCOUNT BACKWARDS
The field which is being used for the sort must be one of the fields being displayed or printed.
If the query uses one of the key-paths which is built into the file then no sorting is required. This makes the query quicker to run, and in many cases will make it very efficient as well. The command is used together with a key-path number. If the Sales ledger transaction file is used as an example we would find that it holds five key-paths. Most of them are primarily in account code order, within that they are sorted in date or document number order.
DOCDATE ACCOUNT BASEVAL BASEPAID
PATH2
This query would output the data in account code order, then by document date and then in document number order. Once more the ascending order is assumed. Use the Reserved word BACKWARDS to reverse the order.
As the information is being accessed in the key-path order, we can also cut the search short where the selection criteria match the key-path sequence. In the above case we will add selection criteria which limit the range of accounts to be selected.
DOCDATE ACCOUNT BASEVAL BASEPAID
IF ACCOUNT GT A0001 AND ACCOUNT LT C0001
PATH2
The system will only scan the part of the file which holds the account codes within the range specified. In this case it can cut out all items which relate to accounts after account C0001. The search is even quicker if only one account is specified, as long as the first field in the key-path is the account code.
DOCDATE ACCOUNT BASEVAL BASEPAID
IF ACCOUNT EQ A0001
PATH2
Imagine you are looking through a telephone book for Mr Smith. You would not look through the A,B,C section, you would quickly turn to the part of the phone book which deals with names starting 'SMI' before looking seriously at the entries, you will then stop looking when you get to names starting with 'SMO'. The system will make the same assumptions when looking through the file via a key-path with matching selection criteria.
If the Ad-hoc enquiries option is taken from the menu then you is allowed to select the file to use as a basis for the query. In this case it is the only file you can utilise for the query. Where there is an enquiries menu available, you may see several options on the menu as well as the Ad-hoc enquiries option. In the case of the Sales Ledger enquiry menu there is 6 options.
The first is Ad-hoc enquiries and the others are Account header, Transactions, Archived transactions, Payments, and Archived payments. The list of options in the Point-of-Sale enquiry menu is even longer.
The reason for these separate entries is either because they automatically load the popular files, or because they have been modified to allow you to access fields from more than one file.
We will remain in the accounts enquiry menu to explain this.
If the transactions file is used to formulate a query, all of the fields in the transactions file are available for use. However, there is no way that you can print the account holder's name on the query because the name of the account relating to the document is held in another file, in this case in the accounts header file. The accounts header file also holds other useful information such as contact names, phone numbers and credit limits. If the Transactions option is taken from the enquiry menu, you is allowed to use the fields from the header file as well as the transactions file, thus adding a further dimension to the query.
The Point-of-Sale module offers similar advantages. If the Parts lines option is used on the enquiry menu, you will also be able to include matched fields from the Point-of-Sale header file and the stock file. Thus customers' details and more detailed information about the parts issued can be included on the query. Users should experiment with these options to discover the advantages for themselves.
A query which is formulated using linked files will look slightly different as regards field names. Each of the field names will show a number as the first character, this indicates the file from which the field is situated.
Here is an example using the transaction file linked to the Sales Ledger accounts file.
1ACCOUNT 1DOCNUM 1BASEVAL 1BASEPAID [1BASEVAL - 1BASEPAID] 2PHONE 2CONTACT
IF BASEPAID LT BASEVAL AND DOCDATE LT [--/--/-- - 30 ]
There are several points to be made here. First of all the field names in the format section of the query are all preceded by numbers, although the number is actually only required if the field does not come from the first file. In this case the contact name and phone number are therefore the only fields which really require numbers in front of them. Notice that the field names used in the selection part of the query do not have numbers at the start of the name.
We see that there are two derived fields involved, one of them gives a simple balance between the amount of the invoice and the amount that has been paid, the second derived value is in the selection limiting the items to only those over 30 days old.
We have already used a couple of derived fields in the earlier sections in this topic. Let us look at them in more detail.
A derived field is a value which is arrived at by applying a mathematical calculation to one or more of the existing fields. Once the field has been calculated it is shown on the format using the image -#,###,###.## and will automatically be totalled at the end of each section and at the bottom of the report. The heading for the fields are DERIVED[1] to DERIVED[9]. Up to nine derived functions are allowed on each query.
A common use for a derived field is simply to create a counter for a query which is being totalled. Type [1] in the format line and the system will accumulate the number 1 until it reaches a total. It will then show the accumulation for that section thereby illustrating the number of records in each section of the query.
Derived fields are the only part of a query that the system may have a problem compiling. If it is unable to resolve your query, the command line may tell you that there is a compilation error and it will show you the problem on the screen. Look at the problem and see if you can recognise the field names, then go back to the query, amend any errors and try again.
Earlier in this topic a list of reserved words was shown. It is repeated here and then each word is discussed in detail.
Click on the option in the box you want to know about:
This word is optional. It is used to make the output of the enquiry display on the screen. The reserved word LIST is similar. If no output verb is used on the query then DISPLAY is assumed. The letter 'D' is sufficient.
This will print the query on the currently selected printer. you may just type in the letter 'P' which is interpreted as PRINT.
Use this word to limit the displayed report to just totals. None of the individual detail lines are printed. If the reserved word BREAKON has also been used, the totals for each section of the report is displayed. If BREAKON is being used in conjunction with TOTAL then a section counter should also be employed to show how many items there were in each section of the list. See the section which covers derived fields for details of a section counter.
To limit the printed output to totals only use the reserved word SUPPRESS.
The GRAPH function is simple to use yet powerful in operation. It will cause the output from the query to be formulated in graphical form. This in turn is a simple bar chart. The graphics server can then be used to format the graph into different styles.
You simply have to define a horizontal text or date axis and a numeric vertical axis to create a simple chart. Make sure that the text field is placed before the numeric field on the query line. The output is automatically sorted in ascending order of the horizontal axis field.
If the GRAPH command is used in conjunction with other reserved words, enhanced output can easily be achieved.
GRAPH DOCDATE BASEVAL WITH DATEYM IF DOCDATE GT 01/04/99
In the above example the trend of sales each month would be seen due to the system automatically sorting into date order. The DATE reserved word is used to show only month and year of sale.
A small change will show the number of transactions in any given date range instead of the value.
GRAPH DOCDATE [ 1 ] WITH DATEYM IF DOCDATE GT 01/04/99
If you try to graph too many items on the horizontal axis, the message 'Graph has overflowed' is shown and the balance of the un-shown entries will all be put into one column marked with question marks at the bottom of the column. Reduce the number of entries found by the query to correct the problem, or perhaps consider using the Export command to send the output to a spreadsheet which has more flexible graph parameters.
This output type is useful for producing mail shot labels, or maybe for producing labels for part bins in the parts department. It is commonly used with the reserved word DIMENSION which specifies the size of the label.
If label is used without DIMENSION, it will produce labels in a single vertical line down the page.
LABEL NAME ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 POSTCODE IF POSTCODE CO RG
The depth of the label to be printed is only controlled by the number of fields being printed. In the above example six lines are assumed.
The Label command will always give you a chance to align the label stationery prior to printing it.
The EXPORT reserved word is used to output the results of the query to a text file on the server. The output is written as a .prn (or print) file, unless the reserved word CSV is used. Once it has been written, the system will offer you a chance to export it to DOS and then delete the file from the server.
EXPORT ACCOUNT BASEVAL BASEPAID DOCDATE IF DOCDATE LT 01/02/99
/tmp/oldinv
In the above example you is able to create a text file with four fields stored against each record included. Notice the output directory on the server is specified, also it is specified in lower case. This is very important for a unix system as file names and directory names are case sensitive and must be named accurately. Also notice that the slash used on the path name is a forward slash, this is the opposite direction to a slash used in DOS.
If the directory specified does not exist on the server then it will be created for you, however it is advisable to get guidance from your system manager if you are in any doubt about which directory to use. The file written in this case is oldinv.prn (the .prn extension is assumed if none is specified). The file name together with any extension must be under 14 characters long otherwise the file will not be written.
Once the file is written, a pop up window will appear offering the following choices; Resume, View-file, Print-file, Download-to-PC and Delete-file.
The Resume option will take you back to the Ad-hoc enquiries screen. The View file option enables you to view the text file on the screen while Print file will print the contents of the file to the currently selected printer. The Download to PC option enables you to transfer the file to DOS after typing in a directory name so that the system knows where you wants to put it. Once the file has been transferred to PC, you may choose to use the Delete file option to prevent too many files being left in the directory used to export files.
The format of the output can be varied with the export command. Normally the attributes box at the bottom of the screen will show FORMAT=IMAGE. This means that the text file will contain an exact print image as if you were printing it to paper. If the reserved word SUPPRESS is used, the headings and totals are excluded from the file and only the columns of data is exported. This provides a clean file for uploading into a spreadsheet. If the CSV file type is specified SUPPRESS is automatically applied.
The Spread option is an alternative to Export which may be used in conjunction with Microsoft Excel. To use the function successfully, Excel must be open before the enquiry is run. The output from the file is written to the Excel spreadsheet SHEET1.
The operator EQ which means equal may also be represented by an equals sign, '='. It is used in selection criteria. The EQ function is literal so, if used, only text or numeric fields which are precisely equal to the specified value is selected.
The NE or 'not equal' operator can be represented by the symbol '<>'. It is used in selection criteria.
The greater than and less than commands can also be represented by '>' and '<' symbols. They are not inclusive, therefore a field which must be 'GT 1000' will not include values of 1000.
The 'greater or equal' and 'less or equal' commands may be represented as '>=' and '<='. They are used in selection and are inclusive, therefore a field which must be 'GE 1000' will include values of 1000.
The 'contains' function is used in selection with text fields only. A selection of 'NAME CO SMITH' will select and record where the name field contains the five characters 'SMITH' in any position whether they are in upper or lower case.
The 'AND' command is used as a connective verb within selection criteria to connect individual items in the selection criteria and make one joint selection logic. Thus NAME CO SMITH AND BASETOTL GE 1000 would only select items where both SMITH is present and the value if 1000 or more.
This provides an alternative selection logic. Thus NAME CO SMITH OR BASETOTL GE 1000 would result in all records with SMITH in the name field being selected as well as all records valued at 1000 or more.
The FORWARDS command is used in conjunction with the reserved words PATH and SORT. It should be pointed out that a forward SORT or PATH access is assumed so it is not often used.
This is used in conjunction with the reserved words SORT and PATH in order to reverse the order of the sort or record access. SORT=DOCDATE BACKWARDS would sort the data in document date order showing the latest date first and the earliest date last.
This is the default sequence of all enquiries and will always be used unless the SORT or PATH reserved words are used. It simply means that the records are output in the order they are found in the file.
The PATH command is used to force the enquiry to access the data via one of the key-paths on the file. See the earlier section on sorting the data for an explanation of the PATH command. The PATH reserved word should always be followed by a number to signify the path number to be used.
This will sort the query by the first field named in the query. The sort will always be ascending unless otherwise specified by the BACKWARDS command.
The SORT command will sort the data by the first field named in the query. The sort is ascending unless otherwise specified by the BACKWARDS command. If the SORT command is followed by an equals sign and a field name, the query is sorted by the field specified.
Thus SORT and SORT=BASEVAL are both acceptable.
The STOPAT command will limit the output to a specified number of records. STOPAT50 will only display or print 50 records. The number should be input immediately after the STOPAT command.
Thus STOPAT100 is allowed but STOPAT 100 is not allowed because there is a space between the reserved word and the specified number.
Be warned that the STOPAT command will limit the number of records displayed or printed but the output will vary depending on the sort order of the report.
If the output is not sorted in any way then STOPAT10 will display the first 10 records on file.
If output is sorted, the STOPAT10 enables the whole file to be searched and the first 10 sorted items are shown.
If the PATH command is used then STOPAT10 will show the first 10 items in the path.
Note : This is a useful option to employ on a temporary basis when you are developing and testing a query which has to work on a large file. Once the query appears to be working as expected, the STOPAT function can be removed.
The DIMENSION command is used in conjunction with the LABEL option and is used to specify the size of the labels to be used. The format for the DIMENSION command is as follows.
DIMENSION<LL,WW,DD> where LL specifies the number of labels across the page, WW shows the width of the label as a number of characters and DD specifies the depth of the label as a number of lines.
Thus DIMENSION<04,35,09> would specify 4 labels across the page, each label allowing 35 characters between the first character on the first label and the first character on the second label horizontally, and 9 characters between the first line on the first label and the first line on the second label vertically.
The DATE command is used to specify how dates are represented within the output of the query. The following date formats are allowed.
The SUPPRESS function is used to prevent the display or print of detail lines from the query and only produce the totals. If the BREAKON function is utilised in the same query then the totals from each section of the output is shown.
DISPLAY ACCOUNT BASEVAL BASEPAID
SORT=ACCOUNT SUPPRESS BREAKON
In the above example the contents of the transaction file are being displayed and the output is sorted into account code order. The individual format lines are being suppressed but as BREAKON is set, there is one line for each account showing the account code, the total of the invoices and the total amount paid.
The CSV command is used along with the EXPORT function to create a comma separated variable file instead of the standard .prn or print file. People wanting to output to spreadsheets often prefer to use CSV instead of .prn in order to get values into individual columns in the spreadsheet without having to parse the data.
This function will suppress the output of duplicate records. A duplicate record is classified as one which has the same sort field as the previous record output. Thus if records were sorted by registration number, only one record for each registration number would be printed.
It is very handy for printing labels for items in a file where only one label for each item should be printed no matter how many times they appear. Take the example where a mail shot is aimed at all people who have hired vehicles from you in the last three months. The records in the rental agreement file could be sorted by licence number and then the NODUPS reserved word applied. Only one label for each customer would be produced no matter how many times they had hired vehicles from you in the specified period.
This are used together with the SORT command to provide breaks and sub-totals for each change of sort key. Thus if the account code were being used to sort the date, the output would break every time the account code changed. Any numeric fields which can be totalled is shown at the total line.
This command is used instead of Breakon to provide a page throw at each break point instead of just a simple break within the page. It is not particularly useful on any output type except print.
The Campaign option is used to assemble records which can be used as a part of a marketing campaign. Full details about using campaigns can be found in the Marketing manuals. The reserved word CAMPAIGN must be followed by an equals sign and a three-character code. This code is used as the campaign name and will identify the file that the Ad-hoc enquiries option will write for the campaign to use.
If queries have been previously stored, they can be recovered by clicking the Load button. Queries are stored in two ways, either as user-specific or global queries. The queries are stored to be used against whichever file is currently selected. As such, a query stored for use with the Sales Ledger accounts file will not be available for use with the Sales Ledger transactions file.
User queries will only be available to one person, global queries are available to all users.
You should decide if the query to be loaded is a global query or a user query. If it is a user query then you should click the User radio button. After clicking the Load button, a small window will pop up on the form displaying a list of all user queries previously stored for use with the currently selected file is displayed. If there is only one query then it is displayed automatically. If there are no previously stored user queries, the form will not appear.
A query which was stored as global can be accessed by all users. There can be up to 32 of these stored for each file. You should click the Global radio button and then click the Load button. A small form will pop up displaying all 32 query slots and allow selection of any query on the list.
Once selected the stored query is displayed on the main query screen.
Once the query has been written and proved successful, it may be stored in one of two places. A query may be stored as a global or a user query. Up to 32 global queries can be stored for each file used. These are available to all users. If you want to store a query which is particular to you then you may elect to store it as a user query, this will not be available to other users of the system.
To save a query you must decide if you wish to save it is a user-specific query or a global query.
If you decide to save a global query, then click the Save global button and the screen will show you a form with 32 query spaces on it.
Select the line of you choice and then select the Name of global query box. Now type in a name to describe the query and click the OK button. The query has now been saved to the specified position number.
If you decide to save a user query, then click the Save button, a window will pop up and ask you to enter a description for this query. The maximum length of the description is 22 characters. The system will default to save the query for you only. However, you can save a user query as Shared and then it will display as a user query for all users within the company. To do this click the Shared radio button. Click the OK button and the query is saved.
Once a library of saved queries is built up, they can be loaded at any time by clicking on either the User or Global radio button, depending on the type of query required, and then clicking the Load button. The first will give you user queries while the second will show the list of up to 32 global queries
Once a query is selected it is displayed in the query box and may be run immediately or edited before running it.
User queries can be deleted. To do this, load the query by the usual method. This will display the query on the form and enable the Delete button. Click the Delete button and the command line will inform you that the query has been deleted.
For this example we are using the Sales Ledger transactions file. In this we will look at all of the transactions which we have on file that are dated before 01/09/2000 and are valued at over one thousand pounds which are not fully paid. In order to make the report more readable we will sort the listing by account code and display totals for each account.
Select the transactions file from the list of files and type the following:
ACCOUNT BASEVAL BASEPAID [BASEVAL - BASEPAID]
IF BASEVAL GT 1000 AND BASEPAID LT BASEVAL AND DOCDATE LT 01/09/00
SORT BREAKON
Once the text is typed in click the Check query button. If the system's interpretation of the query looks correct, and the Proceed button is enabled then click it. Otherwise re-edit the query to correct any mistakes.
There are three conditions to this query. Only records which satisfy all three of the conditions are included in the query. There is also a calculated field, (known as a derived field), which is the sum enclosed in square brackets. Finally we are sorting the list into account code order using the SORT command and making the system break between accounts by using the BREAKON function.
Ad-hoc frequently asked questions
