.PN=62 .OO=15 .EO=7 .OF=//II-# .EF=II-# .OH=Working with Superbase P„r„o„c„e„s„s„ „U„p„d„a„t„e„ .EH=P„r„o„c„e„s„s„ „U„p„d„a„t„e„ Working with Superbase U‚S‚I‚N‚G‚ ‚T‚H‚E‚ ‚P‚R‚O‚C‚E‚S‚S‚ ‚M‚E‚N‚U‚ The main database processing functions are contained in the Process Menu. It provides all the facilities for managing and manipulating your data, ranging from simple mailing label printing to complex multi-file queries and updates. T„h„e„ „F„i„l„t„e„r„ All the Process menu functions make use of the Filter to determine which records are to be processed. The Filter concept is described in detail in G‚e‚t‚t‚i‚n‚g‚ ‚t‚o‚ ‚K‚n‚o‚w‚ ‚S‚u‚p‚e‚r‚b‚a‚s‚e‚, so you should refer to that section if you need to refresh your memory. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ UPDATE The Process Update option allows you to make changes to individual fields in your files. It is a multi-file option, like Query, and you can use records in one file to determine what happens to fields in an other. Since multi-file options are more complicated and more powerful than single file options, and since Update can by definition alter the data in your files, we recommend that you gain a good understanding of the Superbase system before starting to use the option. Also, you should be sure to make a backup of your data, using the CLI or a Workbench utility, before updating it. Update has two requesters: one is a Filter, similar to the Filter you have used from the Control Panel: and the other is a Fields requester, also similar to the Filter but with fewer selection buttons. P„r„e„r„e„q„u„i„s„i„t„e„:„ „O„p„e„n„ „F„i„l„e„s„ Update can use only the open files, so if you want to alter or refer to a field in the Update Command Line you must first make sure the file that contains it is open. Use Project Open File to do this. Also, make sure you close any files that you are not going to need using Close File from the Project menu. S„e„l„e„c„t„i„n„g„ „U„p„d„a„t„e„ Highlight the Process Menu and select Update. Superbase displays the Update Filter requester. S„e„l„e„c„t„i„n„g„ „F„i„l„e„ „a„n„d„ „F„i„e„l„d„s„ The Filter is like the Control Panel Filter, except that the File Name Box at the top of the requester has an upward pointing arrow at its right end. This arrow enables you to display the fields from all the open files in turn. Each time you click on it, the fields from the next open file are displayed in the Field Name Panel below. If you go on clicking, the list of files cycles back to its beginning. C„r„e„a„t„i„n„g„ „t„h„e„ „F„i„l„t„e„r„ „C„o„m„m„a„n„d„ „L„i„n„e„ This is done in the normal way, by clicking on field names and operators, and entering values in the Value Box. Superbase copies the results of your selections into the Main Box below. You can edit the Main Box, but since this increases the risk of errors, you should only do this when you have some experience of the system. The Filter Command Line has a maximum length of 512 characters and can contain any combination of field names from the open files. However, when you are updating one file on the basis of the contents of another, you must specify a link between the files: Cust Number.Customer = Cust Number.Invoices This would ensure that the customer file will only be updated with values from the invoices file when the Cust Number field in a record in the Invoices file is EQUAL TO the Cust Number field in the Customers file. Of course in an example like this, you would probably include other conditions in the Filter Command Line to limit the updating by date or some other factor. When you have created the Filter you want, click on OK. Superbase then displays the Update Fields requester. T„h„e„ „U„p„d„a„t„e„ „F„i„e„l„d„s„ „R„e„q„u„e„s„t„e„r„ In this requester, you specify the precise updating actions you want Superbase to perform on certain fields. Typical updating requirements might be: * Setting a numeric field to zero. * Adding transaction amounts to balances. * Setting a status indicator field to a new value. Each updating action is defined the same way. An example: Status = "Y" This sets the Status field to the value "Y", irrespective of its previous value. Another example: Balance = Balance + Amount Here the Balance field is mentioned twice. This has the effect of adding the Amount field to the Balance, instead of replacing the old value of Balance with the new value of Amount. The third example increases the Amount field by ten percent: Amount = Amount * 1.1 C„r„e„a„t„i„n„g„ „t„h„e„ „U„p„d„a„t„e„ „F„i„e„l„d„s„ „C„o„m„m„a„n„d„ „L„i„n„e„ You start by clicking on a field name. Superbase copies it into the Main Box, and automatically adds an EQUALS sign operator. Next you click on another field name, or in the Value Box, where you type in the value you want, remem- bering to press Return as you finish. The maximum length of the Update Fields Command Line is 512 characters. U„s„i„n„g„ „t„h„e„ „C„o„l„o„n„ „S„e„p„a„r„a„t„o„r„ Each time you enter an update expression, you must set it off from the previous one by clicking on the Colon button. This inserts a colon in the Command Line. If the examples above were all in one line, they would be separated like this: Status = "Y" : Balance = Balance + Amount : Amount = Amount * 1.1 C„o„m„p„l„e„t„i„n„g„ „t„h„e„ „U„p„d„a„t„e„ „F„i„e„l„d„s„ „R„e„q„u„e„s„t„e„r„ Click on OK to finish the Command Line and initiate the Update processing, or click on Cancel and Clear to stop the process. .OH=Working with Superbase P„r„o„c„e„s„s„ „Q„u„e„r„y„ .EH=P„r„o„c„e„s„s„ „Q„u„e„r„y„ „ Working with Superbase ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ QUERY: THE HEART OF SUPERBASE The Query function is the most sophisticated of Super- base's menu options. It allows you to produce formatted output from one or more files, with a great degree of control over both the selection and the presentation of record data. You can select from a wide range of Report features for the Query. You can specify a title to appear on each page. You can request totaling, subtotaling, averages, and counts for any field. Groups of fields can be repeated, and you can choose which fields are to appear on each line. The Filter, which you have encountered in many other Superbase functions, is also a part of the Query. The main difference is that the Query Filter allows you to include fields from more than one file in the Filter Command Line. Query output can be sorted into any order. You can include several fields in the Order part of the Query, to create reports with many levels of grouping and subtotaling. Any sort field can be either ascending or descending. Finally, the output from a Query can be directed to one of four possible destinations. The screen, the Printer, a formatted ASCII disk file, or a new database file. P„r„e„r„e„q„u„i„s„i„t„e„:„ „O„p„e„n„ „a„n„d„ „C„l„o„s„e„ „F„i„l„e„s„ The Query function uses the set of open files, so you need to decide which files you want to include, before you select Query from the menu. You can quickly check which files are open with System Status System. If necessary, c†l†o†s†e† †a†n†y† †f†i†l†e†s† †w†h†i†c†h† †a†r†e† †n†o†t† †n†e†e†d†e†d†,† †a†n†d† †o†p†e†n† †a†n†y† †w†h†i†c†h† †a†r†e†, using the Open File and Close File options on the Project menu to do this. P„r„e„r„e„q„u„i„s„i„t„e„:„ „O„p„e„n„ „F„i„e„l„d„s„ You can output either the open fields for the current file or a set chosen just for the query. If you want to use the currently open fields you must select the set you want before you select Query. Use Project Open File and Project Open Fields to do this. S„e„l„e„c„t„i„n„g„ „t„h„e„ „Q„u„e„r„y„ „O„p„t„i„o„n„ Highlight the Process Menu and select Query. Superbase displays the Query Definition requester. Q„u„e„r„y„ „D„e„f„i„n„i„t„i„o„n„ „R„e„q„u„e„s„t„e„r„ The requester has three parts. At the top is the Title Box, below this is the Query Command Panel, and at bottom left you will see three buttons for selecting the destination for the output from the query. Query Definition works by accepting your selections and typed input through four requesters linked to the Fields, Report, Filter and Order buttons next to the Query Command Panel. Superbase then searches the file or files and outputs any records that match your Filter values. T„i„t„l„e„ „B„o„x„ The Title Box has three parts: Date Selector, Text Entry Box, and Page Number Selector. If you make any selections or enter any text, Superbase will print the results as a Title at the head of each page or screen of the query output, followed by a blank line. If you don't use the Title Box at all, Superbase starts the query output at the top of the page. If you click on the Date Selector, the Title will include the system date, as shown in the Date Format requester on the Set menu. The date is always aligned with the left margin. Superbase highlights the Date Selector when it's active. To enter the text you want for the query output title, click in the box between the Date and Page Number Selectors, and start typing. Superbase automatically centres the text in the box as you type. The maximum length is 80 characters. If you click on the Page Number Selector, the Title will include a page number. The page number starts at one and increases by one every time it's printed. The page number is always aligned with the right margin. Superbase highlights the Page Number Selector when it's active. The margins and page length for the query output are set from the Preferences system utility. Q„u„e„r„y„ „C„o„m„m„a„n„d„ „P„a„n„e„l„ The four lines of the Query Command Panel do most of the work of the Query option. Each line defines one of the elements of a Query: output fields, report features, filter conditions and sorting order. The maximum length of the Query Command Line for Fields, Report and Filter is 512 characters. When you open a previously saved query from the Project Menu, Superbase copies its details into these four lines. You can then click on OK to use it, or edit any of the fields directly. You do not have to use all four lines. For example, to produce a sorted report that prints just the open fields for all the records in the current file, you would only use the Order line to specify the fields to sort on. F„i„e„l„d„s„ If you want to output just the open fields for the current file, leave this line blank. Don't click on the Fields button; go on to the next section. If you do want to select a set of fields for the query output from the open files, click on the Fields button. Superbase displays the Query Fields requester. If you want to output the Open Fields List as well as those fields you select using the Fields button, type "*" at the beginning of the Filter Command Line. The Query Fields requester resembles the Filter requester, but has several important differences. It's purpose is to allow you to select fields from any of the files being used in the query, and apply certain operators and parameters to them. Before looking at these you should understand how to select a file. S„e„l„e„c„t„i„n„g„ „F„i„l„e„ „a„n„d„ „F„i„e„l„d„s„ At the top of the requester is the File Name Box, containing the name of the first open file. The fields in the Field Name Panel below belong to this file. You can select any field by clicking on it in the usual way. If you want to select a field belonging to another open file, you must first click on the upward pointing arrow to the right of the File Name Box. This action selects the next open file, and displays its name and the field names that belong to it. You can cycle through all the open files by repeated clicking on the arrow. Superbase checks to see whether a field name is unique across all the open files. If it is not, Superbase appends the file name automatically: Lastname.Addresses. For simple queries, you just click on the field names you want, and then on OK. But if you want to add features to your query output you must use the special operators and options to the right of the Field Name Panel. D„e„r„i„v„e„d„ „C„o„l„u„m„n„s„ A derived column in the query output is one in which the data is generated from a calculation, usually involving an existing field or fields, one or more arithmetic operators, and numeric variables. Examples are: Multiplication of fields: Quantity * Price Fixed decimal places (no rounding): FIX(amount/3,2) Percentage increase of 15%: Price * 1.15 Percentage decrease of 8%: Price *.92 Future date: Date + 30 Margin: Sales - ( Cost + Commission ) The usual set of arithmetic operators is available. To set up a derived column, click on each of the elements of the calculation in turn, except for actual values, which you should enter into the Value Box, pressing Return to enter them. A derived column that produces a numeric result is output in the default numeric format, which is specified with the Number Format option on the Set menu. Likewise, a derived date column is output according to the format specified with Date Format on the Set menu. The data in a derived text column is output at it's actual length, which means that following fields will not be regularly positioned unless you specify a fixed length for the derived text or a fixed column position for the next column. A„S„ „H„e„a„d„i„n„g„ When you specify a field as part of the query output, Superbase normally prints its name at the head of its column on each page. But this is not possible for a derived column, which has no name of its own. Consequent- ly, Superbase allows you to enter a piece of text as a special heading. This can be done for both derived and normal columns. After specifying the field or calculation, click on the button for AS heading then type the heading you require into the Value Box and press Return. AS will stay highlighted until you press Return. Examples: Sales - ( Cost + Commission ) AS "Margin" Lastname AS "Name" You may enter any piece of text. Positioning is done with the @ Position option. If you're using Query to create a new database, you must make sure that the headings for any derived columns are valid field names -- legal characters only and not more than 15 characters in length. @„ „P„o„s„i„t„i„o„n„ If you don't specify column positions for the fields, Superbase will output the field data for each record in a line, with each field appearing right after the previous one. Control over the exact positioning of data is obtained by clicking on the button for @ Position, and entering the desired column in the Value Box. You do this before clicking on the field name. For example, to print Lastname at column 12, you click on @ Position, then in the Value Box, where you type in "12" (no quotation marks), and press Return. Then you click on Lastname. Superbase shows this in the Main Box as: @12 Lastname We suggest that you try out column positions on screen and then adjust them before starting a lengthy report. Overlapping fields can cause problems. Typically, this occurs when you tell Superbase to print a field at a column position which has already been used by the preceding field's trailing spaces. Superbase's response in this situation is to print the field at the correct column position on the next line (see & Length, below). The maximum column position is 255. Output is forced to the next line if required by Preferences margin setting. &„ „L„e„n„g„t„h„ The solution to the problem of overlapping fields on a line is to limit the number of characters output for each field. You do this by clicking on the button for & Length, and then entering the desired number of characters in the Value Box. You do this before clicking on the field name. For example, to set a length of 12 for Lastname, click on & Length, then click in the Value Box, where you type 12 and press Return. Then click on Lastname. Superbase shows this in the Main Box as: &12 Lastname If you want to output two fields with just a space in between, you must use the following technique: &20 Forename + " " + Lastname AS Name This prints the actual length of Forename, and ensures that the next column will be properly aligned. Notice that as this is a derived text column a separate heading is specified. N„e„w„ „L„i„n„e„s„ You can start a new line in the output at any point by clicking on the LINE button. Superbase places NEWLINE symbol in the Main Box. If you want a blank line in the output you may need to click on LINE twice, depending on the actual print position. O„N„ „F„i„l„e„ Superbase is a relational system. This means that you should find it easy to store data in separate files and create relationships between them using this Query function. In order to see this, import "deposits import" into the Deposits file, and open the Addresses File. These two files can be related to each other, so you can output details from both files using the Lastname field as a link. The link is set up in the Query Filter requester, which is explained later. Outputting data from more than one file raises a special problem of formatting. The ON File option is designed to solve it. The following example is only intended as an illustration of the technique. A typical query output using the Addresses file and a Deposits file with several deposits for each person might have a Fields line like this: Lastname.Addresses, Street.Addresses, Date.Deposits, Amount.Deposits This line would produce a report like this: Hansen 7 Worregarde 12 May 1984 12,000 Hansen 7 Worregarde 6 Jun 1985 2,200 Hansen 7 Worregarde 23 Sep 1985 750 Hansen 7 Worregarde 24 Oct 1986 1,000 Foster 439 Oulette Ave 8 Jan 1983 450 Foster 439 Oulette Ave 28 Apr 1984 9,000 Foster 439 Oulette Ave 14 Feb 1985 600 Too much data is repeated. What is needed is a way of telling Superbase which details to print for each file. ON file does exactly that. When you click on ON File, Superbase copies the name of the file that's in the File Name Box into the Main Box. Now, all the field names that come after the file name will be printed only when a record from that file is printed, until you place another file name in the line. The Fields line would look like this: ON "Addresses" Lastname.Addresses, Street.Addresses, ON "Deposits" @27 Date.Deposits, Amount.Deposits The report would look like this: Hansen 7 Worregarde 12 May 1984 12,000 6 Jun 1985 2,200 23 Sep 1985 750 24 Oct 1986 1,000 Foster 439 Oulette Ave 8 Jan 1983 450 28 Apr 1984 9,000 14 Feb 1985 600 Superbase only prints the name and address when it reads a record from the Addresses file, and only prints the date and amount when it reads a record from the Deposits file. The effect of this feature resembles a "suppression of duplicate names" feature, but you should note that it can only work when the data for each "level" of the report is held in separate files. If you actually want to see the data repeated as in the first example, don't use the ON file feature. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ QUERY REPORT Superbase allows you to specify features such as totalling for the output fields in a query. If you don't want any features of this kind you should leave this line blank, avoid clicking on the Report button, and skip to the next section. If you do want some report features in your query output, click on the Report button. Superbase displays the Query Report requester. The Query report requester, like many others, has three main parts. To the left is a Field Name Panel, with a File Name Box above it. On the right is a set of buttons for making selections, and the Value Box. Below these is the Main Box into which Superbase copies the results of your selections. F„i„e„l„d„ „G„r„o„u„p„s„ This is a key concept. A report consists of a series of records, which may be divided into groups according to the sorting order you set. For example, in a file such as our demonstration Addresses file, all the address records for each country may be printed together, and all the records for each city may be printed together -- a Country group and a City group. The City groups would appear within the Country groups: England London London London York West Germany Bohn Bohn Heidelberg A field group is defined by a change in the content of the field. The change from Bohn to Heidelberg defines a City group, and the change from England to West Germany defines a Country group. Note that the change in the Country group necessarily implies a change in the City group: West Germany terminates both York and England In this example, we would say that the file was Ordered or Sorted by City w†i†t†h†i†n† Country. Now, if the file also had numeric or financial data, it would be very useful to be able to generate a subtotal for each Country and City group. Averages and record counts for each group would also be interesting. And we would like to see figures for the overall totals, averages, and counts as well. The Report requester allows you to specify these features for both the report as a whole and any group within it by clicking on the keyword buttons and entering field names. Summarize The Report will suppress all record data and only output information from the following functions. Report When this button is highlighted, the selections that you make will apply to the report as a whole, not just to a group within the report. Group When this button is highlighted, the selections that you make will apply to one Field Group within the report. Sum The fields that follow the keyword will be totalled or subtotalled depending on whether Report or Group is selected Mean The fields that follow the keyword will be averaged or sub-averaged depending on whether Report or Group is selected Count The records in either the whole report or a group will be counted or sub-counted depending on whether Report or Group is selected Superbase prints the totals and means for each specified field in the column for that field, aligned with the field's numeric format (see also Overflow in Subtotals or Totals, below). Counts are printed once for each group in the leftmost column. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ REPORT EXAMPLE This example illustrates all the Report features we have mentioned. However, it is not a tutorial, and you should not attempt to make it work with the demonstration files as they are released on the program disk. Suppose you want a report from an Addresses file that has been modified to include two fields for holding financial data: Balance and Outstanding. You want to see full in- formation, including record counts and averages as well as subtotals, for every City and Country Field Group. This would be the procedure to follow, starting with the requirements for the report as a whole. R„e„p„o„r„t„ „T„o„t„a„l„s„,„ „A„v„e„r„a„g„e„s„,„ „a„n„d„ „R„e„c„o„r„d„ „C„o„u„n„t„s„ 1. Click on Report. This tells Superbase that the selections you are going to make will apply to the report as a whole. 2. Click on Sum. This indicates that the fields you are going to select are to be totalled for the report as a whole. 3. Click on a numeric field in the Field Name Panel. Superbase copies the name into the Main Box after the keywords. Repeat this action for all the fields you wish to have overall totals for. If you selected Balance and Outstanding, the Report Command Line so far would be: REPORT SUM Balance, Outstanding 4. Now click on Mean. This indicates that the fields you are going to select are to be averaged for the report as a whole. 5. As for Sum, click on a numeric field in the Field Name Panel. Superbase copies the name into the Main Box after the keywords. repeat this action for all the fields you wish to have averages for. If you selected the same two fields, the Line would now be: REPORT SUM Balance, Outstanding MEAN Balance, Outstanding 6. Record counting. This feature is either on or off. If you want the records to be counted and the total printed at the end of the report, use this option. Click on Count. Superbase copies the keyword into the Main Box: REPORT SUM Balance, Outstanding MEAN Balance, Outstanding COUNT It's important to realize that the order of your actions is important here. By clicking at random on operators and field names, you could produce a meaningless Report Command Line. This won't harm your data, and it's the price you pay for complete flexibility, but you should try to avoid it nevertheless. A line like this doesn't help anyone: SUM MEAN Balance, Lastname, COUNT REPORT MEAN City Superbase will either give you a "Can't do this" message if there's an error in the line (such as trying to sum a text field), or print meaningless data. So proceed care- fully. G„r„o„u„p„ „T„o„t„a„l„s„,„ „A„v„e„r„a„g„e„s„,„ „a„n„d„ „R„e„c„o„r„d„ „C„o„u„n„t„s„ Now we come to the business of specifying report features for the different groups within the report. The procedure is very similar to that for the report as a whole. 1. Click on Group, and then on a single field name. Superbase applies the selections you make next to the named group only. If you selected Country, the Report Command Line would look like this: GROUP Country The field that defines the Group should also be part of the Query Order Command Line. Most often it's a text field, but if your report groups its records by date or number then you could obtain meaningful subtotals from a date or numeric field. The only fields you should not use as Group fields are those that change their values from record to record, such as most financial items. 2. Click on Sum. This indicates that the fields you are going to select are to be totalled for the group -- in other words they will be subtotals. 3. Click on a numeric field in the Field Name Panel. Superbase copies the name into the Main Box. Repeat this action for all the fields you wish to have sub- totalled for the group. If you selected Balance and Outstanding, the Line so far would be: GROUP Country, SUM Balance, Outstanding 4. Now click on Mean. This indicates that the fields you are going to select are to be averaged for the group. 5. As for Sum, click on a numeric field in the Field Name Panel. Superbase copies the name into the Main Box after the keywords. Repeat this action for all the fields you wish to have group averages for. If you selected the same two fields, the Line would now be: GROUP Country, SUM Balance, Outstanding MEAN Balance, Outstanding 6. As for the report as a whole, the record counting function is either on or off. By definition it counts only the number of records in the group. Click on Count, and Superbase copies the keyword into the Main Box. GROUP Country, SUM Balance, Outstanding MEAN Balance, Outstanding COUNT Let's suppose that you had set up a full Report Command Line for an Addresses file that had the numeric fields Balance and Outstanding. The Report Command Line would look like this: REPORT SUM Balance, Outstanding MEAN Balance, Outstanding COUNT: GROUP Country SUM Balance, Outstanding MEAN Balance, Outstanding COUNT: GROUP City SUM Balance, Outstanding MEAN Balance, Outstanding COUNT It looks lengthy, but when you read it through it's quite easy to understand what's going on. F„i„e„l„d„ „G„r„o„u„p„ „O„r„d„e„r„ „R„u„l„e„ Remember that we said earlier that the City group can be described as being w†i†t†h†i†n† the Country group? This makes the Country group the outer group, and as you can see above, it comes before City in the Command Line. The order from outer to inner must be reflected in the way you build up the Command Line, or Superbase will be unable to keep track of the subtotals. The Rule is: S†p†e†c†i†f†y† †f†i†e†l†d† †g†r†o†u†p†s† †f†r†o†m† †t†h†e† †o†u†t†e†r† †l†e†v†e†l† †i†n†w†a†r†d†s†.† Example Output The output from a Command Line like the one above would look like this: England London 230 100 England London 450 250 England London 1200 300 -------- -------- 1880 650 626.67 216.67 Group count 3 in London England York 300 75 -------- -------- 300 75 300 75 Group count 1 in York -------- -------- 2180 725 545 181.25 Group count 4 in England West Germany Bonn 6000 1450 West Germany Bonn 750 50 -------- -------- 6750 1500 3375 750 Group count 2 in Bonn West Germany Heidelberg 995 995 -------- -------- 995 995 995 995 Group count 1 in Heidelberg -------- -------- 7745 2495 2581.67 831.67 Group count 3 in West Germany ======== ======== 9925 3220 4962.50 1610 Report count: 7 ======== ======== S„u„m„m„a„r„y„ „R„e„p„o„r„t„s„ You can obtain a report that suppresses the main detail of the query output and prints just the summary information. Click on the Summarize button to activate this feature. If you wanted a summary report based on the one above, but without the mean information, the Report Command Line would be: SUMMARIZE: REPORT SUM Balance, Outstanding COUNT: GROUP Country SUM Balance, Outstanding COUNT: GROUP City SUM Balance, Outstanding COUNT The report would be: -------- -------- 1880 650 Group count 3 in London -------- -------- 300 75 Group count 1 in York -------- -------- 2180 725 Group count 4 in England -------- -------- 6750 1500 Group count 2 in Bonn -------- -------- 995 995 Group count 1 in Heidelberg -------- -------- 7745 2495 Group count 3 in West Germany ======== ======== 9925 3220 Report count: 7 ======== ======== D„e„r„i„v„e„d„ „C„o„l„u„m„n„s„ If you want to apply report features to derived columns, you can. However, you must refer to the derived column expression by the name you gave it with the AS Heading feature in the Query Fields requester. For example, the Query Report Command Line for subtot- alling a derived column where the original expression was Balance * 0.15 and the AS Heading text was "15%" would be: GROUP Country SUM Balance, Outstanding, "15%" You would enter "15%" into the Value Box in the usual way. Obviously you must ensure that each item of AS Heading text is unique, or Superbase may confuse one expression with another. O„v„e„r„f„l„o„w„ „i„n„ „S„u„b„t„o„t„a„l„s„ „o„r„ „T„o„t„a„l„s„ Every time you request totals for a numeric field, you run the risk of creating a format overflow. Suppose you had a numeric field with a format "999.00", defined at this length because the field itself can never exceed 999 in value. If the total were to run into four figures, as it would if just 10 records had the value of 100 in this example, the format would not be able to handle it, and Superbase would print or display number signs: ###.## To prevent this happening, you must adjust the field format in the File Definition. Select Project Edit, select the field to be adjusted, and click on the Numeric button. Then increase the length of the format by clicking on the upper left arrow on the Number Format Panel. Exit from the Number format requester, then from the File Definition requester, both times by clicking on OK. Then select Cancel on the Save File Definition requester. Now Superbase will use the expanded numeric format for all the numbers in the column, as well as subtotals and totals. You may prefer to make such changes permanent, by saving the File Definition with the expanded format. T„o„t„a„l„s„ „f„o„r„ „N„o„n„-„P„r„i„n„t„i„n„g„ „F„i„e„l„d„s„ You may request totals for fields that do not appear in the Query Fields Command Line. Superbase prints such totals without a column heading after any totals for fields that are being printed. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ QUERY FILTER The Filter button calls up a requester very similar to the Filter requesters used in the other functions on the Process menu. If you don't want to restrict the records selected for the query output, leave this line blank. If you're leaving the Filter line blank, don't click on the Filter button, and go on to the next section. If you want to set up a Filter, click on the Filter button. Superbase displays the Query Filter requester. The Query Filter is very similar to the Filter that you're familiar with from the Control Panel. However, this Filter allows you to select fields from any of the open files being used in the Query. Above the Field Name Panel is the File Name Box containing the name of one of these files. The field names in the Panel belong to the file whose name appears in the box. If you want to see the fields for the next open file, click on the upward pointing arrow to the right of the File Name Box. Apart from this difference, the Query Filter works exactly like other filters. You click on a field name, then on an operator, then in the Value Box or on another field name. (Remember to press Return after you've entered a value in the Value Box.) Superbase builds up the Filter Command Line in the Main Box as you make selections and enter values. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ RELATIONAL QUERIES If you're new to databases, or you have no intention of finding out how Superbase handles multiple files, you may wish to skip this section. It's intended for those users who want to explore the possibilities of linking files together to form a sophisticated database system. F„i„l„e„ „D„e„s„i„g„n„ Although the design of the individual files that constitute a database is a very important topic, this Guide cannot take the place of either textbooks on how to structure a database or experience. You may not need or want the former, but you are going to have to gain the latter. We shall spend no time at all on the theory of databases. In your case, as in every case, a database meets the needs of a particular application. This makes it difficult to give much general advice beyond a few basic points: * Keep record and index key sizes small. * In each file that is to form part of a multi-file query, set up a field to be used for linking across files. The field names do not have to be the same. * Place an identifier such as a record or customer number in this field. For every pair of files to be linked, there must be identifiers that are the same in both files, yet unique within each. * Index files on all identifier fields used for linking. * Base transactional records such as invoice line data on a single transaction -- no repeating fields. Q„u„e„r„y„ „F„i„l„t„e„r„ „C„o„m„m„a„n„d„ „L„i„n„e„ „R„u„l„e„s„ Creating an efficient Query Filter is something you have to learn. Even sophisticated users of mainframe databases often find that the results of their first attempts at a query are not exactly what they want. It's important to have a realistic level of expectation, to avoid frustra- tion. Realize that the process of experimentation and feedback is normal. A query is multi-file when fields from more than one file are named in the Query Fields Command Line. For a multi- file query to work, you must specify links between the files. A link is known as a "join" in the jargon. A join takes the form of two fields joined by an EQUALS operator: Custref.invoices = Custnumber.Addresses. You can have joins between more than one pair of files, and a file or field may appear more than once in the series of joins. Although Superbase, true to its philosophy, allows you to create a Query Filter in almost any form, only certain techniques will work properly. To make this clear, we have come up with six important rules for building up Query Filter Command Lines: R†u†l†e† †O†n†e†.† When linking any two files, there must be a link between them in the form of record identifiers that are the same in both: Custref.invoices = Custnumber.Addresses R†u†l†e† †T†w†o†.† Do not use OR expressions when linking across files: Custref.invoices = Custnumber.Addresses OR Custref.invoices = Custname.Addresses. This is horrible! R†u†l†e† †T†h†r†e†e†.† Use only the EQUALS operator when linking between files. R†u†l†e† †F†o†u†r†.† Place all expressions that link across files at the start of the Filter Command Line. R†u†l†e† †F†i†v†e†.† Later in the Command Line, if you want to use an OR expression, enclose it within parentheses: ( City = "London" OR City = "New York" ) R†u†l†e† †S†i†x†.† Sequence all filter expressions in an order from most precise to least precise. For example: Custnumber = Custref AND Country LIKE "England" AND Balance > 1000 AND ( Name LIKE "smith" OR Name LIKE "jones") The join comes first, followed by a string match on "England", which is a single value -- quite precise. Then comes a GREATER THAN expression -- less precise, as many values can be greater than 1000. OR expressions, whatever the degree of precision involved, always come last, inside parentheses. L„e„a„r„n„ „t„o„ „u„s„e„ „a„ „Q„u„e„r„y„ „C„o„m„m„a„n„d„ „L„i„n„e„ „a„s„ „a„ „s„e„r„i„e„s„ „o„f„ „o„p„e„r„a„t„o„r„-„b„a„s„e„d„ „e„x„p„r„e„s„s„i„o„n„s„ „s„e„p„a„r„a„t„e„d„ „b„y„ „A„N„D„ „o„p„e„r„a„t„o„r„s„.„ C‚a‚u‚t‚i‚o‚n‚.‚ ‚Y‚o‚u‚ ‚m‚u‚s‚t‚ ‚f‚o‚l‚l‚o‚w‚ ‚t‚h‚e‚s‚e‚ ‚r‚u‚l‚e‚s‚.‚ ‚I‚f‚ ‚y‚o‚u‚ ‚d‚o‚n‚'‚t‚,‚ ‚y‚o‚u‚ ‚c‚o‚u‚l‚d‚ ‚e‚n‚d‚ ‚u‚p‚ ‚w‚i‚t‚h‚ ‚q‚u‚e‚r‚i‚e‚s‚ ‚t‚h‚a‚t‚ ‚p‚r‚o‚d‚u‚c‚e‚ ‚e‚n‚o‚r‚m‚o‚u‚s‚l‚y‚ ‚r‚e‚p‚e‚t‚i‚t‚i‚v‚e‚ ‚a‚n‚d‚ ‚m‚e‚a‚n‚i‚n‚g‚l‚e‚s‚s‚ ‚r‚e‚s‚u‚l‚t‚s‚,‚ ‚a‚n‚d‚ ‚t‚a‚k‚e‚ ‚a‚ ‚v‚e‚r‚y‚ ‚l‚o‚n‚g‚ ‚t‚i‚m‚e‚ ‚t‚o‚ ‚d‚o‚ ‚s‚o‚.‚ If you want to interrupt a Query, click on Stop. The process will only halt on retrieval of data that matches the selection criteria. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ QUERY ORDER The Order button allows you to specify a sorted order for the query output. If you don't specify any order, Super- base will present the records in the order in which it finds them on the disk. Most probably, they will not be in any alphabetic on indexed order. As this is the fastest way of retrieving information from the disk, you may want to use it in certain situations where the order of pre- sentation does not matter. And if you're creating a new database you should definitely avoid specifying an order, to speed up the processing. However, in the majority of cases, you'll want to see the records ordered, especially when: * Indexed order, such as date order, is an important part of the file * You have set up subtotals on the Report line. If you don't want to specify any order, don't click on the Order button. Skip to the next section to complete the Query Definition. If you do want to add order features to the query, click on the Order button; Superbase displays the Query Order requester. T„h„e„ „Q„u„e„r„y„ „O„r„d„e„r„ „R„e„q„u„e„s„t„e„r„ This requester allows you to select fields for sorting from any file, specifying whether each one is to be sorted in ascending or descending order. You can select fields from any of the open files being used in the Query. Above the Field Name Panel is the File Name Box containing the name of one of these files. The field names in the Panel belong to the file whose name appears in the box. If you want to see the fields for the next open file, click on the upward pointing arrow to the right of the File Name Box. To the right of the Field Name Panel are the buttons for the Ascending and Descending options. The procedure is quite simple: 1. Click on a field name. 2. Click on either Ascending or Descending. 3. Repeat with other fields if necessary. The default length for sorting is 15 characters per field. Superbase gives equal weighting to upper case, lower case and accented instances of a character. C†a†u†t†i†o†n†.† †Y†o†u† †w†i†l†l† †n†e†e†d† †t†o† †h†a†v†e† †a†t† †l†e†a†s†t† †1†5†k† †o†f† †f†r†e†e† †m†e†m†o†r†y† †b†e†f†o†r†e† †y†o†u† †c†a†n† †u†s†e† †Q†u†e†r†y† †O†r†d†e†r†.† When you're finished, click on OK to return to the main Query Definition requester. Superbase copies your selections into the Order line. S„i„n„g„l„e„ „a„n„d„ „M„u„l„t„i„p„l„e„ „F„i„l„e„ „S„o„r„t„i„n„g„ Outputting records from a single file is comparatively easy, and Superbase performs the operation without affecting the file itself. If you specified an order using just a single index, Superbase outputs the data as it finds it. But if your Order request includes several fields, you may have to wait a while for Superbase to create temporary files prior to producing the query output. The temporary files are identified by the names "superbase.tmp" and "superbase.srt". However, if you are using more than one file, Superbase has to do quite a lot more work. Although the amount of extra work varies depending on the precise Order request you set up, essentially it means that Superbase can't tell what the final order is to be until it's finished reading all the files. Superbase may then create a temporary file which is used in the output phase. When this is done, Superbase switches to an output phase, and produces the results you want. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ QUERY OUTPUT DESTINATION You select the destination for the query output by clicking on the appropriate button. O„K„ Always directs output to the Screen. P„r„i„n„t„ Always directs output to the Printer. D„i„s„k„ This selection creates an ASCII disk file containing the query output. The output is formatted as if it were being printed, so provided the file is not too large for your word processor, you may be able to load and edit it like an ordinary text file. F„i„l„e„ The new database is indexed on the first indexed field in the output, or if there are no indexed fields, on the first field in the output. Superbase ignores any formatting or reporting elements in the query. Derived items are treated as new fields, but you must ensure that the text set with Heading is valid as a field name. Once you've selected your output destination you must click on OK to initiate the Query output. The Disk and File options require you to enter a name for the file you're about to create. .OH=Working with Superbase P„r„o„c„e„s„s„ „R„e„m„o„v„e„ .EH=P„r„o„c„e„s„s„ „R„e„m„o„v„e„ Working with Superbase ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ PROCESS REMOVE This option removes a set of records or all the records from a file. It uses a Filter, allowing you to specify the conditions under which a record is to be removed. Single records should be disposed of with the Remove option on the Record menu. U„s„i„n„g„ „P„r„o„c„e„s„s„ „R„e„m„o„v„e„ Select Remove from the Process Menu. Superbase displays the Remove Records Filter requester. The requester is the same as the normal Filter requester. See G‚e‚t‚t‚i‚n‚g‚ ‚t‚o‚ ‚K‚n‚o‚w‚ ‚S‚u‚p‚e‚r‚b‚a‚s‚e‚, pages I-19 to I-27, for a detailed explanation of how Filters work. E„x„a„m„p„l„e„ „1„:„ „R„e„m„o„v„i„n„g„ „S„e„l„e„c„t„e„d„ „R„e„c„o„r„d„s„ In this example we'll use the Addresses file, so make it the current file if you haven't already done so. Then select Process Remove. Your objective is to remove from the file all the addresses where the Country field has the value "England". Click on Country. Superbase copies the field name into the Main Box. Now click on the LIKE operator. Then click in the Value Box, and type "England" (omit quotation marks). Press Return. Your Filter Command Line should read: Country LIKE "England" If it doesn't then click on the Clear button to clear the Filter, and start again. (If you click on Cancel it will have the same effect, but Superbase will assume you've finished with the Filter and remove the requester from the screen.) When you've got the Filter Command Line right, click on OK. Superbase now checks the Filter Command Line to see whether there are any errors in it. If you've entered the example correctly there shouldn't be any. Next comes a confirmation request, as Superbase does not allow records to be removed with a single action. Click on OK to go ahead, Cancel to abandon the action. Provided you click on OK, Superbase processes the file using the Filter. Superbase automatically removes all the records that match the Filter. The Filter Command Line is then cleared out, and Superbase returns to the Main Display. E„x„a„m„p„l„e„ „2„.„ „R„e„m„o„v„i„n„g„ „A„l„l„ „R„e„c„o„r„d„s„ We'll explain how to do this, but don't actually go through the steps yourself unless you're sure you want to discard all the records in the current file. 1. Select Process Remove. 2. When the Remove Records Filter appears, click on OK. 3. When the Confirmation requester appears, click on OK. Superbase will remove all the records in the file by initializing the file. .OH=Working with Superbase P„r„o„c„e„s„s„ „I„m„p„o„r„t„ .EH=P„r„o„c„e„s„s„ „I„m„p„o„r„t„ „ Working with Superbase ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ PROCESS IMPORT This function creates new records in a Superbase database file by reading in data from an ASCII disk file. If your spreadsheet, communication package, or other application is capable of producing an ASCII file on disk, then that file can be loaded into Superbase to give you all the management advantages of the database. Likewise, an ASCII file produced by exporting from one Superbase file can be imported into another Superbase file. In Import and Export operations, the items of data (fields) in the ASCII file are separated by a special character, so that Superbase can tell where each one ends. Likewise, each record is also followed by a special character. The default settings for the field and record separators are respectively the comma and the carriage return. However, if the file to be imported uses other characters, you can instruct Superbase to use them through the Set Options requester. You cannot use import to move data directly from one Superbase file to another. If you want to do this, use the Query function to output data into a new file. Import uses a Filter to determine which records in the ASCII file will be accepted into the Superbase file. You can import data into a limited set of fields in each record if you open the required set of fields before you do the import. U„s„i„n„g„ „P„r„o„c„e„s„s„ „I„m„p„o„r„t„ Select Import from the Process Menu. Superbase displays the Import Filter. The requester is the same as the normal Filter requester. See G‚e‚t‚t‚i‚n‚g‚ ‚t‚o‚ ‚K‚n‚o‚w‚ ‚S‚u‚p‚e‚r‚b‚a‚s‚e‚,‚ pages I-19 to I-27, for a detailed explanation of how Filters work. Build up your Filter Command Line in the Normal way. When you click on OK, Superbase displays the Import File Name requester. Enter the name of the ASCII file from which the data is to be Imported. Even though the Filter operates in the same way for Import as for the other Filters, there are a few important extra considerations: * The file into which the data is to be imported is always the current file. * The fields into which data is to be imported must be of the correct type: a numeric field for numbers, a date field for dates, etc. * If an imported item of data is too long for its field, Superbase will inform you. You may either continue, in which case the error is overlooked and truncated data is stored in the imported record, or stop the import and use Project Edit to extend the length of the field. * If you are importing into just the set of open fields, Superbase will create records with the other fields left blank. Index entries for such fields will be made, but they too will be blank. Note that a unique index cannot allow more than one blank entry. * Superbase will signal an error if import tries to store a blank field that has required status. Remove such status temporarily with Project Edit. * Validation checks will be made on all fields in records created by import, whether they are being imported into or not. Again, you may need to temporarily remove Validations using Project Edit. .OH=Working with Superbase P„r„o„c„e„s„s„ „E„x„p„o„r„t„ .EH=P„r„o„c„e„s„s„ „E„x„p„o„r„t„ Working with Superbase ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ PROCESS EXPORT Export creates an ASCII file on disk as a copy of the data from selected records and fields in a Superbase file. You may have an application that can accept ASCII files. Word processor mailing packages often can. Also an ASCII file can be imported into another Superbase file. You can set up your own field and record separator characters in the Set Options requester, and specify whether quotation marks should be placed around the exported fields (this is necessary if the data contains the character used as a field separator; for example, addresses, dates or numbers may contain commas.) You cannot use Export to move data directly from one Superbase file into another. If you want to do this, use the Query function. Export can only process a single file, so if you need to Export data from more than one file, you should use Query first to combine the data into one new file. Export uses a Filter to determine which records from the Superbase file will be copied to the ASCII file. U„s„i„n„g„ „P„r„o„c„e„s„s„ „E„x„p„o„r„t„ Select Export from the Process Menu. Superbase displays the Export Filter. The requester is the same as the normal Filter requester. See G‚e‚t‚t‚i‚n‚g‚ ‚t‚o‚ ‚K‚n‚o‚w‚ ‚S‚u‚p‚e‚r‚b‚a‚s‚e‚,‚ pages I-19 to I-27, for a detailed explanation of how Filters work. Build up your Filter Command Line in the Normal way. When you click on OK, Superbase displays the Export File Name requester. Enter the name you want to give to the ASCII disk file. We suggest that you adopt a convention for naming Export Files, such as ending each name with the extension ".exp". Superbase then processes the database file, checking each record to see whether it matches the conditions set up in the Filter. If it does, Superbase creates a copy of the data in the currently open fields and adds it to the Export file. .OH=Working with Superbase P„r„o„c„e„s„s„ „U„p„d„a„t„e„ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ PROCESS PRINT The Print option provides a quick and easy way of obtaining a printout from the current file. The function outputs the currently open fields in the column format set for Table View. It uses a standard Filter. U„s„i„n„g„ „P„r„o„c„e„s„s„ „P„r„i„n„t„ Select Print from the Process Menu. Superbase displays the Print Filter. The requester is the same as the normal Filter requester. See G‚e‚t‚t‚i‚n‚g‚ ‚t‚o‚ ‚K‚n‚o‚w‚ ‚S‚u‚p‚e‚r‚b‚a‚s‚e‚,‚ pages I-19 to I-27, for a detailed explanation of how Filters work. Build up your Filter Command Line and click on OK. Superbase prints out the records that match the Filter. Certain print formatting conventions are observed: * The field names appear as column headings, separated from the first line of data by a blank line, on each page. * Superbase takes the page length and margin settings from the Workbench Preferences system utility. If the data exceeds the right margin, Superbase prints it on the next line. * Superbase prints a form feed six lines before the end of each page. * If Preferences indicates that you are using fanfold paper, Superbase doesn't pause at the end of each page. If you're using single sheets, set Preferences accordingly and Superbase will pause at the end of the page for you to insert a new sheet. .OH=Working with Superbase P„r„o„c„e„s„s„ „L„a„b„e„l„s„ .EH=P„r„o„c„e„s„s„ „L„a„b„e„l„s„ „ Working with Superbase ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ PROCESS LABELS You can print mailing labels up to four across using this option. You can control the dimensions of the label, as well as the number of fields per line. Labels uses a standard Filter. Only the open fields from the current file are printed on labels. U„s„i„n„g„ „P„r„o„c„e„s„s„ „L„a„b„e„l„s„ Select Labels from the Process Menu. Superbase displays the Labels Filter requester. The requester is the same as the normal Filter requester. See G‚e‚t‚t‚i‚n‚g‚ ‚t‚o‚ ‚K‚n‚o‚w‚ ‚S‚u‚p‚e‚r‚b‚a‚s‚e‚,‚ pages I-19 to I-27, for a detailed explanation of how Filters work. When you click on OK, Superbase displays a requester to specify the shape and content of the labels to be printed. The requester has three parts. On the left is a panel in which you specify the number of fields per line on the label. To the right are a number of boxes for input of dimensions and other parameters. Below are some extra control buttons in addition to the usual Ok and Cancel. F„i„e„l„d„s„ „P„e„r„ „L„i„n„e„ Superbase uses the Open Fields List if there is one to determine which fields are to be printed in which order. The number of fields per line varies from one to four, and you can have up to ten lines of data per label. You select the number of fields like this: 1. Click on one of the boxes in the left hand panel. This represents one of the ten possible lines of the label. 2. Click on the up arrow gadget to increase the number in the panel, or the down arrow to decrease it. If you don't want any fields to appear on a specific line, set it for zero fields. If Superbase finds a blank field in a record, it compensates by closing up the label lines. A typical Open Fields The actual label format List is this: is this: Title Title, Firstname, Surname Firstname Address Surname City Address State, Code City State Code You should arrange the Fields Per Line panel like this: 3 1 1 2 0 0 0 0 0 0 Superbase automatically inserts a space between fields when there are multiple fields on a line. L„a„b„e„l„ „F„o„r„m„a„t„ To alter any of the settings, click in the box to select it, and then click on the up or down arrows as required. F„i„r„s„t„ „L„a„b„e„l„ „M„a„r„g„i„n„ This controls the first printing position for the left-hand column of labels. You may have to increase the Second Label Margin before increasing the First Label Margin to ensure that you leave enough room for the label itself. L„a„b„e„l„ „T„e„x„t„ „W„i„d„t„h„ This measures the overall width of the label in characters. Superbase won't let you make it overlap the Second Label Margin. S„e„c„o„n„d„ „L„a„b„e„l„ „M„a„r„g„i„n„ This controls the first printing position for the second column of labels. Superbase won't let you decrease it to overlap with the first label. F„i„r„s„t„ „L„i„n„e„,„ „N„e„x„t„ „L„a„b„e„l„ This sets the interval between labels, by specifying the line on which the first field of the second row of labels is to appear. L„a„b„e„l„s„ „P„e„r„ „L„i„n„e„ This varies between one and four. C„o„p„i„e„s„ „P„e„r„ „L„a„b„e„l„ You may specify up to 99 copies of each label. T„e„s„t„ „B„u„t„t„o„n„ The label format can be tricky to get right, especially if you're using a non-standard size. We have provided a special test function to help you avoid frustrating errors in the actual print run. When you've set up the label format and fields, align the labels on the printer. Now click on test. Superbase prints two rows of labels, using the "X" character to show exactly where the fields will print (it doesn't separate multiple fields on a line). You can alter the format values and repeat the test as often as you wish. .OH=Working with Superbase T„h„i„r„t„y„ „M„i„n„u„t„e„ „T„u„t„o„r„i„a„l„ .EH=T„h„i„r„t„y„ „M„i„n„u„t„e„ „T„u„t„o„r„i„a„l„ Working with Superbase S„i„n„g„l„e„ This is a convenience feature. To save you effort, Superbase allows you to print a "one-off" label or envelope from the current record. * Select the record you want as the current record. * Select Process Labels, and click on OK. * On the Label Definition requester, click on Single. Superbase prints the open fields from the current record according to the settings on the Label Definition requester. You may need to reset the First Label Margin to get a correct result on an envelope. ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ ‚ THIRTY MINUTE TUTORIAL This exercise goes through all the single file processing options on the Process Menu, leaving the two more complex multi-file options, Update and Query, for you to study and practise at your leasure. The objective is to make you familiar with the way these core processing functions work, so that you can start to see how you can apply them to your own database. First, you will create a new file, so that you can import data into it. Then you will Export data from the Addresses file (which is supplied on your disk) into a temporary file. Next, you import this data into the file you have created. Then you can Print some of the imported records and Labels for them. And finally, you will remove all the records from the new file. C„r„e„a„t„i„n„g„ „a„ „N„e„w„ „F„i„l„e„ This file will be used to import records later on in the tutorial. The process of setting up a file is pretty easy, and you should have had some practice by now. So we'll skip the details -- if you need to refresh your memory you can check the Thirty Minute Tutorial on pages II-22 to II-32. The name of the new file is New Addresses. Select New File from the Project menu and type in this name. When the File Definition requester appears, add the following fields,