"SBase Personal - 3. DEFINING NEW FILES" 3 DEFINING NEW FILES Files and Forms Entering the New File Name Creating an Empty Copy Passwords The File Definition Requester Creating a New Field Selecting the Field Type Text/Logical Field Numeric Field Rounding Money Format Selecting Numeric Format Type Selecting Numeric Format Features Overflow Internal Storage Date Fields Changing the System Date Day Month Year Order Month Style Year Style Separators Dates As Numbers Time Fields Changing the System Time External File Fields Adding Fields Other Data Attributes Required Read Only Fields Virtual Fields Multiple Response Fields Defining a Multiple Response Field Saving the File Definition Files and Forms Forms are the most powerful layer offered by the SBase 4. A form may combine fields from several different files, and may be used both for browsing through and for entering data into multiple files. Forms depend on files, and file definitions must be created within SBase 4 before you can develop forms to use them with the Form Painter. Many database applications are more efficient if they are designed from the start with multiple files in mind. Chapter 16, Multi-File Applications, explains some of the concepts that underpin the creation of such systems. Time spent analyzing your requirements prior to defining files will be more than repaid by not having to spend time redefining them later. This chapter will help you set up your application whether it is a single file address list or a complex multi-file order processing system. If you are embarking on the development of a multi-file system, you should consult the chapters starting with Chapter 33 for information on how to use the Form Designer for a better understanding of this higher-level tool before proceeding to set up file definitions. Here's a preview of the steps required to set up a new file: 1. Select New File from the Project menu then type in a new file name, adding passwords if desired. 2. Define the characteristics of each field and add it to the File Definition. 3. Specify which fields are to be indexed. 4. Start entering record data. Entering the New File Name From the Project menu select New and with the mouse button still held down select File. SBase 4 presents you with a requester for the name of the file you are about to define. Users of AmigaDOS 1.3 will be presented with a file requester like the one shown below. *** See Fig 3-1 *** If you are running SBase under AmigaDOS 2.0, a more extensive file requester is available as shown below: *** See Fig 3-2 *** Type the name of the new file. Don't type SBF or any other extension. It must not contain any spaces. Any characters may be used except the following: \ = / : ; Once you click OK, SBase 4 will display the Password requester. If you do not wish to set any passwords against this file, click OK. SBase 4 will then display the File Definition Requester which is explained below. Creating an Empty Copy Some applications require the creation of new empty files with definitions that are identical to existing ones. (See Chapter 15 Reorganizing the Database). To do this: 1. From the Project menu select Open and File, then select the file you wish to copy. 2. From the Project menu select New and Empty Copy. 3. Specify the name of the new file and any passwords you want it to have. SBase creates a file which is exactly the same as the current file but without any data in it. Passwords SBase 4 provides up to three levels of password protection. The first level, Delete, gives full access to the file, including the ability to delete data. The second level allows users Read and Write access only. This means that they may update records, but may not delete the file or records in the file from within the SBase 4 system. The third password allows Read Only access - users may not update records or delete them. In a fully developed database, forms may be created to show only selected fields, thus protecting data from viewing as well as from unauthorized updating. Suppose you enter the three following passwords in the requester boxes: John Paul George When you open your file and type in the password John, you will have full privileges for the file. If you enter Paul, you get read and write privileges, but you may not delete data. If you enter George you have read privilege only: no data entry or editing, and you may not delete the file or any of the records in it. When a password exists, SBase 4 will ask for it each time you open the file. As a precaution against the password being seen by someone else, SBase 4 does not display the password on screen when you type it in. Caution Once a password has been set, it may only be modified with the DML PASSWORD command. The file must be open. The File Definition Requester Once you have entered the filename and optional passwords, the File definition requester appears. This is used to specify the details of each field in the file. *** See Fig 3-3 *** The panel at the top of the requester has three parts or zones, indicated by the headings Field Name, Attributes and Format. When you're looking at an existing file definition, the list of fields for the new file appears in the left-hand zone, the abbreviated forms of the field types and attributes (TXT - text; NUM - numeric; CLC - calculated; and so on) in the center zone, and the field formats (length, date or time style, or number style) in the right-hand zone. SBase 4 categorizes fields into three basic types - Text/Logical, Numeric, and Date/Time. Each of these is represented by a radio button below the panel on the left. For each basic type there are a number of format options. The requesters for specifying or amending formats are invoked by clicking the relevant radio button. The check boxes to the right of the radio buttons refer to attributes. The requesters for specifying or amending attributes may be invoked by clicking the relevant check box. Attributes may apply singly or in combination to any of the field types, subject to certain rules. For example, the External attribute always refers to a Text field, Virtual fields are always Read Only, and so on. Detailed descriptions of the formats and attributes are given below. At the foot of the File definition requester are the Add, Delete, OK, Clear, and Cancel buttons. When you've defined the details of a new field, you click on the Add button, and SBase adds the field name together with its attributes and format to the upper panel of the File Definition Requester. You must also click on the OK button to Save the file definition. You must click on the OK button anytime you want to save any changes to the file definition. Delete is used to remove a field from a database by tagging it as Deleted. To change the name of a field after it has been added to the file definition, the procedure is: 1. Click on the name in the File Definition panel. SBase 4 then places the name in the Field box. 2. Click in the Field box, delete or edit the existing name, and type in a new name. 3. Click the OK button. If you click Cancel, SBase 4 first asks if you want to proceed with this action and then exits from the requester to the main display. Clear empties the Field box and resets the field type selection buttons. OK takes you on to the next step, which for a new file is specifying the index fields. To summarize, the procedure for defining new fields is as follows: 1. Type in a field name. 2. Select the appropriate field type button. 3. Enter details about the field - its length or numeric format, for example. 4. Use the Add button to add the field to the file definition. Creating a New Field The first step in creating a new field is to type in the field name. Field names are important. They are used in views and as the default headings for query output. They may be used as variables in DML programs in SBase Professional 4. Ideally they should be short but descriptive. There is no reason why you should not use the same name, for example Desc, to refer to fields in different files. When defining formulas and command lines, SBase 4 automatically appends the file name to the field name, creating a unique field name such as Desc.INVENTRY. Now, click in the Field box below the Date/Time field type button. Type in the field name. You may use up to 15 characters, including spaces. Use only alphanumeric characters, the space, and the underline character. The field name may not begin with a number or contain more than one consecutive space. You can use any words except those reserved for the SBase Professional 4 programming language. A list of these is given in Appendix D. Selecting the Field Type Click the button next to the type of field you want. Choose from Text/Logical, Numeric, Date/Time. If you wish to create an External File field, click the Text/Logical button and the External check box. You can add attributes such as Validation or Read Only later. Your choice will display another requester for entry of details about the field. (Each of the field types has its own requester. These are described in the sections following this one). When you have specified the details, you click OK in the field type requester to return to the File Definition requester. Text/Logical Field Use this type for any field that is not designed to hold a date, a time, or a number. The Logical field type is a sub-type of the Text field type. Logical fields contain a limited range of values, making it easier to predict their contents when specifying search criteria. Defining a text field involves specifying its length, its attributes, and whether it is to be a multiple response field or not: 1. Click the Text button. SBase 4 displays the Text requester. *** See Fig 3-4 *** 2. Set the length of the text field, up to a maximum of 4000 characters, by entering it in the box at the top of the requester. There are two ways of doing this. - From the keyboard. Click in the box to activate the insertion point, then enter the number of characters from the keyboard. - Using the mouse. Set the length using the right and left arrows at either side of the box. The right arrow increases the value in the length box, the left arrow decreases it. The value in the counter changes each time you click one of the arrows. 3. Set the text attributes by clicking one of the five first option buttons. These options determine how text is stored in a field. If you leave the default, Standard, selected, the text will be stored and displayed in the same case in which it is entered (standard text format). 4. Click the Logical field button if you wish to define a logical field. The field will have a fixed length of one character, and you will only be able to enter `Y', `N', `T', or `F' into it. Logical fields are distinguished from others in the File Definition panel by the LOG type abbreviation. 5. If you want the field to include Returns, so that you can format it with paragraphs and blank lines, turn on the Allow Returns check box. If this attribute is included, the File Definition Panel shows the letter R next to the field length. 6. The box labeled Responses is used to define a text field as a multiple response field. This attribute, which is unique to text fields, is explained in the last section in this chapter. Normally the box contains the number `1' which indicates that the text field has has only one response. Unless you want the text field to be multiple response, you can leave this box as it is. 7. When you have set the text format, click OK to accept the entry. Numeric Field Use a numeric field for all numbers, including decimal numbers, currency amounts, integers, percentages or numbers expressed in exponential format. SBase 4 stores numbers with a maximum precision of 14 digits, which you may arrange in any format around the decimal point. When you define a numeric field you control two quite separate aspects of the numbers that will be stored in the field: - the way the numbers are stored (real, long or integer ), and - the presentation of the numbers when they are displayed or printed (controlled by all the other options in the requester). 1. Click the Numeric button to display the numeric field requester. *** See Fig 3-5 *** 2. When the requester first appears, the number format is set to -99999.00. This allows up to five digits to the left of the decimal point, and two decimal places. There is also a space for a minus sign for negative numbers. To change the numeric format length, click the arrows at either side of the number format panel. The left-hand arrows increase and decrease the number of digits to the left of the decimal point, the right-hand arrows increase and decrease the number of decimal places. It is not possible to edit the number format directly. 3. Select the numeric format type and other numeric features, as described in the following sections. 4. Click OK. Rounding The numeric format only determines how numeric field data is displayed; it does not affect the way numbers are stored (SBase 4 stores real numbers to 14 figure accuracy). For example, if you set the format to three decimal places and type in the number to five decimal places such as 5.66666, it will be displayed as: 5.667 Normally SBase would number 5.66666 as 5.66666. If you wanted to store it as 5.667 this can be done with the FIX function in the DML Language. This is only available in the SBase Professional 4 product. The STR$ and VAL DML functions may also be used to round numbers. Money Format The exception to the above is the numeric format with two decimal places. SBase 4 treats numbers using this format as money amounts. Such numbers are both displayed and stored to 2 decimal place accuracy; so if you entered 5.66666, it would be stored as 5.67. Selecting Numeric Format Type You can choose from four possible types of basic numeric format; the selection is always reflected in the number format panel: 9.0 This format is preselected when the requester appears. The format shows actual numbers left of the decimal point, and trailing zeros to fill up the decimal places. For a format of 999.000: 26.200 334.456 9.9 This format shows numbers only, with leading or trailing spaces: 26.2 334.456 *.0 Inserts leading asterisks in place of leading zeros; adds trailing zeros to the right. For a format of *****.00: ***26.20 **334.46 0.0 Inserts both leading and trailing zeros. For a format of 0000.000: 0026.200 0334.456 Selecting Numeric Format Features You may choose from three number sign styles: -99 This button is preselected. It causes SBase 4 to print a minus sign if the number is negative, a space if the number is positive. Shown as - in the number format requester box. +/-99 Prints a minus sign if the number is negative, a plus sign if the number is positive. Shown as + in the number format requester box. 99 This means that SBase 4 does not leave a space for the sign in the format. It does, however, insert a minus sign when the number is actually negative. If you enter negative numbers into a field which has no provision for a sign, they will not be properly displayed in column output, nor when the field is edited. There are three currency sign options: 99 No currency sign. $99 Places the currency sign immediately to the left of the first digit in the format: $26.20 $334.46 Shown as $9 in the number format requester box. $ 99 The currency sign is left-aligned - it appears to the left of the format, always in the same place. For a format of $ 9999.00: $ 26.20 $ 334.46 Plus and minus signs are also aligned when this style is selected. You may choose your preferred currency symbol by typing it in the Currency box in the Set System Options requester. This also allows you to set a currency suffix for the currency sign style, as in: 26.20$ or: 26.20 This is a global setting and may not be varied from field to field. As an alternative to a currency symbol, the percent symbol may be selected. All numbers using this format will appear with a trailing percent symbol. 26.20% The check boxes at the bottom of the requester provide four other options affecting numeric formats: (-) Prints parentheses instead of the minus sign. Can be selected in conjunction with one of the above. Shown as `(' in the number format requester box. (0) Zero suppression. This makes SBase 4 print blank spaces instead of zeros when the number to be printed equals zero. Shown as z in the number format requester box. (,) Thousands separator. Numbers greater than 999 are printed with a separator, e.g. 1,100. Normally the separator is a comma and the decimal point is a period or full stop, but alternatives may be selected from the Set Options requester. Shown as a comma in the number format requester box. E Exponential format. All numbers whose format includes an `e' will be expressed with an exponential signifier at the end. This allows you to enter numbers much larger than the normal 14 digit precision permits. Shown as `e' at the right of the number format requester box. Overflow If the number entered into a numeric field during data entry is too big for the format, SBase 4 displays number signs to indicate an overflow condition: ########.## You can easily edit the numeric format to make it longer, or, if you prefer, include an exponential signifier in the format. Internal Storage You may choose between real, long or integer. Your choice is determined by the type of numeric values to be stored in the field, and by considerations of amount of storage occupied and speed of operation. `Real' numeric fields can hold any numeric value, but occupy most storage space and are slowest in operation. `Long' numeric fields can hold only integers (from -2,147,483,648 to +2,147,483,647) and are moderate in space requirement and speed. `Integer' numeric fields can hold only integers (from -32,768 to +32,767), occupy the minimum space and are quickest in operation. Typically, therefore, you would use a `real' numeric field for amounts of currency, for percentages or for the results of an arithmetic calculation. You would use a `long' numeric field for counters that may take large values, such as numbers of votes cast, documents issued, items sold and so on. You should use `integer' numeric fields whenever possible, especially for fields you intend to use as indexes to database files. The space required by index files, and the time taken by SBase to read them, is then minimized. Date Fields Date fields can accept any date (years 0001 to 9999). There are many possible styles of date expression. *** See Fig 3-6 *** When the date field requester appears, the date is in the default date style. You can reset it by altering the style with the Set Date Format menu command. Changing the System Date The box in the middle of the requester shows the current SBase 4 system date. To change the date, click the left arrow to increase it, or the right arrow to decrease it. SBase 4 remembers the new date whether you set it from here or with the Set Date command. Caution Other programs will also use this date as their system date. You can also edit the date directly by clicking in the box. The system date always reflects the current date style as determined by your selections. Day Month Year Order Click one of the buttons in the upper part of the requester to set the order in which the day, month and year are presented. Month Style You can choose one of three possible month styles: MM Number from 1 to 12. MMM Three letter abbreviation: Jan. MMMM Full name of month: January. Year Style You can choose one of two possible year styles: YY Last two digits of the year: 91. This always implies the current century. YYYY The full year: 1991. Separators There are five kinds of separator to choose from. / 1/10/91 - 1-10-91 space 1 10 91 . 1.10.91 , First separator is space, second is a comma followed by a space: 1 October, 1991 or Oct 1, 1991. Dates As Numbers Although dates are expressed as text, they are stored internally as numbers. This means you can include them in arithmetic expressions, and incorporate them in formulas and validation checks. You can also construct an index on a date field knowing that it will be kept in true date order. Time Fields *** See Fig 3-7 *** Time fields offer a variety of formats for storing the time of day, or for storing any elapsed time period up to 24 hours. You can choose between a 12 or 24 hour format and there are three ways of specifying the time units: hours and minutes hours, minutes and seconds hours, minutes, seconds and thousandths of a second You set the time format by clicking on the Time button at the bottom of the Time/Date requester. The selection buttons will then present the various time format options: seconds, milliseconds, and am/pm. The default format shows the time in hours and minutes on a 24 hour clock. Click the selection buttons to set this to another format. Like dates, time fields are expressed as text, but are stored internally as numbers. Changing the System Time The box in the middle of the requester shows the system time in the currently selected time format. As with the system date, you can change the system time by clicking on the arrows at the side of the box, or by editing the box directly. External File Fields An external file field is a special type of text field, intended to contain a file name. To create an external file field, click the Text button and define the field in the same way as you would define a normal text field. Then click the External check box. The file names in external file fields are used in SBase 4's External File System, which allows you to find and display graphic images or text files. It provides both a kind of built-in `picture library' facility, and a `memo fields' facility. See Chapter 27 Integrating Image and Text Files. Adding Fields After setting the details for a field type, the next step is to add it to the list of fields in the Field Names box. Click the Add button in the bottom left-hand corner of the requester. SBase then: 1. Copies the name from the Field box into the File Definition Panel, adding it to the existing list of names. 2. Shows abbreviations to indicate the field type selections and the field attributes (both are shown under the heading Attributes). These appear across four columns, like this: TXT VAL REQ IXD *** See Fig 3-8 *** - - The abbreviations for the field types and attributes are as follows: First column TXT Text LOG Logical NMI Numeric - Integer NML Numeric - Long NUM Numeric - Real DAT Date TIM Time EXT External DEL Deleted Second column CLC Calculated CLV Calculated and Validated CON Constant COV Constant and Validated VAL Validated Third column RDO Read Only REQ Required RDQ Required and Read Only Fourth column IXD Duplicates Index IXU Unique Index Note Indexes are explained in Chapter 3, Creating Indexes. To show that a field is virtual, SBase 4 takes the first two letters of its type abbreviation, and precedes it with the letter V. For example: VTX (for virtual text fields) VNU (for virtual numeric fields) VDA (for virtual date fields) 3. Shows the format of the field. For a text field this is the length of the field in characters. It may also include the two optional text parameters: case parameters (Upper Case, Lower Case, Capitalize Field, Capitalize Words) represented as U, L, C and W; and the number of multiple responses selected (if more than one) represented as M followed by the number. The character L indicates that the length is greater than 255 characters. For a numeric field the format is specified by the digit pattern plus certain characters to show which style features have been included. For a date field the format is a representation of the day, month and year style you selected, and for a time field the format is a representation of the selected time style. Validation and Calculation formulas are not shown. You may, however, view or print them with the Utilities Status File command. 4. Finally, SBase 4 clears the Field box and the type selection buttons. You can either define another field or exit from the definition requester. Other Data Attributes Each field in a SBase 4 file is one of the six types described above: text, logical, numeric, date, time or external. When you look at the File Definition requester, you will see that there are six more field selection options: Required, Read Only, Validation, Calculation, Constant, and Virtual. Required You may designate any field as a required field. This means that during data entry you must enter a value - you cannot leave a required field blank. Required fields are checked when you exit from them (and also when the record is saved). If you press the ENTER key in a required field without entering any data, SBase 4 displays a requester with the message `This field must have some data.' Read Only Fields The Read Only attribute is used to protect the data in the field from being deleted or overwritten. Data cannot be entered in a Read Only field. Although any field may be designated as Read Only, normally you would only use this attribute for fields which have Constant or Calculation formulas attached to them. If you designate any other kind of field as Read Only, it will not be possible to enter data in the field, and, as a result, it will remain blank. However you may want to protect data after it has been entered in a field. In this case, you would change the field's attribute to Read Only by modifying the file definition. Virtual Fields Virtual fields provide a way of saving disk space. Any field which is defined as virtual must have a Constant or Calculation formula attached to it. When you save a record, SBase 4 calculates the value of the virtual field, and if it is a key field, creates an index pointer for the record on the basis of its derived value. However, the value is not stored with the record. Instead, SBase 4 uses just one byte (one character) per record to signify a virtual field. The value is then recalculated whenever it is required: - When you display the record. - When you edit the record. - When a LOOKUP formula, which specifies the virtual field, is performed in another file. When you are browsing through a file, SBase 4 will recalculate virtual fields as it retrieves them. With complex calculation formulas, this may have a slight effect on the program's performance. Multiple Response Fields One way of explaining about fields is to say that each field holds the answer to a specific question about the data in a record - such as, what is this client's telephone number? Or, how much does this product cost? As the name suggests, multiple response fields are used when there may be more than one answer to the question. Suppose you wanted to index the books in a library. For each book you would create a separate record. Along with the title and other details, the record would contain a field showing the author of the book. Some books, though, have more than one author, so you could make this field a multiple response field. Defining a text field as a multiple response field enables you to store a number of related data items under one field name. When you display a record with a multiple response field, only one of the field's items is shown on screen; but you can display each item in turn using the key combination CTRL+N. To move back to a previous item, use CTRL+P. For example, if the second is currently displayed, CTRL+N displays the third item; CTRL+P will then return you to the second item. There are some constraints on the use of multiple response fields. References to the individual elements of a multiple response field must be by subscript - entering the number in parentheses after the fieldname - and the reference must always be typed in. Typical examples are when setting up a filter or in a calculation formula linking one field to another. The number for the first item is zero, so to refer to the third item in a multiple field, you would enter: Fieldname(2) Some multiple response field applications, such as labels, are best served by being fully programmed in DML. Note also that multiple response fields are not supported in Virtual Field Indexes, or in Transaction Lines defined on a Form. Defining a Multiple Response Field Multiple response fields may have up to ten elements; that is, you can use a multiple response field to store up to ten separate lines of text. The total number of characters in all the elements must not be more than 4000. To define a text field as a multiple response field: 1. Decide how many elements you need. 2. Enter the number in the box labeled Responses in the text field requester. Initially, the number will be set to one. Delete this using the backspace key and type in the new number. Saving the File Definition When you have defined all the fields you require, click the OK button in the File Definition requester. SBase 4 will then present you with the New Index requester. This stage in the process of defining a new file is described in Chapter 3 Creating Indexes. Once you have specified the fields that are to be indexed, SBase 4 automatically completes the file definition process and displays a blank record for the new file. You can now start entering data. See Chapter 5 Entering Data for more information. If you wish to print out your file definition, set up your printer, select the Print command on the Project menu, and then choose the Status File option. See Chapter 29 Housekeeping, for more information.