PROFILE 2 Spreadsheet File Handling System 1984 Copyright McGRAW-HILL Book Company (UK) Limited This tape is copyright. You may not record or copy PROFILE 2 MASTER or PRODEM by any means or for any reason. The first purchaser of this cassette only is permitted to make copies, for his own use only, of the PROFILE 2 spreadsheet program generated by PROFILE 2 MASTER. ISBN 07 084741 X An introduction to spreadsheet filing PROFILE 2 is a spreadsheet filing system, for use with the 48K Sinclair ZX Spectrum computer. You can use it to maintain records of almost any kind, containing numeric or alphabetic information. You can design your own system according to your requirements — anything from a few, very large records to several hundred smaller records. The complete system — known as a file — can be saved on tape and amended at any time. Individual records are divided up into various fields, each field containing one particular piece of information: it might be a name, a price, a date, etc. PROFILE2 arranges the file in the form of a spreadsheet — the equivalent of a single, very large piece of paper on which is written all the information you put in. Each record occupies one line of the spreadsheet. Since the spreadsheet is likely to be much larger than could possibly be fitted onto the television screen, the screen is made to act as if it were a movable window through which you can look at the spreadsheet. Simply by using the cursor controls, you can move any part of the spreadsheet to examine or change it. Just storing the information this way is useful enough, but PROFILE 2 enables you to do much more. As you will see as you read this manual, PROFILE 2 has extensive processing facilities that can be used to change, manipulate and print out files or parts of files, either on the ZX printer or via a suitable interface (such as the centronics interface by Kempston Micro Electronics) on a full-size printer. The cassette supplied has, on side 1, a demonstration file called PRODEM. This is a file already containing data, and you can use it as a ‘training ground’ before setting up your own file. On side 2, the PROFILE 2 MASTER is recorded. This program allows you to set up your own file, specifying the fields in each record, and their sizes and type. The PROFILE 2 MASTER generates a PROFILE 2 file, that can subsequently be saved on tape, copied, and reloaded as required. Read this manual through at least once, then load side 1 of the cassette — LOAD “prodem” — to enter the world of spreadsheet filing. Microdrives PROFILE 2 is fully compatible with Interface 1 and the microdrive. If a microdrive is present, the program detects it and assumes you are going to use it. PROFILE 2 always uses drive 1. You can use PROFILE 2 MASTER to create spreadsheets designed to use microdrive or cassette storage. See page 10, ‘Setting up a file’. 3 PROFILE 2 The display PROFILE’s display acts as a movable window through which you can see the spreadsheet. The middle 20 lines show the contents of the file, and the top line shows the headings. The last line, in white, acts as a reminder of the record title, when you wander off into the remoter areas of your spreadsheet. The file isa large, rectangular sheet. Each line contains one record divided by the headings and the background colours into fields, each of which shows a particular part of the record. The field titles (each just four letters long) are shown in the top line on a bright background, each at the left of the field. The inverse block is the cursor. The bottom line shows a copy of the first 30 characters of the line in which the cursor is currently positioned, and at the extreme right-hand end a single “U” or “L” in a white square indicates whether the spreadsheet is unlocked or locked against overtyping (see page 5). Moving around The controls are on the top line of keys. Used normally they will produce the symbols printed on them, but with the CAPS SHIFT key they function as signals for special purposes. Experiment with the four cursor keys — hold CAPS SHIFT firmly down. The cursor moves around the display at your will. When you reach the edge of the screen, watch carefully! As you ‘cross the boundary’, the whole display moves over. How the window moves The window moves in stages to avoid confusing moving displays. It can move to any section of your file, but never beyond its boundaries. The top line of keys — plus ENTER — pilots you across the spreadsheet. Although you can use the cursor to move anywhere on the spreadsheet, there are special control keys to move you around more rapidly. The control keys move you left or right one field at a time, or up or down ten records at a time. These keys are called the window control keys, and they are in the same order as the Sinclair cursor keys — left, down, up, right — remember this and moves will soon come easily. Summary of movement keys Cursor control: CAPS SHIFT 5 (←) — left CAPS SHIFT 6 (↓) — down CAPS SHIFT 7 (↑) — up CAPS SHIFT 8 (→) — right Window control: CAPS SHIFT 2 — left CAPS SHIFT 3 — down CAPS SHIFT 4 — up CAPS SHIFT 9 — right ENTER — press once to move the cursor to the lefthand edge of the window, once more to go to the beginning of a record. ENTER also moves down one record if possible. 4 Practise using these controls. The value of the bottom display line will be clear. Wherever you are on the spreadsheet, the line will show the first 30 charcters of the record under the cursor. Entering new records The normal and best place to do this is at the end of the file. Navigate your way there using the controls described above, then just type them in. As you do so the file is automatically enlarged. Records can be sorted into the correct order later. You can also amend records by simply typing over them. The delete (CAPS SHIFT 0) key will kill the character under the cursor and move you one space backwards or, of course, the space key will overwrite. To remove unwanted records completely, see the next section. Using the ENTER key makes, for example, entering a column of figures simple. Line the file up using the window controls so that the field you want to fill is on the left of the screen. Then enter the numbers one by one, pressing ENTER once only after each entry. PROFILE 2’s display facilities will take care of the rest, and, of course, you can always see which record you are filling by glancing at the bottom line of the screen. If you like your figures nicely tabulated with decimal points under each other, this can be done using the PROFILE 2 processing facilities. You cannot enter or amend records if the file is locked! See below. Processing facilities PROFILE 2 has extensive facilities to process whole files. They are accessed by using CAPS SHIFT 1. When you press this combination the normal display is replaced by a page with a choice of keys to press. Press any key not on the list to ~ return to the main display. The facilities are briefly described below, then again in detail later. Menu options (CAPS SHIFT 1 for menu) ? Information on the number of records entered and available space in the file. d Delete the record on which the cursor is positioned. You are asked to confirm this choice. f Find all the records which satisfy one or more conditions. i Insert a blank record above the cursor position. I Lock/unlock against overtyping. n Check numerical entries for validity (i.e., whether they are acceptable for the purpose of calculation). o Order (i.e., sort the records alphabetically). p Print on screen or printer a portion of selected records. You can choose which records and which fields (or part fields) to print. r Replace a field in selected records by a calculated value — string or numeric. This 1s a very powerful facility. s Save PROFILE 2 and its contents for future use. t Total numeric values for selected fields. v View whole records. 5 Computer files Tne main display in PRODEM has already given you a fair picture of how a computer file can be organized. The file is the entire block of information, in this case about a collection of cars. It can be broken down into smaller units — records, fields and characters. A record consists of all the information concerning one car. On the screen a record occupies one line and all lines have the same length. The fields of each record are also of fixed length — a field holds information on one particular facet of a car — its make, model, year and so on. PROFILE 2 allows you to ask complicated questions and give detailed instructions about fields, e.g., find all Ford cars with a sale price over £3000, or add 10% to the sale price of all second-hand BL models. To be able to do this sort of thing PROFILE 2 must be provided, when the file is set up, with a certain amount of essential information. The requirement for each field is: 1. The name, which must begin with a lower-case letter and contain four characters. 2. The length or number of characters in the field. 3. Whether the field will contain numeric or character information. Numeric fields will be used in calculations, and contain numbers typed in any of the usual ways (excluding ‘scientific’ notation). Character fields may contain any symbols obtainable on the keyboard. Note that just because a field contains numbers it is not necessarily a numeric one — the year field in the demonstration is a character field, because no calculations will be carried out on what it contains. 4. The number of decimal places that will be used in displaying the results of calculations — a maximum of four is allowed. When you set up your own file with the PROFILE 2 MASTER you will need to refer to this section of the manual, but for the moment it will provide useful background information. In PROFILE 2, when you choose one of the more sophisticated facilities, you will be able to refer to the fields by the names that appear on the headings line — with your own files these will have been chosen by you — whether they are character or numeric in content. You must, however, use the field name in exactly the way it appears on the heading line and be rather careful when carrying out calculations or replacements. Mistakes will not be fatal (to your information), but might be irritating! Using the menu options If you switched the computer off, turn it on again and reload PRODEM. Follow this section through and check the results for each operation. Each process is carried out from the main display by first pressing CAPS SHIFT with 1, then the appropriate (lower case) letter. (Note that you must not put the computer into CAPS LOCK mode before loading PROFILE 2.) ? Information. Displays the number of records entered and tells how many more are available. PROFILE 2 expands as more records are added, so you never have to use more computer space (and cassette tape) than is necessary. You will find that occasionally, as new records are added, the command mode display comes up automatically, acting as a reminder to check on the room left. When (if ever) you reach the end of the available space, PROFILE 2 will refuse to proceed any further, though you may, of course, delete some records in order to add more. 6 i Insert a record. This operates instantaneously, and moves the cursor line and all those following down to create a new empty record. The cursor will be positioned at the start of that record so you can immediately type in your data. You will not be able to insert another one until something has been entered, and, of course, if the file is already full, no new record can be created. d Delete a record. This also operates instantaneously, but only after the record has been displayed so that you can check you really have got the right one. On the whole it is best not to delete unless you have to — you never know when the information might be needed. Often you can use a field to show that the record has slipped out of use for the moment — in PRODEM, for example, we could create a field called ‘sold’ and enter there the date of sale. o Order (sort) the records. Enter a few new records at the end of the file before using this one. It displays the message ‘SORTING’ while work is being carried out, which is changed to ‘SORTED’ as soon as it completes. You can expect most sorts to be carried out in under 10 seconds, rising with the number of records. If you use this option frequently and add records at the end of the file, sort times will be very quick. Press any key to return to the main display. Sort works on the whole record, so, when you set a file up, consider carefully the order in which you enter the fields. By putting the make first in PRODEM, we made sure that cars of the same make would be put together. v View. One disadvantage of the ‘window’ approach is that, on the main display, an entire record cannot be seen. Position the cursor anywhere in the file and use this option to see how PROFILE 2 takes care of your needs. You can move to adjacent records by using the up and down arrow keys (with CAPS SHIFT). When you reach top or bottom of the file, the display will repeat. Use any key other than the arrows to return to the main display. l Lock. This option toggles the file between ‘unlocked’ and ‘locked’ against overtyping. The file status is displayed as a ‘U’ or ‘L’ in the lower right-hand corner of the spreadsheet display. n Check numeric fields. When calculations are carried out, PROFILE 2 expects numeric fields to contain sensible information. This option allows you to check and correct before calculating. Before using it with PRODEM, you might like to make a few nonsense entries in, for example, the cost field. Enter the option in the usual manner, and give the response cost when asked for a field name. After a very short time you will either be given the message ‘no problems’ or be told that there is an error. If the latter happens, pressing any key will position the cursor on the record where the mistake is so that it can be corrected. Note that the program reports an error as soon as one is found, so, if there are several mistakes in the entries for a numeric field, you will have to repeat the option until you get the ‘No problems’ message. As previously mentioned, PROFILE 2 does not handle ‘scientific’ notation, nor does it interpret a blank field as zero. See also the section on limitations at the end of this manual. If you want to know how this routine and the one for sorting works, see Information Handling for the ZX Spectrum by C. A. Street (McGraw-Hill, Autumn 1983). f Find. We are now entering the section where extra care has to be taken in giving our commands. In find, as with some later options, you are asked to enter a condition. The file is then searched for records that meet the condition and any that do are displayed. The message asking you for a condition displays the last one used and asks for a new one. There are three ways to respond: 7 1. Just press the ENTER key. This means that all records will satisfy the condition, i.e., find will act like view except that it starts from the top of the file. 2. Enter 1 (a lower case ‘L’). The previous condition will be used. 3. Enter a new condition. Here are a few examples that you can use with PRODEM. make = “BL” modl = “Cortina” cost < 3000 sale < = 5000 AND cond = “New” sale < 4000 AND (modl = “Cort” OR modl = “Mari”) (< 1s ‘less than’, > is ‘greater than’). As you can see the facility is very powerful, but if you make a badly phrased entry, things will go awry, and you will get the message at the very bottom of the screen ‘Nonsense in Basic’. If that happens, do not worry because the file will still be intact. Just type GO TO 488. Note that you must use the single key for GO TO (the ‘g’ key) and you must not spell out ‘GO TO’ in full. (There is a flashing reminder on the screen). Here are the rules to consider. 1. Field names must be entered as they are displayed in the headings. 2. AND, OR, NOT, <> (not equal to), < = (less than, or equal to), > = (more than, or equal to), must be obtained using the Sinclair single key entry system and not built up from separate characters. 3. Character and numeric conditions may be freely mixed, but groups of characters must be enclosed in inverted commas. The type of condition must match the type of field! 4. Character matches act upon the number of characters within the inverted commas and no more. So modl = “M” will be satisfied by Metros, Marinas and Minis. This allows useful shorthand. 5. Brackets should be used to make the condition clear. Remove them from the last example above and you will see that it becomes ambiguous. 6. Spaces can be used except within field names and at the very beginning. PROFILE 2 will intercept many mistakes, but for those it does not deal with, type GO TO 400. Users of PROFILE 2 who are familiar with BASIC will have no trouble with using multiple conditions, as the structure is identical to that of Sinclair BASIC. For those unfamiliar with BASIC, remember that AND is used where both conditions have to be met, or is used where either (or both) conditions will do, and NOT finds anything that does not conform to the condition. For example: modl = “Metro” AND sale < = 3000 looks for Metros costing £3000 or less. mod! = “Metro” OR modl = “Sierra” looks for all Metros and Sierras. make = “BL” AND NOT modl = “Metro” looks for all BL cars on the list except Metros. 8 t Total. This operates on numeric fields only, of course. It allows you to add up all the values in a field with or without a condition — so you can find the cost price of all cars or just those for which medl = “‘Ford”’, for example. The condition is entered first, just as in the last option (you may find it useful in the early stages to use that choice first). You are then asked fora field name. If you give a numeric field name and it contains no nonsense entries, the total will be displayed after a short interval. Otherwise, PROFILE 2 will try to intercept the mistake, leaving you to type GO TO 404 if all else fails. To avoid this possibility, use the ‘n’ option before totalling. p Print. This option can be used to produce reports on the file to your own format — everything from address labels to price lists. A condition can be entered (or the last one reused), and the output can go to screen, printer, or both. A very useful feature is that PROFILE 2 remembers the last print command given as well as the condition, so you can perfect your layout on screen before committing yourself to print. The last print command is not displayed (the condition is), but it can be reused by typing l (lower case ‘L‘) when you come to that section. First enter the condition as usual. You are then asked to ‘enter print fields below’. Here is a typical response: make + modl + £7 + sale which would produce a list looking like this: BL Marina £3200 Ford Cortina 1.6 £4200 etc. Fields and groups of characters (in inverted commas) must be separated by plus signs. There are two very useful facilities which can cater for special needs. 1. Field names can be followed by a ‘string slicer’, just as in BASIC. For example, if you have fields snme and fnme in your file, which hold ‘Smith’ and ‘John’ respectively, then fnme (1) + ‘“.”> + snme in the list of print fields will produce J.Smith. Slicers may be in any of the forms allowed by BASIC —e.g.,(3 TO 5), (2 TO), (TO 4), but you must remember to obtain the ‘TO’ using the Sinclair single key entry system, not by using T and O. See the Sinclair manual, pages 51-53 for more detailed information about string slicing. 2. Printouts on several lines may be obtained using N$ which provides a ‘new line’. So for address labels you could use: fnme (1) + ““.”” + snme + NS + adr1 + N$ + adr2 + NS +. . . etc. giving: J. Smith 59 Fogmantle Road Upper Twistletown Tangleshire (N$ must be entered with a capital ‘N’). r Replace. This is the most powerful of PROFILE 2’s commands. You can enter a condition first, to select records in which a replacement will be done, according to the second part of the command. For users familiar with BASIC, the replacement equation consists of a statement similar to a BASIC LET command, but without line number and without LET. Examples: 9 cost = costs * 1.10 cost = cost + sale/10 snme = snme (TO 12) + fnme (1) taxp = (saly—alls)*0.33 Notice that string as well as arithmetic processes can be carried out. The right- hand side of the ‘equation’ can contain any sensible combination of fields and operations, including string slicers where characters are involved, but please note that the left-hand side must consist of nothing more than the name of one field. Any attempt to use a slicer there or a combination of fields will result in a ‘Nonsense in Basic’ error, whereupon it will be necessary to type GO TO 404. Once again. the structure of ‘replace’ is identical to that of Sinclair BASIC. For those unfamiliar with BASIC, remember: + is plus — is minus / is divide + is multiply You can use any mathematical functions available on the Spectrum, however, and for more complex calculations you should read the Sinclair manual. Before you use this command on important information, you are advised to: 1. make a copy of your tape, if additions have been made since loading, and 2. test any numeric fields using the n command. A useful replacement command is, for example: cost = cost which has the effect of tabulating all your entries in the cost column in right justified form with the decimal points aligned. PROFILE 2 is not designed primarily as a calculation tool, and its arithmetic should not be regarded as accurate to the last decimal place. If you want total accuracy to two decimal places, it would be best to specify three when setting up. s Save the file. This command outputs PROFILE 2 and your file to the cassette recorder or microdrive 1. It is suggested that you keep two copies of each file, so, if anything goes wrong with one, you will be able to recover the file from the other. As suggested above, it might also be wise to save a copy of the file before undertaking complex replacement operations — PROFILE 2 will carry out what you ask, but you may find that you did not ask exactly what you meant to! Error messages In certain (very rare) circumstances, you may get a ‘RETURN without GO SUB’ message or a repeat of another error message if you have to type GO TO 400. If this happens, use GO TO 170 instead. Empty files PROFILE 2 does not like files that are completely empty, so you cannot delete the very last record in any file. If you want to set up a new file, you should use the PROFILE 2 MASTER program to create it. Similarly, you should not attempt to order, select or otherwise manipulate any file before you have put any data in it! 10 Setting up a file Remove and replace the power plug to clear the Spectrum completely. Put the tape in your recorder, at the beginning of side 2. Load the program with LOAD “promaster”. When the title page appears with the flashing ‘STOP THE TAPE’ sign, stop the tape, but do not rewind it. Pressing any key will start the PROFILE 2 MASTER program, which takes you through the sequence needed to design your own file. Think carefully about the design of the file, and once the file is finished, try it out before putting in lots of data — you may have second thoughts. If the Spectrum has a microdrive connected, PROFILE 2 MASTER assumes that you will be using microdrive 1 for storing files. If there is no microdrive present, tape files are used. It is not possible to mix tape and microdrive storage; PROFILE 2 MASTER designs a spreadsheet for one or the other, according to whether or not the microdrive is present when the program is run. Before going on, re-read the section of this manual on Computer Files, page 6. Limitations on fields are as follows: 1. Name must be four characters long, and must start with a lower case alphabetic character. 2. Each field must be at least 4, and at most 28 characters long. If you need a longer field than this, you can treat two adjacent fields as one for most purposes. When specifying the length of number fields you must make sure you leave enough room for the decimals. Thus a field intended to hold up to 9999 would need to be four characters long if decimals are not required, but eight characters long if you want three decimal places, the decimal point itself requiring one character; 9999.000 3. You must define each field as a ‘character’ or ‘number’ field. Remember that you cannot carry out any calculations on character fields. 4. You must specify the number of decimal places required. If you do not want decimals, or if the field is a character field, you should enter 0. In setting up a file, it is wise always to allow at least one character more than you think you will need for a numeric field! But in the case of character fields it is not a good idea to reserve more space than you want. As you add records PROFILE 2 expands automatically, but each new record uses a fixed amount of the computer’s memory. Unused field space is wasteful and will unnecessarily limit the number of records the computer can hold. PROFILE 2 MASTER lets you use the PROFILE 2 format to fill in information about the file — you should be familiar with this from using PRODEM. Once you have put in all your field definitions, press 1 while holding down CAPS SHIFT. You are asked to confirm that you want to go ahead. PROFILE 2 MASTER next checks the entries (and points out any mistakes you have made), and, if the definitions are acceptable, invites you to ‘Start the tape’. You should now start the tape playing again, from the point at which you stopped it after loading the first part of the program. This loads the main PROFILE 2 program, passing to it the information about your own file structure and over-writing the PROFILE 2 MASTER program. When the program is loaded there will be a short pause, after which your PROFILE 2 spreadsheet will appear on the screen. Do not try to go into the menu before entering any data as some of the options, if carried out on a completely empty file, will result in chaos! Having 11 entered some data (the first character of the first field is enough!) you can use menu option s to save your PROFILE 2 file and its contents. The file is saved (and loaded) under your chosen file name. Reload the file and contents with LOAD “filename”. Microdrive files When using microdrive files, each ‘generation’ of spreadsheet is given a number following your file name. For example, if the file is called “address”, the first spreadsheet will be saved as “address 0” the second as “address 1”, and so on, up to “address 99”. After 99, the number resets to 10 (not zero) and begins to count up again. Using PROFILE 2 with a printer PROFILE 2 operates the ZX printer directly through menu option p. If you wish to use a printer other than the Sinclair or Alphacom 32 printer, you should use a centronics or RS232 interface unit. PROFILE 2 will operate with systems that recognize the LPRINT statement. If the printer interface requires software to drive it, any such software should be in high memory, protected by a suitable CLEAR command. Full details will be supplied with the interface unit. HELP! If you have problems . . . Just fill in the enclosed card, stamp and post it. We provide a quick (and free) - advice/exchange service. If you bought the tape from a shop, please don’t take it back; use the card, but keep your receipt. DON’T send the tape back at this stage. Important: We cannot offer advice about modifying the programs on this tape or ‘about applications outside the range considered normal for this type of software. 12