"SBase Personal - 17. MULTI-FILE APPLICATIONS" 17 MULTI-FILE APPLICATIONS Files for Multi-File Applications Tombstone Data Transactional Data Table Data Avoiding Repeating Fields in a File Definition Data Integrity Defining Common Reference Fields Common Reference Field Validation Common Reference Field Cross-File Lookup Forms for Multi-File Applications Summary Data Entry in a Multi-File Application Preparing for Data Entry Using Lookups Entering Data into Multiple Files Transaction Blocks Cross-File References for Non-Form Data Entry Using Virtual Fields Defining and Using Multi-File Queries Defining a Multi-File Query Fields Line ON File Entering ON File Defining a Multi-File Query Report Line Defining a Multi-File Query Filter Optimizing Queries Optimization with Operators Using OR in the Query Filter Line Sequencing Precision Defining a Multi-File Query Order Line Defining and Using Multi-File Updates Setting the Update Filter Setting the Update Definition Optimizing an Update 17 MULTI-FILE APPLICATIONS SBase 4 includes a number of features which may be combined to create practical multi-file applications. Files may be linked for data entry and in forms created with the Form Designer. Relational queries and updates provide ways of joining files for processing operations. In this chapter we discuss the principles which you should follow and the program features you should be aware of when you are considering whether your own application needs to be multi-file. If you are going to set up a multi-file application, the descriptions of file types, the principles of linking files, and the rules for defining queries should enable you to make a start on analyzing your requirements. Be prepared to spend some time working with prototype files before committing yourself to the real thing. A multi-file application is one in which the user's requirements for data entry, retrieval, and reporting cannot be satisfied by a single file structure. The real-world model for a single file or flat-file database application is the card index box, in which each card represents a record, and each record is a collection of unique fields. It is relatively easy to set up such a system, and reporting on a single file is largely a matter of selecting fields and records for output. A file of book references, for example, can be listed and grouped in many different ways - by author, title, publisher, and year of publication. Provided you are satisfied with the results, a single file is appropriate. However, a simple decision to store more details about an author than just his or her name changes the nature of the application. You would not want to waste disk space and retrieval time by storing the author's place of birth with every book. Instead you would keep all the details about an author in one place, and all the details about books in another, and link the two files together: a multi-file system. Immediately, you must start to explore questions such as, how should the two files be linked? Should the links be permanent or temporary? How can I be sure that the data is properly cross-referenced? SBase 4 provides ways of answering all these questions. Forms, cross-file lookup, relational queries, and global multi-file updating commands can be combined to produce effective and flexible applications of many kinds. Files for Multi-File Applications The relationships between the files in an application and the structure of the files themselves are determined by the nature of the data. Files themselves can be usefully categorized into three types: those containing static or `tombstone' data, such as author's date of birth; dynamic or transactional data, such as a publication by an author; and tables of data such as a list of topics or keywords for use in categorizing other data. All these files are set up in the same way as normal SBase 4 files. The division into three types is purely logical. There are also three important types of relationship: one to one, one to many, and many to many. A look at the file types will help us to clarify the meaning of one to many and one to one relationships. Tombstone Data (SBase Professional Only) This type of file is used for storing details about a person or thing (including legal entities such as companies). Names and addresses, customer account details, product details, details of services offered, personnel details - these are typical examples of tombstone data found in the commercial area. Comparable examples can be found in education and other domains, ranging from student or patient records to laboratory experiments and libraries. Tombstone data does not change frequently, and only one instance of it needs to be stored. In multi-file applications, these files serve as `master' files, with a unique reference field (see Defining Common Reference Fields, below), usually a code of some kind, which is used to link each record with associated transaction records in other files in one to many relationships. During data entry of transaction records, details from the tombstone record may be looked up to ensure that the same data is entered correctly into every associated transaction record. One important type of field often found in tombstone records is the balance field. This is not necessarily a money amount: inventory counts are also balances. The important point is that the balance is held in the master record, and not in the transaction record, although data from the latter is often used to update the former. Transactional Data (SBase Professional Only) This type of file provides the other end of the one to many relationship. Here the records consist of `events,' although thinking of them as such may sometimes seem rather unfamiliar. Orders, stock movements, client billings, publications, exam results, book lendings, and laboratory instrument readings are examples of transactional data which is inherently linked to a single tombstone record. Each such record will include a common reference field which links it to its unique master record. Several transaction records will contain the same reference, so that, for example, all an author's books can be linked to the same author record. Table Data (Both SBase Personal 4 and SBase Professional 4) There is some overlap between the concepts of the tombstone data and table data, in that table data is also static and only one instance of it is stored. A file of country names should not need to change, and each country only occurs once. Table files provide a means of ensuring that data to be used for subsequent searching is always entered in a standard form. Although tables can be quite large, they often contain fairly small lists of codes or words which occur repeatedly in other records. Typical tables are lists of countries or states, standard codes used for categorizing businesses or individuals or products, and lists of key words used for searching transactional files such as bibliographies. The relationship between a table record and a record in a tombstone or a transaction file is one to one. There should be only one occurrence of the table data per tombstone or transaction record; if there is more than one, you are breaking an important rule which forbids the repetition of fields in a record structure (see next section). Tables are heavily used in data entry. For example, when the user wants to enter a country name into an address record, the system can display a list of all the allowable countries. The user selects the required country name, which is copied in automatically, thus ensuring that it will be spelled the same way in every record in which it occurs. (You can also arrange for the country name not to be copied, thus reducing storage requirements.) Avoiding Repeating Fields in a File Definition This section illustrates the rule that a record should not contain repeating fields. One of the most common errors when setting up databases is the repetition of identical field structures in a file definition. In a database of authors and books, it would be tempting to define a single file with a record for each author, in which you store details of the author such as his or her name, plus fields to hold the details of, say, 10 or 20 books. The book fields, you might think, could be given different names, such as Title1, Title2, etc. It seems so simple, yet it would greatly reduce the usefulness of your system. If your application requires a one to many relationship like this, and as we saw in the previous sections, very many applications do, then you must set up two files, one for the `ones' and one for the `many': Authors and Books. By defining a common reference field for the two files, such as Author_code, you can achieve a supple and dynamic application which will ensure accurate data entry and efficient data retrieval. In short, never define groups of repeating fields in a file definition if you wish to make the best use of SBase 4's relational features, which are designed to help you achieve the best possible data management system. Data Integrity A library system will be useless if you cannot be certain that the author code is correctly entered into every publication record. Therefore, the integrity of the data to be used for linking files must be assured. This is achieved by predefining a capability for linking through common reference fields, and by ensuring that reference field data is validated when it is entered, both in its own terms and by automatically checking it against existing data. Defining Common Reference Fields Every pair of files between which there is to be a relationship must have a reference field in common. This is a field in each file that holds the same data. The common reference field is used as the link between the pair of files during data entry and when relational queries and updates are defined. These fields should be designated as indexed fields. At least one of the indexes should only allow unique keys. Examples of common reference fields are account codes, part numbers, identity numbers, etc. Common reference fields are usually text fields, and are kept as short as possible to speed up index updating and sorting during queries. Pairs of common reference fields do not have to have the same name in both files. Common Reference Field Validation At the data entry stage, what goes into the common reference fields must be carefully controlled. The aim is to ensure that, for example, an account code is always entered in the same case in all files in which it is used. This is done in three ways: 1. By setting the text field attribute for each of the common reference fields to perform the same type of automatic case conversion, to either upper or lower case. 2. By where possible using a calculation to generate the contents of the common reference field automatically. For example, the serial number function SER can be used to create a unique number for a new record, which can be combined with part of a text field such as a person's name. 3. By attaching a validation formula to check the pattern of what is entered, its length, or the range of acceptable values. This may only be necessary in certain applications. Common Reference Field Cross-File Lookup A further control on data integrity is via the LOOKUP function. This enables the system to check what is entered into the common reference field in one file against what already exists in the common reference field in the other file, which may be either a tombstone or a table 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 common reference field in the file definition as a validation formula. It uses the data you enter as an index key for the common reference field in the other file, and attempts to read a record from that file. For example, LOOKUP (stockcode.ORDERS,stockcode.STOCK) would be the validation formula for the Stockcode.Orders field. When you enter data into this field, SBase uses it as an index key for the Stockcode.Stock field, and attempts to read the corresponding record from the Stock file. If the record is not found, the validation fails. If the record is found, any fields which are cross-file references, in which data in the record being entered is copied from the looked up file, are completed automatically. The LOOKUP function can be extended in two ways. You can add a user defined help message, such as `This Stock Code Is Not On File.' Or, more usefully, you can add a REQUEST 20 command to the validation formula. This command specifies a requester which displays selected data from the file being looked up, in our example the Stock file. The user can then select the correct record from a list, thus ensuring that data is never invalid. See Chapter 18 Linking Files for full details of LOOKUP and REQUEST 20. Forms for Multi-File Applications The underlying structure of a multi-file application resides in the file definitions you set up using the concepts and techniques described above. Forms provide many ways of extending and refining an application. You can have many different forms for an application. In our Authors and Books example, you could have one form for entering details of authors and another for entering details of books. A third form could show a list of books for an author, and yet another could show just a table of books. One form could allow editing, another could be restricted to reading only. Yet all these forms would use the same file structures, so when you added new data or edited existing data the same LOOKUP functions would ensure the integrity of your data whichever form you were using. If you plan to set up a multi-file application, first define your file structures, then use the Form Designer to build the forms you need as you need them. The Form Designer Pro has many features to appeal to the more advanced user, such as 'one-to-many' transaction handling, specialized calculation functions, and the ability to include DML command lines that can interface to subroutines in a resident DML program. However, the average user will be able to create an attractive data entry or browsing form in minutes using the Form Designer of SBase Personal 4 otr the Form Designer Pro of SBase Professional 4. Summary Here is a summary of points to bear in mind when creating a multi-file database system. - Analyze your requirements to see whether you need a flat file or a multi-file system. If your data breaks down into tombstone and transactional data, you probably have a multi-file application. - Decide whether you should keep any frequently occurring data in tables. - Set up file definitions for all the files to be linked. - Do not repeat fields within a file definition. - Define common reference fields for each pair of files to be linked for data entry, Update, or Query. - Use validation formulas to control the range and pattern of data entered into common reference fields. - Set the same Text Field case conversion attributes for each pair of common reference fields. - Use LOOKUP to cross-check data entered into one file against data already present in the other. - Use REQUEST 20 to ensure that data entered into one file is copied from data already entered in the other. - Define fields as virtual if you want to avoid repeating tombstone or table data in transaction records. - Keep record and index key sizes as small as possible. - Use as few indexes as possible. - Create forms after having created and tested file definitions. Data Entry in a Multi-File Application Data entry is critically important to a successful multi-file application. The integrity of the data must be maintained so that you can be confident that subsequent searching and reporting produce accurate results. Preparing for Data Entry The order in which you prepare your files is important. All table file data must be entered first, so that other files can refer to it. If you are using a table of country names and while entering names and addresses you need to enter a country which is not in the table, you must stop and make an entry into the table before proceeding. Likewise, all tombstone or master file data must be entered before you start entering transactional data in SBase Professional 4. Using Lookups When you enter data into a field which is linked to another file with LOOKUP, SBase uses the characters you type as an index key for the other file. As explained above, there are a number of possible outcomes for the lookup operation. If you typed in an exact key, SBase 4 finds the correct record in the other file. Any cross-file references in the record you are entering will be completed and the fields `filled in' for you. If the key you typed is not found, the validation fails; if you have specified an error message it is shown. You may click either Cancel or OK. OK puts the insertion point back in the field to try again. Cancel terminates data entry. If you have included a REQUEST 20 extension to the LOOKUP validation formula, SBase 4 shows a list of data from the other file. The starting point of the list depends on what you typed. For example, typing `s' will display records from the nearest key value to `s' onwards. This technique allows you to position quickly to a record close to the one you want, then visually confirm the exact record required. Pressing ENTER in a LOOKUP field without having typed a space or any other character passes through the field (unless it is a Required field, in which you must enter data). However, you will not be able to save the record without having performed a successful lookup. Entering Data into Multiple Files Forms allow you to enter data into several files from a single screen. Following a Record New command SBase 4 prepares to create a new record for each file associated with the form. It will only actually create a record in a file if you enter data into one or more fields for that file. So if you do not want to create a record in a certain file - for example, you might not want to allow anyone to add a new author on the same form you use to update the file of books - just designate all the fields from that file Read Only when you create the form. This allows you to have any number of fields shown on a form for display or reference purposes, without having to be concerned about the security of the data. This is also true when you edit existing data. If none of the fields for a file are changed, SBase will not update that file. The Form Designer also allows you to specify the order in which data is entered into fields. Transaction Blocks (SBase Professional 4 Only) One of the most powerful features offered by the Form Designer is the ability to define a group of fields as `transactions.' Using this feature, you can create a form that manages a one-to-many relationship without any need for programming. Each transaction consists of one or more fields defined as a group. The group is replicated in a special way that allows SBase to create multiple records in the file at the `many' end of the one-to- many relationship when you save the form. Special functions may be added to the form to display running totals, post data from one file to another, etc. You enter data into the fields in a transaction block in the same way as for ordinary data entry. Each transaction can use the LOOKUP and REQUEST 20 function to cross-check data, and may itself include references to fields in more than one file - although only one one-to-many relationship is allowed per form without extra programming. Cross-File References for Non-Form Data Entry If you are not using a multi-file form for data entry, you may need to set up cross-file reference fields to copy data from looked up files. For example, when entering the details of a publication you would look up the author's record by means of an author code, and you would have set up the file to copy the author's name into the record you entering. The field Name.Book would be copied from Name.Author. Name.Book would in fact be a calculation formula of this type: LOOKUP(Author_code.BOOK,Code.AUTHOR) ? Name.AUTHOR : "" The way you position cross-file reference fields in Page View can make a difference to the way data entry works. It is usually preferable that cross-file references are completed immediately after the lookup is performed. Since cross-file reference fields will always be Read Only, the insertion point passes over them into the next editable field. The recommended method is to place all cross-file reference fields that are completed following entry into a LOOKUP field immediately after the LOOKUP field, in sequence from left to right and top to bottom. This will ensure a satisfactory display. Using Virtual Fields Virtual fields can have a useful role in a multi-file application. If your customers come from a group of twelve countries, you might set up a table file Country to hold the country names, and copy the appropriate name into each customer record when you enter it. Country.Customer will therefore be a cross-file LOOKUP field. By making Country.Customer also a virtual field, you will not have to store the country name in the customer file, thus saving on disk space. However, because the contents of Country.Customer must be looked up from the Country file every time a record is read from the Customer file, there is an inevitable effect on performance. The effect will be more or less noticeable depending on the speed of your computer. If the table file is small, SBase 4 may be able to hold all the data in its own buffers and minimize the overhead. You will have to decide for yourself whether you want to trade disk economy for speed, or not. Defining and Using Multi-File Queries You should have read Chapter 14 Defining and Using Queries before attempting to define a multi-file query. SBase 4 provides excellent facilities for linking files together in queries. You can specify fields for output from many different files, and set links between many pairs of files, subject only to the 512 character limit on the Query Fields and Filter command lines. In practice, you are unlikely to need to set links between more than four or five files in a single query. This is a very powerful facility, and the user has to accept a certain responsibility for learning to understand the rules that SBase 4's query logic obeys. The definition of efficient queries is therefore a skill to be learned, and a period of experimentation may be necessary before you are fully satisfied with the results. Defining a Multi-File Query Fields Line To output fields from more than one file, you follow the same procedure as for single-file queries: 1. Open the files whose fields you wish to output. 2. Select the Query command on the Process menu. 3. Click the Fields button in the Query Definition requester. 4. Initially, the List box shows the fields in the current file. Click the file selector button (the right arrow) to the right of the file name to select another file. - You can cycle through all the open files by repeated clicking on the file selector button. 5. Before selecting a field, specify any output formatting features you may require by clicking on their respective buttons; for example: @ Position, & Length, As Heading. See Chapter 14 Defining and Using Queries for more information on these features. 6. Select the fields you want in the order you want them. To enter a field name in the Query Fields text box, double-click it or click once and press ENTER. 7. When you have completed the Query Fields command line, click OK to return to the main Query Definition requester. ON File Outputting data from more than one file raises a special problem of formatting. The ON File option is designed to solve it. ON File causes specified field data to be displayed only when record data changes. Certain filter command lines (see Optimizing Queries, below) may cause SBase to retrieve records in an order which effectively changes the record data on each iteration. This may result in the unexpected display of duplicated field data which ON File normally suppresses. The best way to explain how ON File works is with an example. For this purpose, we will use one of the demonstration Query files, Clibals1. Load Clibals1 using the Query Open option. Clibals1 automatically opens the two database files, Clients and Trans. Both files hold data relating to the same set of individuals. The Trans file contains details of the stocks transactions made by the individuals in the Clients file, using one record for each transactions. Note that the relationship between the two files is `one to many': for each record in the Clients file, there may be several records in the Trans file. Clibals1 outputs data from both files, using the Customer_ref field as a link. This link is set up in the Query filter, which is explained later. The Fields line in the Clibals1 Query file specifies which fields are to be shown in the report: &20@2Client.CLIENTS AS "Client"@24Trans_Date.TRANS AS " Date",Transaction ref.TRANS AS "Ref",USD Value.TRANS AS "$ Value" When you click on OK in the Query requester, the screen output should look like this: *** See Fig 17-1 *** Too much data is repeated. What is needed is a way of telling SBase 4 which details to print for each file. ON File does exactly that. The demonstration Query file Clibals2 works with the same database files as Clibals1, but uses ON File to format its output. The only difference between these two Queries is in the Fields line: ON "CLIENTS"&20@2Client.CLIENTS AS "Client" ON "TRANS"@22Trans_Date.TRANS AS " Date",Transaction ref.TRANS AS "Ref",USD Value.TRANS AS " $ Value" If you now load Clibals2 and click on OK, the screen output will be like this: *** See Fig 17-2 *** As you see, ON File ensures that the clients' names and addresses are not repeated. It instructs SBase not to output fields from a file unless the current record has changed. You can think of ON as being short for `on change of record.' In this example, SBase 4 reads a record from the Clients file and then reads the records in the Trans file. Every time it finds a Trans record whose Customer_ref field matches the Customer_ref field in the Clients file, it outputs the data specified by the Fields line. Without the ON "Clients" command, it repeats the name and street from the Clients file for matching records in the Trans file. The effect of ON "Clients" is to suppress duplicate names and addresses. The second instance of ON File - ON "Trans" - has a different effect. It cancels the first ON command and instructs SBase to output data for all the matching records in the Trans file. If you left out ON "Trans" from the Fields line, only the first matching record for each client would be printed. SBase assumes that the ON command applies to all the field names to the right of it, irrespective of which file they belong to. This is why we need to include ON "Trans" in the Fields Line; it ensures that the first ON command only applies to the fields in the Clients file. If we were not concerned about the order in which fields are output, we could dispense with the second ON command, as in the following Field line: Description.Trans, Total.Trans, Trans_date.Trans ON "Clients" Firstname.Clients, Lastname.Clients Entering ON File To include this in the Fields command line, click on ON File in the Fields requester. SBase 4 will then copy ON into the Main Box together with the name of the file that is in the File Name Box. Note that ON File only works when the data for each `level' of the Query output is held in separate files. Defining a Multi-File Query Report Line The same considerations apply to multi-file as to single-file reporting. See Chapter 15 Reporting for more information. 1. Select Process Query. 2. Click the Report button on the Query Definition requester. 3. Click the file selector button at the right of the file name to select the file from which you wish to choose fields. 4. Enter the report commands by clicking on one of the keyword buttons to the right of the fields list. 5. If appropriate, select a field. 6. Repeat steps 4 to 6 until you have defined all the reporting features you require. 7. Click OK to return to the main Query Definition requester. Defining a Multi-File Query Filter A query is multi-file when fields from more than one file are named in the Query Fields or Filter command line. For a multi-file query to work, you must specify a link between the files. This is done by means of common reference fields (see above). Each file in a pair of linked files includes a reference field in which the data is the same as in the other file, such as an account code. This link is known as a join. In SBase 4 , a join is defined in the Query Filter line as two fields joined by an EQUALS operator: Customer_ref.Clients = Customer_ref.Trans This is referred to in this section as a join expression. - You can have joins between more than one pair of files. - A file or field may appear more than once in the series of joins. SBase 4 allows you to create a Query Filter in almost any form. However, query filter definition requires careful attention as certain rules must be observed. 1. Only the = operator can be used in a join expression. 2. Both fields must be indexed. 3. If you have more than one join in a filter, the join expressions must be related with the AND keyword: Customer_ref.Clients = Customer_Ref.Trans AND Stock_key.Trans = Reference.Stock 4. Do not use an OR keyword to relate join expressions. 5. Place all join expressions at the start of the line. 6. Every file reference in the line must be part of a chain. A line of the form A=B AND B=C is acceptable, but A=B AND C=D is not. A simple rule of thumb is that there should always be one less join than the numberof files in the query. Note These rules must be followed. If they are not followed, you could end up with queries that produce repetitive and meaningless results, and take a very long time to do so. - If you want to interrupt a query, click the Stop button or press CTRL+C. Optimizing Queries To some extent, the user is responsible for defining the best possible query filter for the application. SBase 4 will follow its own internal logic to obtain optimum performance. However, if you have a complex application involving many files, you should be prepared to try out various forms of filter line and see which is the most efficient. Since queries can be stored on disk, this does not involve repeated redefinition of every element of the query. One area in which experimentation is often necessary is the order in which files are referred to in the Query Filter line. Files are processed in the order in which they are referred to. In a typical one-to-many application it is usually more efficient to process the file containing the `ones' first, so you should place its common reference field on the left-hand side of the first join expression: Customer_ref.Clients = Customer_Ref.Trans This forces SBase to read the first record from the Clients file, then find all the matching records from the Trans file, then get the next Clients record, and so on. Provided there are more transactions than clients and you want to process all the clients anyway, this should be the most efficient filter - the one that requires the fewest disk accesses. However, in some cases the proportions of the data may be the other way round: you are restricting the range of clients, or there are few transactions to process; or both conditions may apply. In this case it may be more efficient to process the Trans file first. Optimization with Operators Not all of the operators available for use in filter command lines allow SBase 4 to optimize its own performance. Basically, SBase 4 needs you to define either an equality or a range for an indexed field. This technique allows SBase 4 to read only the relevant section of the file in question. The range may be defined as within a file or as either end of a file: >> "Bill" AND << "Fred" >>= "Bill" AND <<= "Fred" << "Fred" >>= "Fred" <<= "Fred" >> "Fred" Expressions involving the LIKE operator cannot be used in optimization, but do not actually stop it. However, any occurrence of OR, NOT or parentheses prevents SBase 4 from optimizing the remaining expressions in the line. Note that all SBase 4 functions use parentheses. Using OR in the Query Filter Line The OR keyword prevents further optimization of the query. It must not be used to relate join expressions, and it should not be placed before any part of a filter command line which SBase could optimize, such as the range expressions above. If you want to use an OR expression later in the line, enclose it within parentheses: ( City.Clients = "London" OR City.Clients = "New York" ) Sequencing Precision A further optimization consideration is the correct sequencing of expressions in the filter command line. The rule states that you should sequence all filter expressions in an order from most precise to least precise. For example: Trading.Clients = Trading.Trans AND Country.Clients = "USA" AND Trading_balance.Clients >> 1000 AND ( Lastname.Clients LIKE "smith" OR Lastname.Clients LIKE "jones") The join comes first, followed by a equality match on `USA' (case sensitive), which as a single value is precise. Then comes a GREATER THAN expression - less precise, as many values can be greater than 1000. OR expressions, whatever the degree of precision involved, always come last, inside parentheses. Defining a Multi-File Query Order Line The Query Order line for a multi-file query needs no special treatment. It is of course necessary to ensure that the order line includes all fields on which the query output is grouped, as specified by the GROUP keyword in the Query Report line. More specifically, the Query Order line must correspond to the group structure which you establish in the Query Report line. If you have defined two levels of grouping, the Order line should sort the query output on the basis of the two group fields. For example, suppose your Report line contained the commands: GROUP Country.Clients, GROUP Trans_type.Trans For this report to work correctly, the Order line must be: Country.Clients, Trans_type.Trans The procedure for defining a Query Order line is as follows: 1. Click the Order button in the Query Definition requester. 2. Click the file selector button next to the file name to select the file from which you wish to choose fields. 3. Select the field on wish you wish to sort. 4. Click either the Ascending or Descending button. 5. Repeat steps 2 to 4 for all the files on which you wish to sort. 6. Click OK to return to the main Query Definition requester. Defining and Using Multi-File Updates Multi-file updates allow you to update one file on the basis of the contents of another. As such, Update is a powerful tool for making global changes to file data in a single operation. We will use a specific example to illustrate the procedure for defining a multi-file update. It involves two files called Employee and Payscale. The first contains details of all the employees in a company, including an employee's name, salary and grade within the company. Salaries are calculated on the basis of an employee's grade, and the Payscale file stores the salary figures for all the different grades. Each record in the file hold just two items of information: a grade and the yearly salary for that grade. There are two circumstances which require large-scale modifications to the data in the Employee file. The company conducts a progress review every six months - and upgrades some employees on the basis of their performance - and, once a year, it also revises the pay scales in line with the rate of inflation. In a manual system, each of these events would entail a large number of changes to records in both files. With this example of a simple personnel application, you would still need to edit individual records in the both files - to reflect changes in employees' grades and changes to payscales - but salary updates can be carried out automatically using a multi-file update. The next two sections use this example to describe the exact procedure for defining the multi-file update. See also Chapter 18 Updating. Setting the Update Filter If you are updating one file on the basis of the contents of another file, you must specify a link between the files using the Update Filter. The first step is to select Update from the Process menu. SBase 4 displays the Update Filter requester with a list of the fields from the current file. To select another open file, click on the file selector button next to the file name. In the example application given above, you would now enter the following command line in the Update Filter's Text box: Grade.Employee = Grade.Payscale This ensures that the Employee file will only be updated with values from the Payscale file when the Grade field in a record in the Employee file is EQUAL TO the Grade field in the Customer file. In another application, you would probably include other conditions in the Filter command line to limit the updating by date or some other factor. Setting the Update Definition Once you click OK in the Update Filter requester, SBase 4 displays the Update Definition requester. Here you specify the updating action required. In the example, we want the Salary field in the Employee file to be updated by the Salary field in the Payscale file; so the command line should be: Salary.Employee = Salary.Payscale After specifying the Update Definition command line, click OK to initiate the update processing. If we continue with the example, we can describe the precise steps SBase takes when performing a multi-file update. Starting with the first record (according to the current index) in the Employee file, SBase 4: 1. Selects the record in the Payscale file which has the same grade; that is, the value in the Grade field is the same in both files. 2. Copies the value from the Salary field in the Payscale file to the corresponding field in the Employee file. 3. Saves the updated Employee record to disk. 4. Selects the next record in the Employee file and repeats these steps until all the records in the file have been updated. Optimizing an Update If the first field that occurs in the Update Filter line is an indexed field, SBase 4 will make use of the index when selecting records. With a large file, this may speed up the operation considerably. The index must be the current index and should be selected (using Index Open or the `-' key) before you select the Update command. However, you should not attempt to optimize an update in this way if the indexed field also occurs in the Update Definition command line. See Chapter 19 Updating for more details.