Risc World 3

home *** CD-ROM | disk | FTP | other *** search

/ Risc World 3 / Risc World 3.iso / SOFTWARE / ISSUE4 / POWERBASE / DOCS / Ch03-Print < prev next >

Wrap

Text File | 2001-01-30 | 47.5 KB | 966 lines

================================= Ch 3 − Printing from the Database ================================= This chapter describes how to print out selected data from a database, including the printing of individual records and of labels. Such a print-out is commonly referred to as a report. 3.1 Output destination -------------------------- Printed output may be displayed in a window, directed to a specified text file or sent straight to the printer. Choosing Options from the Print submenu (keystroke equivalent Ctrl Print) displays the Print Options window and you will see at the top of the window three radio buttons, Window, File and Printer, which allow you to select the output destination. Each of these options will now be described. 3.1.1 The Window destination This is the default setting and, as its name suggests, it displays the completed report in its own window. Clicking with MENU over this window opens the Report menu which offers four choices: Save as text leads to a Save box from which an icon may be dragged to a filer window or to any application which can accept a Text file. The supplied pathname uses an appropriate leaf-name for the file and points to a directory called PrintJobs which is inside the database directory. Each database has its own PrintJobs directory whose contents can be displayed by choosing Show jobs done (Ctrl P) from the Print submenu. To save files with the least bother simply click on Save or type Return. You can, of course, delete the pathname (Ctrl U), type in your own filename and drag the file icon to any open directory. You might also be able to produce hard copy by “saving” the text to the Printers icon on the iconbar, although some recent printers contain no fonts of their own and therefore cannot print text files in this way. Look in PrintJobs regularly and get rid of files which you no longer need. Sort will sort the report on whatever column the mouse pointer was over when you clicked MENU. Shrink list will remove as much surplus “white space” as possible from between the columns of a report. This item might be shaded: white-space removal can be set to occur automatically before the report is displayed (in fact this is the default setting). If this is the case no further space can be removed and the menu choice is therefore made unavailable. Scrap removes the report window from the screen and recovers the memory it occupied. You might find this useful if you run short of memory after creating a large report. Calling up records from the report window ----------------------------------------- The report window has one more useful feature. If you point at a particular piece of displayed data and double-click with SELECT the record window will come to the front displaying the relevant record. If the field corresponding to the item you clicked is editable the caret will be placed in that field. If you are using a report to look for errors in the data you can quickly correct them by this method but the report won’t change to reflect your corrections until you re-create it. If you double-click with ADJUST instead of SELECT the record window opens at the pointer and its size and scrolling are adjusted to show only the required field. 3.1.2 The File destination A Save box will appear as soon as you tell Powerbase to go ahead and generate the report. The supplied pathname is the same as would be displayed when saving from the report window. Simply click Save to save it in PrintJobs under that name. Alternatively, type a filename and drag the icon to an open directory. The report will then be created and the file closed. Nothing else appears on the screen in this case. The File destination is of greatest use when reports are being produced from a Script file (see Ch 12). 3.1.3 The Printer destination When the Printer radio button is selected the More button alongside becomes available. (It is shaded when Window or File is selected.) Clicking More opens the Printer Setup window which provides a wide range of options. You may specify the number of copies to print, choose the font and point-size to be used, print with the paper upright (portrait) or sideways (landscape) and set the inter-line spacing and any or all of the four margins. All measurements other than font size may be specified in mm (default), inches or points. 3.2 Print formats --------------------- Powerbase is capable of producing reports in four different formats. Only two of these, Horizontal and Vertical, are available when you print to a - 16 - window or a file and these are selected via two radio buttons on the Print Options window. When the output destination is Printer two additional formats, Table and Label, are also available and are selected from the Printer Setup window. These four formats will now be described. 3.2.1 Horizontal This is so-called because each record appears on a single, horizontal line with the fields aligned so that they form neat columns. Non-numeric fields are left-justified. Numeric fields, and others whose content is treated as a numeric value (see 3.5.2), are right-justified. A header line identifies the columns by means of the tags or descriptors of the fields and this header can be made to appear on every page of the report or on the first page only. An optional descriptive title may also be incorporated. The Spacer is used to separate columns (see 3.10). All these features (and others) are chosen from the Print Options window. Besides the ability to sort a report in a window (see 3.1.1) you may force Powerbase to sort the report before displaying it. To do so, select the Sort on option button in the Print Options window and enter in the associated writable icon either the tag of the field on which you wish to sort or the column number of the report. The feature is only of use in Horizontal and Table formats, but may be used with the Printer destination as well as with Window. 3.2.2 Vertical Fields are printed underneath each other with the identifier (tag or descriptor) at the left hand side. Where the field selection includes an external text file, i.e a Text or Text block field, the Vertical format is the only one which may be used and will be selected automatically. Although each field normally occupies a line to itself you can override this by holding down Shift as you click with ADJUST to select the field. You will then not get a new line after the field: the next field to be selected will appear (with its identifier) on the same line. We will call this function field concatenation. It may be applied to any number of fields: keep Shift down while selecting all except the last one to appear on the line. This feature is very useful if your report contains a mixture of long fields which need a line each and short ones which don’t and would otherwise result in wasted space and paper. Concatenated fields are separated by the Spacer and the width of the report is governed by Text width (see 3.10). 3.2.3 Table This is only available when outputting to the printer. It resembles Horizontal format but the lines and columns are separated by horizontal and vertical rules, forming a grid. When this format is selected a number of extra features are enabled allowing you to include extra (blank) columns and lines, making this format especially useful when you want a list to which information is to be added by hand (e.g. entering marks against a printed list of students). The number and width of blank columns and the number of extra lines may be specified. For a tidy result it is recommended that you increase the line-spacing from the default 120% to about 150% when using Table format. 3.2.4 Label This is meant for printing on special label stationery. Since such stationery is expensive you are advised to try out your settings on plain paper first! Selecting this format enables the label setup choices which include the label size and the number of labels in a row. It also allows optional fixed starting and finishing lines to appear on each label. The number of lines on the label is not needed: Powerbase works this out from the label height and print size. Printing will normally begin on the first label in the first row on the sheet but, to enable you to use up a part sheet of labels, you may specify which label to begin with, e.g. for three-in-a-row labels, entering 5 would make printing start at the second label of the second row. After the first sheet the starting-point reverts to the first label in the first row. Each field normally appears on a separate line but fields may be concatenated in the same way as for Vertical format, with the Spacer being used to separate the fields (see 3.10). This may be necessary if you are using separate fields for surname and initials or surname and forename. You may specify one field to be substituted for another if the latter is blank. Both fields are specified by tag and the Substitute switch is set. This is useful in a school or college situation where labels are being addressed to parents. Mature students, for whom the “parent” field in the record is blank, can have their own names printed instead. Another switch makes the primary key of the record appear in small print on each label as a - 17 - means of identification. This can be useful if the data printed on the label doesn’t make it obvious which record it comes from. 3.3 What types of field can be printed? ------------------------------------------- Powerbase can use many different types of field. All are described in 4.2.5 in connection with setting up a new database and you should refer to that section to clarify what follows here. You can print data from the following types of field: (1) Any Editable field (i.e.one into which you can type directly), including Scrollable lists. (2) Computed and Stamp fields. (3) Text and Text block fields. (4) Check Boxes. What is printed for these differs from what you see in the check box. Thus: (a) Tick/Cross boxes result in “Yes” or “No” (b) Tick/Blank boxes result in “Yes” or “-” (c) Star/Blank boxes result in an asterisk or “-” (d) Null/Tick/Cross boxes result in “-”, “Yes” or “No” (e) ?/Tick/Cross boxes result in “?”, “Yes” or “No” Note that (d) and (e) are three-state check-boxes; (a), (b) and (c) are two-state check-boxes. It is, of course, possible to print Draw and Sprite fields from individual records by loading the external file into Draw or Paint and printing from that application (See 2.6.2). 3.4 Specifying which fields to print ---------------------------------------- The field or fields selected for printing is called a print selection. Point at each of the required fields and click with ADJUST. The fields will be highlighted by reversing the foreground and background colours. Only those fields which are printable (see 3.3) will respond to ADJUST in this way. A second click will de-select the field. Note that the order in which you select the fields is important since that is the order in which they will appear in the report. The menu button at the bottom-centre of the Match window (see 3.5) will list the fields in the order in which they have been selected. Ctrl F has the same effect. (If no fields are selected Ctrl F gives a listing of all the fields.) A contiguous range of fields may be selected by placing the caret in the first field then double-clicking with ADJUST in the last. To select all printable fields choose Select all (Ctrl A) from the Print submenu. There is also a Clear selection entry on this submenu (Ctrl Z). 3.4.1 Saving print selection files Print selections may be saved for future use. Save selection from the Print submenu leads to a Save box. Accepting the default pathname will save the file with the name Selection in a directory called PrintRes. Just as every database has its PrintJobs directory, so does it also have its own PrintRes (i.e. “Print Resources”) directory whose contents can be displayed with Show resources (Ctrl R) from the Print submenu. Selection files are of type &7f3 and are distinguished by their icon which bears a large S. Because they have a specific file-type which Powerbase recognizes they can be loaded by double-clicking on them. 3.4.2 Default selection You may save as many print selections as you like and use any file-names, but one name is special: a file saved as !Selection is treated as a default selection. When you instruct Powerbase to create a report without having first highlighted the fields to be included, the default selection will be searched for in PrintRes and used. If there is no such file the primary key field(s) will be printed. As soon as the report is complete the selection is cleared. You won’t see the highlighting of the fields at all when a selection is used automatically in this way. To save a default selection you need only select the option button Default selection in the Save box and accept the supplied pathname by clicking Save or typing Return. 3.5 Specifying which records to print ----------------------------------------- Unless we want to print all the records in the database we need some means of telling Powerbase what are the common features of the records we wish to print. There are two ways of doing this. The more versatile way (and the one which Powerbase uses by default) makes use of a search formula or query - 18 - describing the characteristics of the required records. The remainder of this section deals with the construction and use of search formulae. For the alternative method, query by example see Section 3.6. If you choose Print from the main menu a small window with the title Find matching records appears. We will call this the Match window. The same thing happens if you go to the Print submenu and choose Create report. Simplest of all is to just type the Print key on the keyboard. The most prominent feature of the Match window is a group of icons enclosed by a thin red border. This object is called the Query panel and you may have already seen it since it forms part of several windows. It appears when you select the Filter switch on the keypad for example (see 2.3.3) and is also used when making Global changes (2.5.5), performing a Move/delete operation on a batch of records (2.5.6), doing a Mail merge (9.2.2), exporting a CSV file (8.3) and creating a Subset (Ch 13). The writable icon in the Query panel, in whatever context the latter appears, is meant to take a search formula. The simplest thing you can do, of course, is to type nothing at all! If you then click on the Print button you will create a list of all the records in the current subfile of the database. You could achieve the same result by typing ALL*, indeed if after producing the above list with a null formula you click on the Old button (Ctrl O), which retrieves the last-used search formula, you will find ALL displayed. 3.5.1 The construction of search formulae Most database queries will involve a selected group of records. A search formula describes the criteria which records must fulfil in order to be included in the report. Getting to grips with search formulae is, perhaps, the biggest hurdle faced by the new Powerbase user and you are referred first to the simple examples described in the Tutorial file. A search formula consists of one or more search elements. A search element specifies that a field value must fit a certain condition and takes the form: <TAG LIST><COMPARATOR><TARGET LIST> The angle brackets are there for clarity and are not used in entering the actual formula. There must be no spaces between the three parts. A tag list (if it contains more than a single tag) has the form: tag1,tag2,tag3,.... where tag1 etc. are field tags (see 4.2.1 and 4.2.4) which identify the fields to be matched. A target list (if it contains more than a single target) has the form:- target1,target2,target3,.... If the Case switch on the Query panel is selected then all comparisons will be case-specific, e.g. “Cat” will be regarded as different from “CAT” or “cat”. If the Case switch is not selected all those three will be considered identical. Fields are normally listed in ascending alphabetical order or, for numeric fields, ascending numerical order. If the Reverse switch on the Query panel is set they will appear in reverse order. The heading of a report shows which fields were used in the search formula and what targets were specified. If a target was placed in quotes (which is the only way of searching for any string containing a comma, for example) then it appears in quotes in the heading also. It is impossible to describe the use of search formulae adequately without quoting actual examples. As in the Tutorial file we will mainly make use of the Elements sample database. A simple example of a search formula consisting of a single search element is: GP=T where GP is the field tag, = is the comparator and T is the target. This means “The field whose tag is GP must contain the value T”, i.e. all transition elements (but no others) are to be included in the report. A slightly more complex one is: GP=1,2,3 which would be interpreted as “The GP field must match one of 1,2 or 3”. This may also be entered as: GP=1 OR GP=2 OR GP=3 which is possibly easier to understand but also somewhat longer. A further example is: OX1,OX2,OX3=3 meaning “One of the first three oxidation state fields must have the value 3”. - 19 - This could also be entered as: OX1=3 OR OX2=3 OR OX3=3 Yet another way is: OX1-OX3=3 i.e. you may specify a range of adjacent fields by giving the first and last separated by a hyphen. Where you don’t know which fields to test you can replace the tag, tag list or tag range with @, which causes all the fields in the record to be examined. Note that in these examples only one of the fields in the tag list is required to match one of the targets in the target list (although it doesn’t matter if more than one field matches more than one target). Sometimes we want an inclusive search so that all of the fields in the tag list match a given target or, less frequently, a field contains all of the values in the target list. It’s a matter of connecting the search elements with AND instead of OR. You can do exactly that: OX1=3 AND OX2=3 AND OX3=3 You may also save typing by using the ampersand (&) instead of the word AND, but the same result can be achieved even more briefly by simply doubling the comparator, in other words using == instead of = so that the formula becomes: OX1,OX2,OX3==3 The full list of available comparators is: =, <>, <, >, <=, >=, { and }{ (N.B. ~ may be used instead of <>) { should be read as “contains” and }{ as “does not contain”. These are used where the target value must (or must not) be part of the field but isn’t expected to make up the whole field. The use of { is the main source of those rare instances where we want all the items in a target list to be matched in a given field. e.g. Suppose we knew that someone’s house number was 17 and that they lived on “<something> Avenue” but the actual name couldn’t be remembered. In a database of addresses a search formula such as: ADDR{{17,Avenue (note the doubled comparator) would find it, whereas: ADDR{17,Avenue would find all those addresses where the house number was 17, regardless of street name, and all those addresses with “Avenue” in them, whatever the house number. You may invert the logic of a search criterion by putting NOT in front of it. To print all non-transition elements you could use: NOT (GP=T) Note the space after NOT, the need for brackets, and that the syntax isn’t GP NOT=T. You could equally well use one of the following: GP<>T or GP~T and may find either of these more understandable. To make multi-criterion searches either place tags and targets in comma-separated lists as described above or string search elements together with the connectives AND and OR. Use AND (or the ampersand, &) when a field must meet all of a set of criteria. e.g. GP=T & Z>50 & NAME{IUM for all transition metals with atomic numbers greater than 50 and names containing IUM. Use OR when a field need meet only one of a set of criteria. e.g. GP=L OR GP=A would find all lanthanide and actinide elements as the formula means “either L or A; I don’t care which”. AND and OR can produce ambiguous search formulae e.g. GP=1 OR GP=2 AND Z<50 could mean either “elements in group 1 or 2 (don’t care which) with atomic numbers less than 50” or “group 1 elements (of any atomic number) or group 2 elements whose atomic numbers are less than 50”. You probably want the former, but Powerbase will give you the latter. To get what you require use brackets to make the logic clear. In other words write it as: (GP=1 OR GP=2) AND Z<50 You could also write this as: GP=1,2 & Z<50 without the need for brackets at all. - 20 - 3.5.2 Numeric and other special fields in search formulae For most types of field the comparison with the target is made by character matching but for certain types the comparison uses the numeric value of the field. The fields concerned are: Numeric, Calculated, Record number, Sequence number, Day of month, Month number, Year Only the first of these is an editable field type but all may be included in search formulae. Suppose we have a Numeric field whose tag is NUM. If you entered the formula: NUM=5 the record would be included in the report if NUM contained 5, 05, 5.0 etc. because all of these have the same numeric value. If you had an Alphanumeric field called NUM the same search formula would only match records where the content was literally 5, i.e. the character “5”. This can easily catch you out. Suppose, for example, you want to print records for which NUM<8. You might be surprised to find records in which NUM contains values such as 55, 20, or 13 being printed, as well as those containing 4, 6, 2 etc! If this happens check what type of field NUM is. Unrestricted and Alphanumeric fields will give the above result; Numeric fields (and the others listed above) will give the result you probably want. You can force a comparison by numeric value for a field which consists of (or, at least, begins with) numerals, even though the field is not defined as of Numeric type, by enclosing the field tag in square brackets, e.g. [NUM]<8 would produce the desired result in the above example even if the field is Alphanumeric or Unrestricted. This is useful where you want to make a comparison but still allow the field to accept non-numeric characters. The comparison-by-value can only work in such cases if the number part of the field comes first. e.g. it will deal correctly with 55A, 20B, 13X but not with A55, B20, X13. 3.5.3 Using “wild-cards” in search formulae The use of characters “$” and “#” as “wild-cards” was described in 2.5.5 in connection with search-and-replace operations. They may be also be used in search formulae. “$” is used to represent a group of characters and “#” to represent single characters which do not need to be matched. e.g. If (still using the Elements database) you type: NAME=$ON you are, in effect, saying “find all the elements whose names end in ON; I don’t care what precedes ON as long as nothing follows it”. Powerbase will duly find CARBON, BORON, NEON etc. If you were to use: NAME=$ON$ You would find PLUTONIUM, POLONIUM but not CARBON, BORON, NEON; something must precede ON as well as follow it. (To print both sets of elements you would use NAME{ON.) Note also that: NAME=$TIN$ finds PROTOACTINIUM, PLATINUM etc, but not TIN itself. NAME=S$IUM finds all names which begin with S and end with IUM, e.g. SAMARIUM, SCANDIUM, and SODIUM. The effect of: NAME=S####IUM is somewhat different. You are, again, asking for names which begin with S and end with IUM but this time SAMARIUM and SCANDIUM would be found, but not SODIUM since you have specified exactly 4 wild-carded letters between the S and the I. Finally, to find any 5-letter name, regardless of the actual letters: NAME=##### 3.5.4 Comparing the contents of two fields A field tag (instead of a literal string) may be specified as a target, thus allowing two fields in a record to be compared to produce, for example, a list of all records in which the relevant fields have the same content. This might interfere with a “normal” query where the required literal target happens to be the same as the tag of another field. The problem can be overcome by enclosing the literal string in quotes. 3.5.5 Saving search formulae for re-use Choosing Save query from the Print submenu opens a Save box from which the search formula may be saved. By default the file is saved in PrintRes under the name Query. Selecting the Save as default switch on the Save box will - 21 - cause the file to be saved as the default query with the name !Query. If such a file exists in PrintRes it will be automatically entered in the Query panel whenever the Match window is opened. A default query file, in other words, behaves in a similar way to a default selection file as described in 3.4.2. Query files are of type &7f4 and are recognizable by the large Q in their icon. You may save as many Query files as you like and load them into the query panel by double-clicking on them. 3.6 Query by example ------------------------ After that lengthy description of the search formula method of querying the database we turn to the alternative: query by example. For brevity when comparing the two we will refer to them as SF and QBE respectively. Select the option switch at the top left of the Match window in order to use QBE. 3.6.1 What is QBE? The user is presented with a blank record and invited to type into the relevant fields the data which must be matched in order for the record to be included in the report. What you are saying in effect is: “I want a list of all records which look like this. I don’t care what’s in any of the fields I haven’t filled in, but the ones I have filled in must correspond to what I have typed.” e.g. in the Elements database if you wanted to print a list of all transition metals you would simply enter T in the Group field and then proceed with the report. The tag of the field isn't needed at all, whereas using a search formula requires you to type GP=T. Enter the data to be matched then either click with SELECT on the Print button of the Match window or else press the Print key. If you simply enter the required target strings Powerbase assumes that you want the relevant fields to match exactly, i.e the effect is the same as using “=” in a search formula. There are, however, other comparators besides “=” which may be used in search formulae. (see 3.5.1 for explanation and complete list). You may use any of these in a QBE query by placing them at the start of the string, e.g. {Avenue in an Address field would match all records where the field contained the word “Avenue”. An address such as “15 Acacia Avenue” could be found by this method whereas just entering the word “Avenue” wouldn’t work because it would require the field to read “Avenue” and nothing more. Wildcards may be used; e.g. you could print from the Elements database all elements ending in IUM by entering $IUM in the NAME field or all those whose symbol began with H by entering H# in the SYM field. You may specify a target list (see 3.5.1) to make the search include all records matching any item in the list. e.g. Leeds,Liverpool,Manchester in a Town field (if it will fit) would cause records with any of these places to be included. You can also specify a field list (equivalent to a tag list; see 3.5.1) provided that the fields form a contiguous group. The target string (which may be a target list, be wild-carded or be preceded by a comparator) is entered in the first field of the group. Press Return and enter " (double quote or “ditto” mark) in the next field and for the remaining fields of the group. (Pressing Return rather than moving the caret by means of the mouse ensures that you really are dealing with a contiguous group of fields.) 3.6.2 QBE vs SF So what are the advantages and disadvantages? QBE is very intuitive and avoids the need to bother with field tags or the minutiae of search formula syntax. On the other hand SF is more comprehensive and flexible: there are some things you simply cannot do with QBE. Some of the limitations have already been mentioned but here is a complete list. • Except where exact matches (“field=target string”) are required it might not be possible to fit the target string or list into the relevant field. One consequence is that you can't target a Date field for all dates prior to, say, 01-06-90 since dates fit their fields exactly leaving no room for the necessary < comparator. Such a search is perfectly possible with SF. • Searches involving multiple fields and the same target, i.e. those where FieldX or FieldY or FieldZ must match are only possible where the fields form a contiguous sequence. With SF it is possible to perform such searches on fields dotted about the record. • There is no equivalent in QBE to the @= (any field in the record matches) or @{ (any field contains) searches which are possible with SF. • You can't search for text in an external Text or Text Block field. You can with SF. - 22 - • You can't include Check-box fields in QBE searches. • Since QBE doesn't use tags you can’t force a comparison-by-numeric-value on a non-numeric field (See 3.5.2). • Calculated fields and those belonging to the Stamp class cannot be used by QBE because you cannot type into them! If your requirements involve only fairly simple searches then QBE might be just what you’ve been looking for, but if complex multiple searches are often needed then SF is the one to go for. It is, of course, a simple matter to switch between the two. As supplied Powerbase uses SF as the default query method and the Query by example switch will be deselected when the Match window is displayed. If you want to make QBE the default you can edit the relevant line of the Config file in !Powerbase.Resources to read Query QBE instead of Query SF. Don’t forget the space. Selecting the Print function will then produce the blank record without displaying the Match window at all. There is, of course, no Print button to click with the mouse after you have entered the target strings so you tell Powerbase proceed by typing the Print key after entering the data to be matched. 3.7 Other features of the Query panel and Match window ---------------------------------------------------------- At the far left of the Match window is a group of four radio buttons labelled Print, Count, Mark and Clear. Only one of these may be selected at a time and the default action button at the bottom right of the window reflects whichever one you select. When the Match window is opened it is always Print which is selected, this being the most often used feature. If you merely want to know how many records match a specified set of criteria, without printing them, select Count. The number of matching records appears to the left of the Cancel button (which merely closes the window). Mark and Clear are explained in section 3.8.2 below. The icon to the left of Cancel indicates the selected output destination (see 3.1) by displaying a representation of a window, a text-file icon, or a printer. In the latter case the icon will be shaded if no printer driver is loaded. Clicking with SELECT on the icon opens the Print Options window. Old, as stated earlier, retrieves the last search formula used. The action of the Case and Reverse switches has also been described (see 3.5.1). Help opens the Help window which offers another way of building search formulae which might appeal to beginners. Select the target field by cycling through the tags with the bump icons or by choosing from the pop-up menu. Choose the comparator by selecting a radio button. Type the target value into the writable icon. Place the caret in the Query panel writable icon and click Add to formula. The search element will appear at the caret. You may click on AND or OR and enter other search elements in the same way. If you wish to use the NOT button you must do so before clicking Add to formula. Powerbase inserts the brackets round the search element for you. Holding down Ctrl and clicking on a field with SELECT while the caret is in the Query panel causes the tag of the field to be entered in the search formula at the caret. This, together with the above method of constructing search formulae, largely overcomes the problem (especially when using someone else’s database) of not remembering what the field tags are. 3.7.1 Printing records from more than one subfile Reports are usually created from records in the currently-selected subfile which is displayed in the title bar of the record window. Just under the Query panel is the legend Incl.subfiles: and a row of numerals, 0-5. When a database is opened 0 will be highlighted, indicating that reports will only include records from subfile 0. If you change subfile by clicking on the appropriate keypad buttons you will see this highlighting move from one number to another, showing the selected subfile. You can, however, click on these numbers so that any or all of them are selected. Subfiles are deselected with a second click. When you create a report from more than one subfile the records are not merged into one alphabetically (or numerically) ordered list; the ordering starts afresh for each selected subfile. This isn’t really a problem because you can always sort the completed report on any field to produce a single, ordered list (see 3.1.1). 3.7.2 Including record number, key and subfile number A group of three option switches in a frame to the left of the query panel allow you to include record numbers, the current key, and the subfile number in a report. The latter is especially useful if you have created a report from several subfiles and then sorted it as described above. If you need to - 23 - keep track of which subfile a record comes create the report with File selected. These three special items are not saved with a print selection file and always appear on a report before any of the selected fields. 3.8 Marking records for inclusion or exclusion -------------------------------------------------- There are times when you want to print a number of records which have no obvious connection with one another: they may have a common feature which is obvious to you but none within the records themselves. Such a situation commonly occurs when you want to print a few mailing labels. No common feature means no basis for constructing a search formula. So how do you tell Powerbase which records you want to print? 3.8.1 Single records A small panel attached to the bottom of the record window* contains a check-box, Mark for printing etc., which you can tick to indicate that the displayed record is to be printed. Using the Search button or the browse controls you can call up each record you want and tick the box. You then simply select the required fields and print in the usual way. If no search formula has been entered then only the marked records will be printed. (Printing without a search formula when no records are marked gives the whole subfile as described in 3.5) If you do enter a search formula you will get the records which match the formula plus the marked records, whether the latter match the formula or not. A pop-up menu allows you to invert the effect of this feature so that printing without a search formula gives all records in the subfile except the marked ones and printing with a search formula gives all the matching records except for those marked. When the menu option is set like this (to exclude rather than include) the check box shows a red cross instead of a green tick. Next to the check-box is the Clear marks button which does exactly what it says. It is shaded when no record is marked. A further indication of whether records are marked is provided by the small rectangle at the far right of the Query panel which is coloured green or red, as appropriate, when any record is marked. This applies to the whole database, by the way, not just to the current subfile. Powerbase takes heed of marked records in any operation which involves the query panel, i.e. batch move/delete, global change, filter, export subset, export CSV file, as well as print. 3.8.2 Groups of records It is sometimes useful to be able to mark (or clear marks from) a group of records which fit a search formula. This is made possible by the Mark and Clear radio buttons on the Match window. By repeatedly selecting Mark and executing different query operations you can build up a set of marked records by stages then, if desired, selectively clear the marks from certain ones. Finally, you can print your carefully-tailored selection of marked records without entering a search formula at all. 3.9 Printing single records ------------------------------- There are two methods of printing the displayed record only: (a) Hold down Shift whilst clicking with SELECT on the Print button of the Match window or type Shift Print on the keyboard. The highlighted fields of the displayed record are printed using the currently-selected print format as determined by the setting in the Print options window. If no fields are selected the action is as described in 3.4.2; Powerbase will use the default selection if it exists or, failing that, print the primary key fields only. (b) Follow Export selected from the main menu to a Save box from which may be saved (or dragged to the printer or into a wordprocessor document) a text file containing the highlighted fields of the displayed record. If there are no fields selected the menu entry is shaded. 3.10 The Print options window -------------------------------- To display this window you can choose Options from the Print submenu, type Ctrl Print, or click SELECT on the icon to the left of Cancel on the Match window. Features such as Destination (see 3.1), Format (see 3.2) and the Sort on facility (see 3.2.1) have already been dealt with extensively. The rest are covered here. Default settings appear in brackets after the name of the feature. Scrollable lists (as single row) These may be printed in two different ways. - 24 - The default is for all the cells in the list to be made into a single row with semicolons separating the data from individual rows of the scroller. This can result in very long lines indeed. An option switch (Shrink row - ON by default) causes as much white space as possible to be removed, but lines could still be too long for the printer. The alternative format puts the data from each row of the scroller on a separate line so that the data aligns in columns. This occupies less room horizontally but much more vertically. Experiment! Headings (tags) appear at the head of reports in all formats except Label unless None is selected. Expand codes (OFF) causes extra data from a validation table to be substituted for (or added to) the coded data in fields linked to such tables (see 5.2). Expand headers (ON) will show the expanded versions (see 5.2) of the target values for fields linked to validation tables in the list header. Turning the option OFF causes the target values to be shown exactly as typed in the search formula. Upper case (OFF) causes all textual output to appear in capital letters. Print header (ON) causes the printing of header lines at the beginning of each page. The header includes the following information: • The name of the database plus a title, derived from the search formula, making it clear on what basis the records have been chosen. • The index used for the ordering, plus the date stamp (if appropriate switch selected). • An optional description entered in the Title writable icon. • The column headings as described above. The following switch (on p. 1 only: default OFF) limits the header to the first page of a report. Print footer (ON). Reports in Horizontal and Table format normally end with a footer which specifies the number of records printed. If the output includes Numeric fields and column calculations have been selected (see 6.4) then the results of these too will be part of the footer. Date stamp (ON) makes the date and time when the report was created appear as part of the header. Shrink list (ON). In Horizontal and Table format the width of columns is determined by the maximum defined length of the fields included in the print selection. These lengths are often greater than the length of data actually present in the fields, resulting in a lot of “white space” between columns. With this option ON the surplus space will be automatically removed. Even if it is OFF you can still remove white space via the Report menu (see 3.1.1). Output to Printer always removes white space whether this switch is ON or OFF Page numbers (OFF) allows page numbers to appear at the bottom of each page of a report. This feature works quite independently of the Print footer switch. Page length (0) determines the total length of page, including header, footer and top margin, for destinations other than Printer (for which the page length is determined by the printer driver). The default value of 0 means no division into pages at all, but you might want to alter this if you drag text-files to the printer. An A4 page is 70 lines long, but you won’t be able to print on them all and page-feeds might occur in the wrong place. Look at Edit paper sizes on the iconbar menu of Printers. Subtract the displayed top and bottom text margins from 70 and enter the value in place of 64, if necessary. In Vertical format Powerbase will try to avoid splitting a record between pages, but this can happen if the report includes Text or Text Block fields of greatly varying length. (It will also happen if the number of fields to be printed exceeds the length of the page!) Text width (A) specifies the line length used when printing in Vertical format. A means “Auto” and lets the program calculate the value. You may enter a value of your own (e.g. 70) to override this. Spacer (1) specifies how fields printed on the same line will be separated. Fields are first padded with spaces to the maximum width of the relevant data field (but see Shrink list above) and the spacer string is then printed before starting the next field. Three interpretations of the contents of this setting are possible: • A number by itself means use the specified number of spaces. • A number followed by a non-numeric character means use a string of the specified number of that character, e.g. “3-” means 3 hyphens. • A non-numeric string is used “as is”, e.g.“|” or “ | ”. If the former is used when outputting to Printer continuous vertical rules will be placed between columns in Horizontal format. - 25 - 3.10.1 Saving print options files All the settings in the Print Options and Printer Setup windows may be saved as a Print Options file. Clicking Save choices with the in database radio button selected brings up the familiar Save box. By default the file is saved in PrintRes under the name PrintOpts. As with Selection (see 3.4.1) and Query files (see 3.5.5) you can save a default options file called !PrintOpts by selecting the Default options button on the Save box and Powerbase will load this whenever the database is opened. Options files have a large P on their icon and a filetype of &7f5. You may save as many as you wish and load them with a double-click. If the in Powerbase radio button is selected the options are saved as the Powerbase default and no Save box is displayed. Load default reloads this file, overwriting any changed settings. 3.11 Field analysis reports ------------------------------ The Field submenu has an Analyse option which allows you to print a breakdown of the field contents under certain special circumstances: (a) If the field is indexed the menu entry will read Analyse index. When chosen it will produce a list of all the values in the index with the number of times each one occurs. e.g. A database of college students might have a field for the school of origin. If there are 20 different schools and if the field is indexed then a list of those schools will be generated showing how many students came from each school. (b) If the field contains an 8 or 10 character date (e.g. 19-10-42 or 19-10-1942) the menu entry reads Analyse months and a breakdown by month will be printed. There will be a line for each month showing the number of records for that month. For example, this could be used in an orders database to find out quickly how many orders were received or dispatched each month. This feature works on editable Date fields and also on Date stamp8 and Date stamp10 fields in the Stamp class. It is, of course, possible for a field containing a date to be indexed. Action (a), above, takes precedence in such a case. You can, however, force action (b) instead by first selecting the field with ADJUST, then choosing from the menu. For cases not described above the menu entry simply says Analyse and is shaded. Printing is always to a window (from which the report may, of course, be saved); the Destination buttons in the Print options window have no effect. 3.12 Subsidiary indices and printing speed --------------------------------------------- Try the following experiment using the sample database Elements : (1) Create a subsidiary index, if one doesn’t already exist, on the Group field (see 7.1 for the way to do this). Don’t make the index case-specific (2) Enter the formula GP=T and click Print with ADJUST so that the Match window remains open. Note the time taken for creating the report. (3) Deselect the Case button and repeat the process. The time will be shorter. You might also notice the brief appearance of a numeral (probably 1) in the small rectangle to the right of the search formula in the Query panel. The speed increase is most noticeable with a slow machine such as an A3000. On a StrongArm RiscPC and with such a small database both times will be so short that the user’s reaction might be “So what?” but when dealing with databases of thousands of records the improvement can be quite dramatic. What happens is that Powerbase detects the fact that there is an index based on the Group (GP) field, goes straight to the first occurrence of “T” in that index then prints records for as long as the key remains “T”. The number briefly displayed in the Query panel is the number of the index being used and its appearance shows the user that a subsidiary index is being used. If you look at the header of the report you will see that it says “Ordered by GP index” whereas the first time it said “Ordered by PrimaryKey”. The speed-up only works when the all following conditions are met: • The search formula must include a simple comparison for equality, i.e. of the type TAG=target, without alternatives: no OR, no tag list, no target list. • The whole of the field represented by TAG must be indexed. • The Case switch on the Query panel must be set to agree with the index, i.e. it must be selected if the index is case-specific and deselected if not. - 26 -