SUPERBASE README ================ This document contains information about a number of features in Superbase Professional and the Forms Editor which could not be fully described in the User Guides. If you have loaded the Readme file by clicking on its icon, you can scroll down the document by pressing the space bar. If you are in the Superbase Text Editor, use the down cursor key or the scroll bars at the side of the window. For a list of the topics covered in this document, see the README CONTENTS on the next page down. README CONTENTS SUPERBASE PROFESSIONAL Using Superbase Personal Files Edit Form Edit Form on the Amiga Reorganize Using the Lookup Function Amiga Requester Default Keys Keyboard Equivalents New Editing Controls The Current Record Button Passwords Numeric Entry Ram Disk Support (Amiga only) Reserved Words Graphics and Form Printing Using Superbase as a Relational System Further Rules for Constructing Queries Superbase Start Options DATABASE MANAGEMENT LANGUAGE (DML) Changes to DML New Commands User-defined Menus FORMS AND FORM HANDLING Changing Text Fonts Using External Files in a Form The Amiga Colour Palette Changing the Colour Pallette Report Functions Images FORM CALCULATIONS Calculation Types Calculation Prefixes MISCELLANEOUS FORTHCOMING FEATURES SUPERBASE PROFESSIONAL Using Superbase Personal Files ------------------------------ Superbase Personal is upwardly compatible with Superbase Professional; so if you have upgraded from one to the other, you can use any of the files that you created in Superbase Personal. (It doesn't work the other way round: Professional files cannot be loaded into Superbase Personal.) However, there a number of differences in the way the two programs store file data. You should take these into account when you are loading Personal files into Professional. When you save the files, Superbase Professional will automatically convert them to its own format. But in some cases you may need to edit your Personal files before you can use them. Here is a checklist of the points to bear in mind when converting files from Personal to Professional: * Professional query files include the details of any database files associated with a query and these files are opened automatically when the query is loaded. If you use a Personal query in Professional, you will need to open the database files yourself before loading the query. Then save the query in order to convert it to the Superbase Professional query format. * Although Superbase Personal does not offer a Constant field type, it allows you to create a special type of calculation formula which acts like a constant formula. For example, if you define a single word formula such as "London" or TODAY it will have the same effect as the equivalent constant formula in Superbase Professional. When you load a Personal file into Professional, calculated fields will be defined as CLC RDO fields. If they were originally intended to act as constants, you should change the CLC attribute to CON by selecting Edit File and editing the file definition. You may also want to remove the Read Only attribute. * The list of words that are reserved for the use of the system is more extensive in Superbase Professional than in Superbase Personal (see the section on Reserved Words further on in this document). As a result, you may find that some of the field names in your Personal files are treated as reserved words in Superbase Professional. For example, Professional offers a large of number of new functions such as YEAR and HRS. If you have used YEAR as a field name, it would now become invalid. Before loading a Personal file, check that the field names are valid; if they are not, change them by editing the file definition. Edit Form --------- The Project menu option, Edit Form, runs the Forms Editor as an additional task if it is not already in memory, provided there is sufficient RAM capacity. If you have opened a form in Superbase, you can use this option to edit it in the Forms Editor and then return to Superbase. For Edit Form to work, Superbase must be able to locate the Forms Editor program. First it looks for the program in the current directory and then in the root directory of the current device. If this fails, Superbase will look in the parameters directory as specified in the Options dialog. If the Forms Editor still cannot be found, Superbase will assume that it resides on a disk with the volume name SBFORMED:. If this disk is not present in the system, and you have not used the CLI ASSIGN command to specify the location of SBFORMED:, then an AmigaDOS requester will prompt you to insert it. If the Forms Editor is already running when you select Edit Form, then the Forms Editor window is pulled to the front and made active. The current form in Superbase will also be selected for editing. To Return to Superbase you can use the depth arrangement gadget to push the Forms Editor screen to the back. Reorganize ---------- The primary application for Reorganize is as described in the User Guide. It allows you to reclaim disk space after deleting records from a file, and makes disk operations with the file more efficient. This option can also be used to recover records after their data has been corrupted; i.e., when an attempt to open a file produces a disk error message. Reorganize steps over any errors it finds in a file and retrieves as many valid records as it can. The records which have been damaged will be lost when the new file is created so Superbase informs the user with the message: "New file has different record count". There may also be circumstances in which Reorganize can be used to recover data which could not be accessed before. For example, if you turned off the computer after storing new records in Batch mode, the details of these records would not be stored in the file header. Reorganize rewrites the file header so that the records are included in the new file. In this case, it issues another error message (as well as the one above): "Source file size incorrect in header". Note that the manual suggests that the best way to recover data from a disk error is to Export the remaining records and then import them to a new file. This method has now been superseded by Reorganize. Using the Lookup Function ------------------------- There are several points to note in connection with the LOOKUP function: * When you are creating a validation formula (in a file definition) that uses LOOKUP, you should always enter the function's arguments by typing them into the box at the bottom of the validation dialog. In other words, even if your LOOKUP formula contains a field which is shown in the field list above, you should type the field name into the box instead of clicking on it. * It is important to include the file name as an extension to any field name used with LOOKUP. This applies to all fields irrespective of whether their names are unique to one file or not. For example: LOOKUP(Forename.Address,Firstname.Customers) is correct, while: LOOKUP(Forename,Firstname) may cause problems. * LOOKUP will not work correctly with date/time and numeric fields that have null values -- i.e., fields that have been left blank. If you wish to use LOOKUP on fields of this type you must force the first argument to give a null value when empty. For example LTRIM$(DATE$(date field)) will give a null string when the `date field' is empty. Amiga Requester Default Keys ---------------------------- Many of the dialogs in the Amiga version of Superbase accept Return and Escape as keyboard equivalents to clicking on the OK or Cancel buttons. Press the Return key to select OK, press the Escape key for Cancel. In cases where a casual OK might be destructive, Superbase does not allow the Return key equivalent. Keyboard Equivalents -------------------- In addition to the keyboard equivalents described in the User Guide, Superbase also provides the following: Alt/Amiga B Toggles Bold (text style) on and off in the Text Editor Alt/Amiga U Toggles Underline on and off Alt/Amiga I Toggles Italic on and off Alt/Amiga P Toggles Plain on, bold, italic, and underline off Alt/Amiga B Toggles Batch mode on and off (this option is only available when the database window is active, otherwise Alt/Amiga B acts as a keyboard equivalent for the Bold text style) Alt/Amiga A Select the program command line window -- equivalent to selecting Command from the Program menu. Note that `Amiga' indicates the right Amiga key. The keyboard equivalent for Set Paging, Alt/Amiga P, is no longer available. New Editing Controls -------------------- Two new controls have now been provided to allow the user to copy lines in the Program Editor and Text Editor. CTRL L is used to select the line you wish to copy; move the cursor to any point on the line and press CTRL L. CTRL R inserts a copy of the line that has previously been selected with CTRL L; move the cursor to the point in the program or document where the line is to be inserted and press CTRL L. A copy of the line will then be inserted below the cursor. Current Record Button --------------------- By now you will have noticed that the current record button on the Control panel does not agree with the illustration in the User Guide on page 1-8. Whereas the guide shows a single triangle (as in Superbase Personal), the program now has two triangles pointing in opposite directions from each other. The reason for this is that, in Superbase Professional, the button has two functions. As well as acting as the current record button, it also allows you to change current page in a multi-page form. In the first capacity, it is used to redisplay the current record after the record data has been overwritten or removed from the screen by some other action -- for example, by Status File. As a page selector, the same button is used to select either the next page or the previous page. Clicking on the right-hand triangle displays the next page, clicking on the left-hand triangle displays the previous page. Passwords --------- In earlier versions of Superbase, passwords were shown on screen in the Password dialog as you typed them in. Now, when you type in a password in order to gain access to a file, the password is not displayed in the Password dialog. RAM disk support (Amiga) ------------------------ Normally if you wish to use the Amiga RAM disk within a program, you need to copy data files into the RAM disk before running the program. With Superbase Professional, this step is unnecessary. You can load data into the RAM disk simply by using the drive identifier RAM: when you open a Superbase file. If the file is not already in the RAM disk, Superbase will copy it to the disk from the current directory, and open it at the same time. Superbase also provides a similar service when you close the file. It asks if you want the file to be copied back to the current directory, i.e. to the floppy or hard disk it was originally copied from. If you click on OK it does the job for you. Should you click on CANCEL the file will not be copied back and you will be asked if you want to erase the copy you have in the RAM disk. Selecting CANCEL will leave the RAM copy intact. (Note that if you open an existing file on RAM disk which has not been put there by Superbase then when you close that file Superbase will not ask you if you wish to copy it back.) As an example, suppose the Address file is stored on the disk in drive DF1: and the current directory is DF1:. To open this file from the RAM disk you would select the Open File option on the Project menu and then enter: RAM:Address You can now work on the file in the normal way -- retrieve data, edit data, remove records, and so on -- with the advantage that file operations using a RAM disk are much faster than they are on a physical disk. Remember to close the file at the end of a session so that any changes you have made can be saved permanently in the directory from which it was copied. This feature also works with other Ram disk facilities where the drive identifier is VD0: or VDK:. Reserved Words -------------- It should be emphasised that the reserved word list includes all Superbase functions even though they are not listed in Appendix C. You should bear this in mind when you are defining a file or writing a program: many of the DML functions have names which you might tempted to use as field or variable names, for example HRS, DAY, MONTH, YEAR. Note also the following keywords which should be added to the list in Appendix C. AS BUFFERS CALL COPY DISK DISKSPACE FIELDS PAGING RECORD TABLE Graphics and Form Printing -------------------------- On the Amiga, Printer on the Set menu has been extended to provide a graphics printing option for forms. Selecting this option displays a sub-menu with two items, Draft and Graphics. Draft works in the same way as is described in the User Guide, Volume 1. When Draft is set, output that appears on screen is sent to the printer as well; so if you select the next record in the current file, its field data will be output to the printer. If a form is in use all the text items will be printed as well as the field data. If you wish to print the field data only then you should set the Print Status of the text items to non-printing within the Forms Editor. The Graphics option can be used when you are displaying data with a form. It prints all the graphic elements in the form -- lines, boxes, areas, images, and so on -- together with all the data the form displays. Data items are printed in the system font, text items are printed in the fonts and point sizes which were set in the Forms Editor. However, Superbase will also take into account the Print Status of an item as set in the Forms Editor. On a 512K Amiga, you will only be able to print four colour forms in either draft or graphics modes. Using Superbase as a Relational System -------------------------------------- Although it is impossible to describe the principles of relational database design in this document, most users will benefit from an abbreviated account of some of the more important facts. When you are contemplating the basic design of a relational database application, there are three key considerations: 1. Establishing relationships between files. Every pair of files between which there is to be a relationship must have a field in each file that will be used as the link between them. These fields should be designated as key fields, preferably with unique indexes. 2. Ensuring integrity of relational link data. At the data entry stage, what goes into the link fields described above must be carefully controlled. The aim is to ensure that, for example, an account code (usually a text field) is always entered in the same case in all files in which it is used. This is done by setting the text field attribute for each of the link fields to perform automatic case conversion to either upper or lower case; obviously the conversion must be the same for both fields. Validation formulas may also be necessary to check the pattern of what is entered, its length, or the range of acceptable values. The second control on data integrity is via the LOOKUP function. This enables the system to check what is entered into the link field in one file against what already exists in the link field in the other file. For example, you would always want to check that a product was in stock before accepting an order for it. The LOOKUP function is attached to the link field in the file definition as a validation formula. However, if you are using links as described in the next paragraph, you must ensure that none of your LOOKUP functions tries to check for a record that you are still in the process of creating! 3. Including links in a multi-file form. If you want to design forms which can retrieve data from more than one file, you must use the Set Link menu option in the Forms Editor to create the links between the files. In Set Link, you specify the link fields described above as the master (or primary) and secondary link fields. At the other end of the application, you will want to retrieve data from your relational system in the most convenient way. There are a number of ways of doing this. 1. Browsing. Use a multi-file form with links set up as described above. The video buttons operate in the usual way, and you can switch files and indexes to vary the order of retrieval. The printer may be used to obtain draft or graphics hard copy. 2. Queries. The Process Query Edit menu option can be used to set up multi-file queries, which may be stored on disk. Output may include any fields from files between which a relation is specified in the Filter, and can be sorted as you wish. Report functions may be added, as well as limited formatting, but output is basically columnar and non-graphic. 3. Reports. Report programs may be generated automatically with the Forms Editor or written directly in the program editor (the Program Edit menu option). The forms editor allows for multi-line headings and footings, many levels of subtotalling and sorting, and full page field positioning, as well as offering many other features. Relations between files are derived from the links between files, so here again you must use the Set Link menu option before saving the report form. Output is non-graphic. Further Rules for Constructing Queries -------------------------------------- In multi-file queries, the user (especially the programmer) must exercise extra care to ensure that the query is efficient. The following rules should be observed: 1. Files are processed in the order in which they are mentioned in the WHERE statement (Query Filter Line). In some cases you will want to process the smallest file first, in some cases you won't. You may have to experiment to find the optimum query for a particular application. 2. Every file reference in the WHERE statement must be part of a chain. WHERE A=B AND B=C is acceptable, but WHERE A=B AND C=D is not. A simple rule of thumb is that there should always be one less join than the number of files in the query. Superbase Start Options ----------------------- When you run SBpro from the CLI, you can use the following optional parameters: SBpro [-c] [-r] [-fformname] [-p] [-s] [-z] -c Selects custom screen -r Disables returns (chr$(13)) when writing to disk files -f Loads form at startup time -p Removes remote control panel -s Removes scroll bars from Superbase window -z removes sizing gadget and disable sizing NOTE: Remember to set the stack to at least 8000 before running Superbase from the CLI. The start options are also available from the Workbench by setting Tool Types in the Info for the SBpro icon as follows: SCREEN=WORKBENCH Use Workbench SCREEN=CUSTOM Use custom screen RETURN=ON Enable returns RETURN=OFF Disable returns SYSTEM=NOPANEL|NOSCROLL|NOSIZE Remove panel scroll bars and size gadget You also may use any combination of the three options in the SYSTEM item. You can load a form at startup by creating an icon for it with the default tool set to SBpro and then double clicking on it. DATABASE MANAGEMENT LANGUAGE (DML) Changes to DML -------------- Programmers should be aware of the following changes, extensions and undocumented features in Superbase's Database Management Language: 1. The LOOKUP function is case sensitive -- for example, LOOKUP("fred",Firstname.Address) will not find an occurrence of "Fred". 2. The record SELECT commands (SELECT NEXT, SELECT LAST, SELECT KEY etc) have been amended so that they now take another optional parameter, FORM. The effect of this parameter is to activate any multi-file links that have been set in a form. If it is not used, the SELECT commands only operate on the current file and ignore any links between files. FORM must be placed immediately after SELECT. For example, with SELECT NEXT command, you would enter: SELECT FORM NEXT. 3. The KEY command now accepts a numeric key as well as a string key. 4. The commands EJECT, LOCATE, and FOOTING do not operate when the current output device is the screen and PAGING is OFF. You can, however, use LOCATE with paging off to set the column position -- but not the row position. 5. FORM. This command takes another optional parameter, SHOW, which is used to display external files. If the form contains external file fields, FORM SHOW is equivalent to clicking on the camera button. The syntax for FORM is: FORM [SHOW] [page [,row,column]] Note that the `page' parameter is now optional. When it is omitted and the FORM command is used without any following parameters, Superbase only displays the field data in a form and omits any other objects. 6. The command SET BUFFERS only sets the size of the disk buffers for the current session. If you want to set the number of buffers to be saved with the parameters file (SB.PAR), use the new command SAVE SET. 7. SET TABLE/RECORD/FORM can be followed by the ON parameter. This switches to the specified view mode even if there is a form in memory. Normally SET TABLE, for example, acts as a toggle between Table view and the form. If Table view is the current view mode, this command would switch in the form. SET TABLE ON, however, would leave the current view mode as it is. The advantage of this option is that it enables the programmer to select a view mode without having to keep track of what the current mode is. 8. During the execution of a program, BREAK OFF disables the Stop button and CONTROL-C; also, you cannot use the space bar to pause during program execution. When the program has finished, BREAK is turned on again. 9. Apart from the Stop and Pause button, the remote control panel is disabled while a program is running. Stop is only disabled if the BREAK OFF command has been issued, Pause is available at all times. 10. STORE takes another optional parameter, FORM. If FORM is used all the records in the current form are saved; otherwise STORE only saves the record which belongs to the current file. The new syntax for this command is: STORE [,0/1/2] [FORM]/[FILE sbfname] Note that STORE ,2 secures not only the current file but also any other open files. It is therefore not necessary to follow it with the FORM or FILE parameters and, in fact, adding one of these parameters to STORE ,2 will cause an error. 11. BLANK can also take FORM as an optional parameter. With a multi-file form, BLANK FORM creates blank records for each file represented in the form. When the FORM parameter is not included, BLANK only creates a blank record for the current file. The syntax for this command is: BLANK [FORM]/[FILE sbfname] 12. If SELECT DUPLICATE does not find a duplicate of the current index field, it leaves the current record unchanged. 13. A string function should not contain more than one intermediate string concatenation. For example, LEFT$("abcde",LEN("x" + "y")) contains only one intermediate concatenation and gives the correct result, but LEFT$("ab" + "cde",LEN("x" + "y")) gives the wrong result because it contains two concatenations -- "ab" + "cde" and LEN("x" + "y"). 14. Superbase already provides you with equivalents for the system commands (or disk utilities) Delete, Rename and Copy. If you want to use some of the other disk utilities without switching screens or exiting from Superbase, you can do so with the CALL command. On the Amiga, you enter: CALL "NEWCLI" This opens a CLI window on the Workbench screen, and you can then type in and execute any of the AmigaDOS commands. Alternatively, you can execute a DOS command directly without first typing it in the CLI window. Thus to list the directory for drive DF1: you would enter: CALL "DIR DF1:" The directory will then be output in either the CLI window used to run Superbase or the small Superbase window opened on the Workbench screen. You may want to use CALL to run a program which requires input from the keyboard. In this case, you should follow the program file name with the `<*' parameter, as in: CALL "fcopy <*" The effect of this parameter is to redirect input from `stdin', allowing you to type in data from the CLI window. 15. With commands that set a single file filter using WHERE (for example, IMPORT, EXPORT and SELECT WHERE) you can include ASK as the parameter to the WHERE statement. The effect of ASK is to display the standard filter dialog when the command is executed, thus allowing the user to set a filter while a program is running. If a filter is present when ASK is executed, the filter expressions will be presented as a default for the dialog. Examples are: SELECT WHERE ASK In this example, ASK causes the filter dialog to be presented. Entering the following filter line (at run-time): Lastname LIKE "[r-z]*" would have the same effect as executing the command: SELECT WHERE Lastname LIKE "[r-z]*" However, a partial filter like this is not allowed: SELECT WHERE datefield > ASK 16. ENTER. The parameters for this command can only be used with a form. With other view modes, ENTER (on its own) places the data entry cursor in the first open field that is not Read Only. 17. REQUEST. There is now an additional dialog type with the dialog number 19. This displays a list of the forms in the current directory. Note that the description in the DML User Guide for dialog type 5 is incorrect. This dialog provides a list of the fields in the current file. 18. SHOW. The command now takes an optional extension to allow or remove the menu bar from the EFMS screen: SHOW [MENU ON/OFF] [field]/[strexpr] 19. ? QUERY. The syntax for this command has been extended to make it easier to select a device for output. It is no longer necessary to select an output device before executing ? QUERY. Instead you should specify the device using the TO parameter. The new syntax is: ? QUERY [TO device] where `device' can be the printer, an ASCII file or a new `.sbf' file. If this parameter is not included output is to the screen. The device options are: TO PRINTER Outputs to the printer. TO FILE filename Creates an `.sbf' file on disk under the file name specified. TO filename Outputs to the ASCII file specified by `filename'. 19. IF THEN ELSE. The explanation of block IF THEN statements given in the manual needs to be supplemented by two further points: * Every block IF THEN statement must end with an END IF statement. (Example 4 on page 5-69, Volume 2, requires a second END IF after the last line.) * Within a block IF THEN statement, you can insert one or more ELSE IF statements. These are used to extend the number of alternatives provided by the original block IF THEN (so that it has the same effect as the CASE statement found in some versions of Basic); but they do not require additional END IF statements. The following examples should make these points clear: IF a% THEN ? atrue$ IF b% THEN ? btrue$ ELSE ? bfalse$ END IF ELSE ? afalse$ END IF IF a% =1 THEN ? "one" ELSE IF a% = 2 THEN ? "two" ELSE IF a% = 3 THEN ? "three" ELSE ? "Not 1 to 3" END IF New DML Commands ---------------- 1. SET PG Use this command to set the page size on your printer. It takes the syntax: [SET] PG rows [,columns [,ss]] where rows specifies the number of rows on the page and column specifies the number of columns (the page width). ss stands for single sheet and takes a value of 0 or 1. 0 specifies that single sheet paper is used, 0 specifies continuous stationery, also known as fan-fold paper. SET is optional. An example of the use of SET PG would be: SET PG 50, 65, 1 This line sets the format for a printed page to 50 lines of no more than 65 characters. It also tells Superbase that the printer takes continuous stationery. You can use this command in a program or from the command line to customize a page for any kind of printed output -- whether you are printing a text file, record data or a report. However setting up the printer in Preferences on the Amiga will override the PG settings. 2. SET PRINTER ON [,0/1] / OFF This is the program equivalent of selecting Print on the Set menu. The 0 parameter specifies draft mode printing, and the 1 parameter specifies graphics mode printing. Use this command in conjunction with FORM to obtain form printouts. After SET PRINTER ON has been executed, any output to the screen will also be sent to the printer, so remember to SET PRINTER OFF when the print operation is complete. 3. SAVE SET This command saves the Superbase parameter file. It is equivalent to clicking on OK in the Options dialog. All the parameters that have set in the dialog (and those that are set elsewhere) will be saved on disk in the file SB.par. 4. SET MIN This command sets the minimum size allowed for the Superbase window. It takes two optional parameters, the minimum width and height of the window in pixels, so the full syntax is: SET MIN [width [,height]] Maximum width is 640, maximum depth is 183. SET MIN without parameters allows the programmer to lock the window to its current size while a program is running. You cannot use SET MIN to set the window larger than its size when the command is executed. SET MIN can also be executed from the command line or a function key. User-Defined Menus ------------------ On Gem versions of Superbase, you can define up to six menus and each can have up to eight items (not including item 0 which specifies the menu title). The figures given in the User Guide -- 10 menus with 12 items -- only apply to the Amiga. A menu item can have a maximum of 23 characters. Other points to note in connection with the commands MENU and MENU ON are: * The first item defines the width of a menu in terms of the number of characters. When you define the text string parameter for item 1 you may need to allow for the width of other items in the menu by adding spaces to the end of the string. * If you wish to use checkmarks in your menus, remember to leave a space for the checkmark character when you define the MENU text string -- the first character in the string should be a space. * MENU on its own clears all the menu settings (as do ERASE and CLEAR). * After the MENU ON command has been executed, any menus that have been defined remain active until an item has been selected. The program example on page 5-91 of the DML User Guide is slightly misleading on this point since it suggests that the MENU ON command needs to be executed repeatedly. A better way of checking whether an item has been selected would be: MENU ON a%,b% menuloop: ON a% GOSUB sub1, sub2, sub3 GOTO menuloop Note, however, that the MENU ON command should be executed again after an item has been selected in order to reset the menu variables to 0. FORMS AND FORM HANDLING Changing Text Fonts ------------------- Contrary to what is stated in the Forms Editor User Guide, text fonts can be altered without deleting the text and typing it in again. In fact, the text font can be treated in the same way as the text style, as one of the text object's attributes. To change the font, first select the object by double clicking on it; then select the new font from the Fonts menu. Using External Files in a Form ------------------------------ You can use external file fields in a form in the same way as you do in a database file. The difference is that the external files are displayed in the field boxes rather than in a separate window. This means that multiple external files can be displayed at the same time. When you load the form into Superbase, the box for an external field will show the data in the current record; i.e., the name of an external file. To display the file in the box, click on the camera button. If a box which contains an image file extends beyond the screen, Superbase will move the form so that it shows as much of the image or text as possible. The result of entering a box -- by clicking on it, or by pressing Return after editing the previous field in the field order -- differs according to whether it contains a text file or an image file. If you enter a text box, Superbase replaces the box with the Text Editor window (which is given the same dimensions as the box). You can then edit the text in the normal way. Closing the Text Editor window or clicking in another part of the screen, restores the text to the field box, and moves on to the next field. With images, the effect of entering an external field box is to activate the image colour map: the image is displayed in its original colours. Otherwise, the image is displayed in the default colours of the form. These facilities for showing text and images in external field boxes only operate if the box is more than one character deep. For this reason, the first thing you should do after placing an external field on a form (in the Forms Editor) is resize it. Amiga Colour Palette -------------------- On the Amiga, the Colour Palette option (in the Attributes window) has been altered to reflect this computer's special colour features. The Palette now works in the same way as the Preferences colour changing option. It also offers several extra facilities not available in Preferences or in Gem versions of the Forms Editor. You may find it helpful to read the section on page 3-9 of the Forms Editor Guide as an introduction to the ideas underlying this feature, but for detailed instructions on how to use the palette you should consult the next section. Changing the Colour Palette (Amiga only) ---------------------------------------- When you click on the Colour Palette icon in the Attributes window, the Forms Editor will present you with the Colour Selection dialog (requester). At the top, there are three sliders for defining a colour in terms of its RGB (Red, Green and Blue) values and a box showing the currently selected colour. The set of colours available to you is shown below in the same format as in the Attributes window -- as two rows of eight colours. If you have previously set the resolution to 4 or 8 colours (see page. 2-11, Forms Editor User Guide), only the first 4 or 8 colours will be available and the rest of them will be ghosted. To change one of the colours in the Palette, first select it by clicking on its box in the colour panel. The Forms Editor will then do two things: it will display the colour in the box at the top left-hand corner of the Colour Selection, and it will reset the sliders to reflect the RGB values for the colour you have selected. You can now change the colour by moving the sliders in the same way as you would if you were using Preferences. When you have finished, click on OK to return to the current Forms Editor page. By using this technique, you could define all the colours in the Palette, setting the RGB values for each of them in turn. The Forms Editor also provides several other options which allow you to change a number of colours at a time. These options are indicated by the buttons below the colour panel. The Copy button enables you to copy a colour from one box to another. The procedure is as follows: first, click on the colour you wish to copy; second, click on the Copy button; third, click on the target colour to which the first colour is to be copied. With the Range button you specify the two colours at either end of the range and the Forms Editor then adjusts the colours in between. If you specified white and dark blue, for example, the colours in between would be changed to shades of blue from light to dark. To set a colour range, click on the colour which is to form the starting point (or the end point) for the range, click on the Range button, then click on the colour at the other end of the range. The Read Prefs button at the right of the colour panel changes the first four colour boxes to the colours that have been set in Preferences. The Reset button reverses the effects of any changes you may have made and restores the colours that were set before you selected the Colour Palette. Report Functions ------------------- Apart from COUNT the report functions -- SUM, MIN, MAX, MEAN, VAR and S.D. -- can only be used with numeric fields. These functions operate either on all the records in a group (if used in an AFTER GROUP section) or on all the records in the report (in an AFTER REPORT section). SUM Gives the field total for all the records in a group or report. MIN Returns the minimum value of the specified field among the records in a group or the report. COUNT Returns the number of records in a group or the report. Unlike the other functions, COUNT should not be followed by a field name -- it operates on the field specified in the REPORT or GROUP box. MAX Returns the maximum value of the specified field among the records in a group or for the report. MEAN Returns the average value of the field data in a group or in the whole report. VAR (Variance) Provides a measure of the spread of the data from its mean. S.D. (Standard Deviation) Provides a measure of the spread of the data from its mean -- for the field data in a group or over the entire report. Amiga Images ------------ When you add an image to the page, the Amiga version of the Forms Editor gives you a choice over the colours the image takes. A dialog appears with the message: Use image colour map? Clicking on OK selects the image colour map and resets the colour palette to those colours. If you click on Cancel, the image will be displayed using current range of colours as shown in the Attributes window. The form designer should ensure that only images compatible with the resolution and colour of the form are used. FORM CALCULATIONS Calculation Types ----------------- The calculations you add to a form have a number of features which are not covered in the Forms Editor guide (you should ignore the calculation examples given in the guide). In fact, with forms that are designed to be used in Superbase, calculations can play a far more important role than is suggested in the guide. This section describes these features in detail. Calculations take the same names as DML variables. If they are intended to store string data, the last character must be `$'; if they are used to store numeric data, the last character must be `%'. Calculations referred to by another calculation must already have been added to the form. When you create a calculation, you will be presented with a calculation dialog where you enter the formula for the calculation. Initially, it will show a list of the fields in the current file which have been added to the form. If you want to refer to other calculations in the formula, click on the multi- file gadget. The calculation names will then be listed as if they were fields belonging to a file called FORMCALCS. There are four different types of form calculation, and each has its own syntax. 1. Blank calculations. These are calculations which do not have a formula attached to them. They provide a means of interacting with a form from a program and can be treated as program variables. To define a calculation of this type, first enter its name and then, without entering a formula, click on OK in the formula dialog. Once the form has been loaded into Superbase, the calculation can be used as a link between the form and a program. This works both ways: you can assign a value to a calculation-variable either by typing it in the calculation's box on the form or by executing an assignment statement in a program. Suppose, for example, you defined a blank calculation with the name fred%. In the form's field order fred% is number 5. If you wanted to input data to a program from the form which contains fred%, you would include the program line: ENTER 5 When this line is executed, the input cursor will appear on screen in the box belonging to fred% and the value entered by the user will be assigned to fred%. If you now executed the Memory command, fred% and its contents will be listed along with all the other program variables. And any other DML commands which operate on variables, such as CLEAR, will also work with a calculation variable. To assign the value 22.5 to fred% from within the program, you would execute the statement: fred% = 22.5 This value will then be shown in the calculation box when the form display is next updated. Note that ENTER is the only input command that can be used with a form. Forms are object oriented rather than character oriented so you cannot use ASK or GET in combination with LOCATE. Another point to note is that you can only identify a calculation by its field order number; a command such as: ENTER fred% will not work. 2. Standard calculations. These function in the same way as the calculations which are attached to fields, and they take exactly the same syntax. In fact, they can be regarded as a special kind of field which only exists on a form. The calculation name should not occur in the calculation formula unless it is a self-referencing calculation. Instead the result is automatically assigned to the calculation when the formula is performed; that is, after each of these events: * Saving a record * Moving the cursor through the calculation box * Clicking in the calculation box * Retrieving another record, e.g. by clicking on the Next Record button or with the command SELECT NEXT * When you press Return after entering data in the box (only possible if the calculation is not Read Only) Typically, this type of calculation is used to derive a total from the contents of other calculations or fields and then to display it on the form. Another application would be to display a date by using the keyword TODAY in the formula. Examples are: Calculation name Calculation formula subtot1% Quantity * Price name$ Left$(Firstname,1) + ". " + Lastname When$ TODAY If you want to create a self-referencing calculation (see Self-referencing Formulas, Chapter 2, Volume 1), you need to add the calculation to the page and then edit it. 3. LET calculations. The formula for this type of calculation must start with an assignment statement with LET as the first word. It is used to assign a calculated value to a field or another calculation. The field may belong to the current file or another open file. An example would be: LET Amount.invoice = quantity * cost where this formula is attached to the calculation fred%. Quantity times Cost will be evaluated in the same circumstances as for type 2 calculations and the result will displayed on screen in the calculation box belonging to fred%. When a new record is retrieved, things happen slightly differently. The right-hand part of the formula is evaluated and displayed, but the value is not assigned to the field named to the left of the equals sign. As with the other types of calculation, the result determines whether the calculation should be defined as a string or numeric variable; i.e. for a string result, the calculation name must end with the `$' character; for a numeric result, the name must end with the `%' character. If you are using a LET calculation to send or `post' data to a record in another file, remember to save the record after completing a task. 4. Executable calculations. These calculations contain program statements which are executed when the user passes through the calculation box or clicks on it. A calculation of this type must begin with a DML command as opposed to the name of a variable, field or calculation. But, to distinguish it from other types of calculations, the command word should not be LET, AFTER or POST. Apart from this, executable commands follow the same rules as for command line statements. Almost any single or multi- statement DML program line can be entered as an executable calculation. This provides immense scope for creating powerful forms with their own built-in processing facility. At the simplest level, you could use an executable calculation to assign strings to function keys for use in data entry. For example: Key 1, "London": Key 2, "New York": Key 3, "Paris": Key 4, ... More complex applications include running a program, executing another calculation, etc. You can even use the calculation itself as a variable into which the user can enter a value. Thus the following command string could be assigned to the calculation f$: IF f$ = "A" THEN ..... ELSE ..... To force a calculation, make it Read Only and next in order. You can only make a calculation self-referencing by creating then editing it. Calculation Prefixes -------------------- In addition to the four calculation types, there are two prefixes, AFTER and POST, which can be placed in front of the formulas for types 2 and 3. AFTER The purpose of AFTER is to force a formula to be performed more frequently than it would be normally. It is followed by an optional field name and is separated from the rest of the formula by a colon. For example: AFTER : Quantity * Price Here AFTER forces the formula to be performed whenever any event occurs on the form -- if you click on or pass through a field, when you select a new record, and when the form is saved. In the example above, if the formula was attached to the calculation aa1%, the result would be displayed in aa1%'s box every time the user clicked the mouse in the box or in any other box on the form. If a field name is supplied, AFTER only takes effect after an event relating to the field specified. In the example below, we'll assume the formula is attached to the calculation Tot1%: AFTER Item: Subtot1% * 1.15 Tot1% will be assigned the result of the formula `Subtot1% * 1.15' after each of these events: * the cursor passes through the Item field * the user clicks in the Item box * the value of Item changes * all the usual events that cause evaluation of the formula POST Just as there are some calculation formulas which need to be performed repeatedly -- for these you will use AFTER -- so there are others which should only be performed once, when a record is saved. With POST, you can ensure that a result is only stored in a field (posted to another file) after the other calculations in the form have been carried out. POST takes the syntax: POST [LET xxx =] expression where xxx is a field name and the expression can be another field, a calculation, or a formula which calculates a result. The expression is not evaluated until the record is stored, unless the LET part of the formula is included. If this is the case, the expression is evaluated subject to the normal rules, but the assignment of LET is not made until the record is stored. Take, for example, the formula: POST LET Cust_Balance.Customers = Cust_Balance.Customers + Order_Amount.Orders Order_Amount could represent the final figure in a series of calculations which work out the final value of an order. Clearly, we do not want the total to be stored in the Customers file until all the calculations have been performed. By preceding the formula with POST we can ensure that this condition is satisfied. However, intermediate results can be shown on the form by clicking in the calculation box to which this formula is attached. MISCELLANEOUS * In some circumstances the Update option may skip some of the records in the file it is updating. The problem occurs when the update modifies a field which also forms part of the update filter; for example, it would occur if the update fields instruction was: Salary = Salary * 1.075 and the update filter was: Salary >= L8000 The solution is to enclose the filter statement in parentheses, as in: (Salary >= L8000) If you were running this example update under program control, you would place the entire WHERE statement within parentheses: (WHERE Salary >= L8000) This solution works by disabling the automatic optimization which causes the problem. * If you try to print a line longer than the line length set in Superbase's Printer options, Superbase will output a Carriage Return followed by a Line Feed when it reaches the maximum line length. * Note that Amiga owners can set a printer initialization sequence from within Superbase's Set Options dialog. This allows you to set the features and modes you prefer. The sequence of control codes is sent to the printer each time you perform a printing operation from within Superbase. If a sequence is present Superbase does not send the `Skip over perforation OFF' and `Set page length' codes that are normally sent when the printer is accessed. * The output format parameters BF, UL, IT enable you to set different print styles within a report or in the output from a query. If you wish to set other printing features, you can do so by using the CHR$ function to insert the appropriate printer control codes. With a report which has been generated in the Forms Editor, you will need to insert the control code sequences in the report program. In a query, you can simply enter them in the query fields line. For example, on some Epson printers, the sequence 27, 14 selects enlarged print, and the control code 20 turns it off. To output the data for one field in enlarged print, your query Fields line might look like this: Firstname, Lastname, CHR$(27);CHR$(14);City;CHR$(20), Street Note that some control codes may be inappropriately interpreted by the printer device, in which case you should set Print Raw on the Options requester to bypass the interpretation. * Path names. Superbase accepts path names up to 40 characters long (not including the file name). * File Names. Avoid using the characters : ? # / ; or the space character. When using a file name as an argument to a command such as OPEN FILE, it must always be inside quotation marks. However, a file name used to extend a field name, e.g. Name.Customers, need not be inside quotes, unless it includes non-alphanumeric characters, i.e. other than a-Z and 0-9. Superbase supplies quotes when necessary during dialog selections, but if you type a command line in directly be sure to observe this rule. * The Duplicate option on the Record menu re-initializes any constant formulas. This means you can duplicate a record which contains the constant formula SER without duplicating its serial number. Similarly, if a file uses the constant formula TODAY, creating a new record by duplicating an existing record does not reproduce the original date. * Superbase and the Forms Editor require a large amount of chip memory when printing a form in graphics mode. If your Amiga only has 512K RAM, the graphics print option may not be usable. * The DML LABELS examples on page 5-78 are wrong. Example 1 should not suggest that the command can extend over more than one line; example 2 requires the keyword FILE after LABELS. * The normal New Line sequence on the Amiga is a CHR$(10) (line feed). On most other machines and in Superbase, the default New Line sequence is CHR$(13) CHR$(10) (carriage return, line feed). The CHR$(13) may be suppressed, for example to achieve compatibility with the ED editor, by setting RETURN=OFF in the Tool Types list of the Superbase icon, which may be modified using the Workbench Info option. * GET and WAIT (1). Control characters can be returned in GET and WAIT statements. If these are saved in a file which is subsequently LISTed, they will generate the "File contains non-text characters" error. Note also that CONTROL-C, the standard interrupt key, does not stop program execution if returned in GET or WAIT. It is up to the program to detect the value and act appropriately. * GET and WAIT (2). WAIT makes the window active before it starts waiting for input, so if the window becomes inactive for any reason you cannot reactivate it. If this is a problem for your application, use GET instead; however, you will not be able to input into any other window, such as a CLI window, because GET continually reactivates the Superbase window. * The CLEAR statement must be placed last on a program line. * You may want to convert Superbase text files (`.sbt' files) to ASCII files. The technique for doing this is: 1. Rename the `.sbt' file, giving it a different extension. E.g., rename `Merge.sbt' as `Merge.asc'. 2. Load the file into the Text Editor. Its name will not appear in the list of files presented by the Project Open dialog and you will have to type it into the dialog box (remember to include the extension name). 3. Delete the lines containing information about ruler lengths. These will be blank lines except for two figures which give the start and end points for the ruler that is attached to the following paragraph. 4. Save the file using the same name with which it was loaded. FORTHCOMING FEATURES Future releases of Superbase Professional for the Amiga will include the following product enhancements: * Communications * Multi-line transaction handling on forms * Improved form printing * Data conversion utility * Additional programming commands * Further performance optimization Certain features of Superbase Professional referred to in the manual have not been included in Version 2.00. These relate to the multi-line transaction handling capability, which requires formula syntax allowing SUM, for example, to be used in form calculations. The EDIT REPLICATE menu option in the Forms Editor will not be enabled until the full transaction handling version of Superbase is released.