@database @node main Chapter18 "SBase Personal - 18. LINKING FILES" @{" 18 LINKING FILES " link 0} @{" Cross-file Validation " link 1} @{" Text Field Attributes " link 2} @{" LOOKUP Requesters " link 3} Only in SBase Professional 4 @{" REQUEST 20 " link 4} Only in SBase Professional 4 @{" Cross-file Calculation " link 5} @{" Cross-File Calculations and Forms " link 6} @endnode @node 0 18 LINKING FILES SBase 4 files can be linked in four situations. - Queries and Updates may include relational links, which are used to join files together for the duration of the processing only. The link information may be stored in the query or update file. See Chapter 17 Multi-File Applications. - In Forms, files may be linked to provide relational browsing capability. This allows SBase to retrieve sets of related records from multiple files as you step through one of them. The link information is stored in the Form file. See Chapter 10 Retrieving Data Interactively and Chapter 38 Linking Files in Forms. - In data entry and editing, the LOOKUP function may be used to cross-check data as it is entered into one file against data in another. - (Only in SBase Professional 4) A REQUEST command can invoke a LOOKUP requester showing records from another file, from which you can pick the required data. The link information is stored in the File Definition. The previous chapter discussed the general principles involved in setting up multiple file applications. Here we describe the two SBase 4 commands - LOOKUP and REQUEST - which are used to establish links between files within a file definition. You may use LOOKUP in two ways: - For cross-file validation, where it checks whether the contents of a field in one file match the contents of a field in another file. - Within a calculation formula, to extract data from another file. REQUEST 20 can be combined with LOOKUP in a validation formula to generate a LOOKUP requester. @endnode @node 1 Cross-file Validation The syntax for LOOKUP is: LOOKUP(field1.File1,field2.File2) field1 represents the name of the field to which the validation formula is attached, i.e., the current field. field2 represents the name of the field in another file. For example, you could use LOOKUP to validate the Country field in the Clients file by checking it against the Name field in the Country file. The formula to use would be: LOOKUP(Country.Clients,Name.Country) When you enter data in the Country.Clients field, LOOKUP will check the records in the Country file to see whether the name of the country exists. If it finds a record where Name.Country matches the name you have entered it accepts the entry. If the name does not exists, it rejects your entry and displays a validation error message. There are a number of rules governing the way LOOKUP is entered in a validation formula: - The second field name must be the name of an indexed field in another open file. LOOKUP cannot be used for single file validation. - LOOKUP will not work 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 use a string function to force them to give an empty string instead of a null value. For example: LTRIM$(DATE$(date field)) - will give an empty string when `date field' has a null value. - LOOKUP is case sensitive, so there must be an exact match between the data in both fields; i.e., if the data in the first field begins with a capital letter, the second field must also be capitalized. See the section below. @endnode @node 2 Text Field Attributes If you are validating data entry to a text field by checking it against data in another file (using the LOOKUP function), it is important that the data in both fields should be in the same case. The best of way of ensuring an exact match between data in both fields is to set the same text attributes in their respective file definitions. The text field attributes are set in the Text Format requester. In most cases, you will specify one of the text field attributes when you first define the file. But you can also do this at a later stage using the Project Modify File command. The five relevant text field attributes are represented by the first five buttons below the field length counter: Standard, Upper Case, Lower Case, Capitalize Field, Capitalize Word. Click the button for the attribute required. Standard is the default attribute and leaves text as it is. If you select this option, text is stored and displayed in the case in which it is entered. The other options convert text to a specified format. - Upper Case converts all the letters in a text entry to capital letters. - Lower Case puts all the letters in lower case. - Capitalize Field makes the first letter in the first word a capital letter. - Capitalize Words capitalizes the first letter of each word in the field. @endnode @node 3 LOOKUP Requesters (Only in SBase Professional 4) LOOKUP requesters are an extension of Validation help messages (see Chapter 7 Data Entry Validation). There are some circumstances in which a single line message is not in fact very helpful. An example would be the following validation formula which checks the data you enter against the data in another file: LOOKUP(Stockcode.Invline,Stockcode.Invntory) ELSE "Stock code not found in Inventory file" When you enter the code for a stock item into the Invline file, the formula uses the LOOKUP function to check whether the code exists in the Invntory file. The trouble is that if users make a mistake, it is probably because they are not sure what the code is for a particular stock item. Ideally, they should be shown a list of all the stock items in the file together with their codes. This is just what LOOKUP requesters do. They display a list of all the acceptable data inputs, and allow the user to select an item by clicking on it (without having to type it in). To display a help requester, you need to use the REQUEST command in your validation formula. For the example above, the formula would be: LOOKUP(Stockcode.Invline,Stockcode.Invntory) ELSE REQUEST "Stock code not found","Select another stock code", 20,, Stockcode.Invline,40,Stockcode.Invntory, Description.Invntory Provided the first field is an indexed field, typing the first letter of the stock code would bring up all the stock codes beginning with that letter. Alternatively, you can use the scroll bars to scroll down the list. Double clicking on any item in the list automatically places the item into the field specified (in this example, the Stockcode.Invline field). When the user enters the wrong stock code, the following requester will appear: *** @{" See Fig 18-1 " system "ps sb2:Fig18-1"} @endnode @node 4 REQUEST 20 (Only in SBase Professional 4) When you create a validation help requester, it is important that you enter the correct parameters for the REQUEST 20 command. The command itself is an example of the way in which the SBase 4 Database Management Language can be accessed from a higher level of the SBase interface. The first eight parameters are compulsory and must be separated from each other by commas. You may also add two optional parameters at the end of the REQUEST command, and these must also be separated by commas. 1. For the first two parameters following REQUEST, enter the message that is to appear at the top of the validation help requester. If you wish, you can leave these parameters blank by entering two empty strings; i.e., the command would start with: REQUEST "","", ... 2. The next parameter, 20, does not make any difference to the way the LOOKUP requester functions, but it must be entered in the formula. Note also that 20 must be followed by two commas. 3. Now enter the name of the field to which the validation formula is attached. When you select an item using a REQUEST requester, the item will be entered into this field. 4. The next parameter (40 in the example in the previous section) specifies the width of the requester in terms of the number of characters. 5. Enter the name of the field which will appear on the left of the requester box list. This field (Stockcode.Invntory in the example) is the one which will be selected for data entry. 6. As an option, you may also specify up to two more fields, which will be displayed in the requester to the right of the field specified in the previous step. These allow you to provide the user with additional information about the data. Note The REQUEST command has several other forms. Some of these are also permitted in a validation formula. SBase Professional 4 users should see the DML Reference Guide for full details of the syntax of REQUEST. @endnode @node 5 Cross-file Calculation In cross-file validation, LOOKUP checks the data you enter at the keyboard against the data in another file. In cross-file calculation, it automatically extracts data from another file. Note You need to be clear about the the idea of the current record. If a cross-file validation is successful - if LOOKUP finds a matching field in another file - the record containing the field becomes the current record, even though the file may not be the current file and is not displayed on screen. Each open file has a current record. The current record for the current file is simply the one that is displayed on the screen. Although the other files are behind the scenes, you can access their fields in the same way as with the current file. When you refer to a field in a file other than the current file, SBase 4 looks for data in the current record for that file. These features provide a way of inserting the data from another file in a new record, using LOOKUP in a ternary calculation formula (see Using the Ternary Operator, Chapter 8 Derived Values). We can illustrate this with an example involving two files, Orders and Invntory. Each record in the Invntory file contains details of a particular product, such as its stock code, its price and a description of the product. Orders contains details of a customer's order; these include a stock code for each product item ordered and the quantity of items ordered. Note that the stock code field is common to both files and provides the relational link between them. When a new record is created in the Orders file (or when an existing record is edited), the stock code and quantity will have to be typed in. But by using LOOKUP in combination with the ternary operator, we can ensure that the price (the unit cost) of a product and its description are automatically read into the new record from the Invntory file. The calculation formula to attach to the Unit_Cost.Orders field would be: LOOKUP(Stockcode.Orders, Stockcode.Invntory) ? Unit_Cost.Invntory: 0 The effect of this formula is to force a LOOKUP on the Invntory file whenever new data is entered (or existing data is edited) in the Unit_Cost.Orders field. First it checks to see whether the stock code in the current record in the Orders file exists in the Invntory file. If it does exist, it makes the matching record in the Invntory file current. The condition expressed by the ternary operator has been satisfied, so the first value after the question mark - i.e. the contents of Unit_Cost.Invntory - is assigned to Unit_Cost.Orders. If the condition is not satisfied, the second value - in this case, a value of zero - is assigned. With a string field, the second value must be a text string, so the calculation formula for the Description.Orders field would be: LOOKUP(Stockcode.Orders, Stockcode.Invntory) ? Description.Invntory: "" Here, the empty string is assigned to Description.Orders if the stock code doesn't exist in the Invntory file; in other words, the field remains blank. It is up to you to decide what action is to be taken if the LOOKUP is unsuccessful. With string fields, you may want to assign a text string indicating that an error has occurred, as in: LOOKUP(Stockcode.Orders, Stockcode.Invntory) ? Description.Invntory: "Error" Now if there is no match between the stock codes in the Orders and Invntory files, `Error' will be assigned to the Description.Orders field. Normally, you would also use LOOKUP in a validation formula attached to the Stockcode.Orders field, so the situation would never arise: the stock code in the Orders file will always be matched with a stock code in the Invntory file and, as a result, the condition in the calculation formula will always be satisfied. @endnode @node 6 Cross-File Calculations and Forms If a form refers to more than one file, you have to define a link structure in the Form Designer that will allow SBase to retrieve related records correctly. Since the separate files are linked at the form level, some cross-file calculations may become redundant. There is no need to use a LOOKUP in a ternary calculation formula to force specific records to be read; the link structure does this automatically. Using forms allows you to simplify your file definitions. You do not need to duplicate information from one file into another. In the example in the previous section, a formula is used to copy the Unit_cost.Invntory field into the Unit_Cost.Orders field. But in a form, although you must include a LOOKUP to access the Invntory file, fields from that file may be displayed and referred to independently. @endnode