" SBase Personal - 8 DERIVED VALUES" 8 DERIVED VALUES Field Calculation Formulas Constructing a Calculation Formula Using Dates and Times in Formulas Using the System Variables When is a Calculation Performed? Linked Calculations Calculation Count Self-referencing Formulas Checklist Constants Generating Serial Numbers Using the Ternary Operator Extended Ternary Operations Self-referencing Ternary Operations Further Information 8 DERIVED VALUES In addition to the data you enter directly into records, you can use calculation formulas to derive new values from existing data. This chapter describes the use of calculations to generate values which are then stored in fields. Derived values can also play a useful role in queries and updates. See Chapter 14 Defining and Using Queries and Chapter 19 Updating. In SBase Professional 4 calculation formulas may also appear on forms, providing additional processing power beyond the file level. Form calculations can generate running totals, transfer data from one file to another in a multi-file form, or serve as programmable variables. Form Designer Pro can create forms that may also include command lines, providing an interface to DML programs. This is discussed further in Chapter 39 Form Calculation Formulas and 41 Commands and Controls in Forms. Field Calculation Formulas A calculation formula works out and stores a result that depends on other fields and values. Unlike a validation formula, which checks data entered by the user, a calculation formula generates the data itself. This means that you cannot edit a calculated field directly. To be more precise, if the field is not Read Only, you can attempt to edit its contents, but the formula will then overwrite your entry with the calculated result. Calculation formulas can be attached to any type of field. Calculated fields may also be validated, so that the result is first calculated and then checked. Here are some examples of typical calculation formulas: quantity.Invline * price.Stocks subtotal_1.Orders + subtotal_2.Orders + subtotal_3.Orders amount.Stocks * ( 1 + increase.Stocks / 100 ) Formulas are usually arithmetical and attached to numeric fields, and as such rarely make use of relational operators like `greater than' or `less than'. There are restrictions on the combinations of types of field in a formula expression. SBase will let you know if you break a rule, by trying to multiply two text fields, for example. Remember, though, that you can treat date and time fields as numbers. You can create formulas for text fields by typing directly in the Formula box: LEFT$(Lastname.Clients,5) + "/" + STR$ (SER("Clients"),"00000") Here the formula makes use of the SBase functions, LEFT$, STR$ and SER (see Chapter 9 Use of Functions). It could produce a result like `johns/00042', which could in turn be the basis of an index. Date and time formulas are also valid. Typically, they would be used with the reserved words TODAY and NOW, to show the date and time when a record was created or last edited. All the examples given here relate to single file applications. Chapter 17 Linking Files explains how formulas can also be used to extract data from other files for insertion in the current file. Constructing a Calculation Formula You do this in the same way as you create a validation formula (see Chapter 7 Data Entry Validation), with one important difference: you do not need to include the name of the field you are working with in the formula itself. If you wanted to create a formula which added two fields together and placed the result in a field called total, you would enter: amount1.Orders + amount2.Orders Unless you are using the ternary operator (explained in a later section in this chapter), a calculation formula should not contain the equals sign; so it would be a mistake to enter: total.Orders = amount1.Orders + amount2.Orders When you create a formula, the field you are attaching it to is shown at the top of the calculation requesters; and the result of the formula is automatically assigned to that field. This is why it is not necessary to include the field name in the formula itself. It is possible, however, to construct a formula which includes the name of the field it is attached to. We refer to this as a self-referencing formula. For reasons which are explained further on in this chapter, you should exercise caution before creating formulas of this type. Using Dates and Times in Formulas SBase stores dates by representing them as julian date numbers; a date's julian date number is the number of days between the date and the year A.D. 0. For example, the date, 26 July 1986, has the julian date number 725202; and the same date a year later has the value 725202 + 365. This method of storing dates makes it easy to incorporate date fields in formulas with numeric values. For example, to generate the number of days between two dates, or to set a date in the future: date1.Orders - date2.Orders date.Orders + 90 Time fields can also be treated as numeric fields. SBase stores times as thousandths of a second within a 24 hour period. Using the System Variables `System variables' is the term we use to describe the SBase keywords, TODAY and NOW. Your computer system keeps track of the date and time; these words provide a means of displaying the date and time, and inserting them in a field. If your system has a battery-backed real-time clock, TODAY and NOW will show the current date and time; that is, the correct date and the correct time. Otherwise you can set the date and time using the Date Format command on the Set menu, at the start of any session with SBase. For the rest of the session, the system will then update these variables constantly, so that they show the current date and time. By using TODAY and NOW in calculation formulas, you can ensure that the current date and time are inserted in a record whenever it is edited or updated. Most people do not need to know the exact time when a record was updated, so NOW is less likely to be used in a formula. But it is often useful to `date stamp' a record using the TODAY variable. To do this, first define a date field, then create a formula consisting simply of the word TODAY. Calculation formulas are not suitable if you want to show when a record was first created. For this, you need a constant formula (see the section Constants further on in this chapter). Note Note that NOW and TODAY can also figure in validation formulas. For example, you could use TODAY to invalidate checks that are out of date: Checkdate >> (TODAY - 365) Caution Do not use TODAY and NOW in a calculation which is attached to an indexed virtual field. When is a Calculation Performed? A record may contain just one or a number of calculated fields. When you save a record, SBase carries out all the calculations in the record and stores the results in the fields which the calculation formulas refer to. This applies both to new records and to edited records. Most, if not all, calculated fields will be Read Only. With these fields, calculations may also be performed while you are entering data in the record or editing it. This happens if you click in the field or if you pass through the field on the way to another field further down the field list. The result will be that the calculation is performed at least twice: every time you click in it and then again when you save the record. You can see this process in action by defining a time field with the calculation formula NOW attached to it. Provided you have set the time format so that seconds and milliseconds are shown, the time field will be updated every time you click in it. Usually, repeating a calculation gives the same result, and it doesn't matter how many times the calculation is carried out. Likewise, it doesn't usually matter in what order you enter data in fields, or edit fields. The only time these considerations may be important is when a record contains linked calculations, as explained in the next section. Linked Calculations It is sometimes important to consider the order in which calculations are carried out. If they are carried out in the wrong order, you may get the wrong result. The problem only arises with linked calculations, where the result of one calculation depends on the result of another. Normally, you do not need to take the order into account. When you save a record, SBase calculates fields in the order in which they occur in the field list; that is, it works out the result for a calculated field at the top of the record (in Record View) first and then passes to the next calculated field below. Incorrect results are only produced when the order in which the records should be calculated does not match the field list order. The following example shows how this might occur. Example This example presents two sets of calculations. In the first set the order in which the calculations should be performed matches the field list order and gives the correct results: Field Calculation Value Unit Cost Quantity Total Cost Unit Cost * Quantity $24.00 Discount Rate Discount (Total Cost * Discount Rate)/100 $4.80 Total Amount Total Cost - Discount $19.20 Here, the calculation formulas for Discount, and Total Amount are linked to the formula which is attached to Total Cost. These formulas generate the data values for their associated fields. The values in the other fields are fixed values entered by the user. What would happen if you now decided to modify the file definition in order to introduce two more fields, Tax Rate and Tax Amount? This step would be necessary if you wanted to include the local tax rate in your calculations. You would be able to modify the existing formulas in the file definition, but the two new fields would have to be added at the end of the field list. And the formula attached to Tax Amount would be calculated in the wrong order. After all the fields have been calculated once, the results would be as follows: Field Calculation Value Unit Cost Quantity Total Cost Unit Cost * Quantity $24.00 Discount Rate Discount (Total Cost * Discount Rate)/100 $4.80 Total Amount Total Cost - Discount + Tax Amount $19.20 Tax Rate Tax Amount (Total Cost - Discount) * Tax Rate/100 $2.88 You will notice that the result for Total Amount is incorrect, despite the fact that Tax Amount contains the correct value. The reason for this is that SBase calculates the value for Total Amount before it calculates the value for Tax Amount. When the value for Total Amount is calculated, the value of Tax Amount is 0.00. Calculation Count SBase supplies a straightforward solution to this problem. It is based on the fact that if you perform the calculations a second time, you will arrive at the correct result. You can do this by setting the Calculation Count option in the Set System Options requester. This feature allows you to specify the number of times a calculation (or a chain of calculations) is performed. With a more complicated set of linked calculations you may need to perform them three or four (or more) times before arriving at the correct result. In each case, you would set the Calculation Count to the number required. For further details, see Chapter 31 Customizing Your System. Another solution would be to create a new file in which the fields were in the correct order. To do this, you would enter the fields in the correct order in Query Fields command line, and then use the Query to File option to create a new file from the existing file. See Chapter 16 Reorganizing the Database for more details. Self-referencing Formulas A calculation formula should not normally contain the name of the field it is attached to. But if you edit a calculation formula after it has been added to the file definition, it is possible to insert the field name in the formula, or to create a new formula which includes the name. In this way, you could create a self-referencing formula for the field total, such as: total + amount When the formula is calculated the value in the field amount will be added to the value in total and then stored in total. Although this may appear to be a useful feature, this type of self- referencing formula is not recommended. The following example should make this clear. Suppose the initial values for the two fields total and amount were: amount: 25 total: 50 After the formula has been calculated once, the new values will be: amount: 25 total: 75 This, of course, is the correct result. But if the formula is calculated again, the values will be: amount: 25 total: 100 As you see, if the formula is calculated more than once, it gives the wrong result. Because it is difficult to ensure that a formula is only calculated once, self-referencing formulas of this type will invariably give the wrong result and should therefore be avoided. However, there are some self-referencing formulas that will always produce the desired result, no matter how many times they are calculated. Formulas which include the ternary operator (see the next section) provide one example. Another example would be a formula like this: LTRIM$(Firstname) LTRIM$ strips leading spaces from the text specified in the brackets. You might want to attach this formula to the Firstname field, in order to eliminate any leading spaces that the user might type in accidentally. Notice that this also provides an example of a calculated field which is not Read Only. When you define a calculation formula, SBase automatically makes the field Read Only. If the field is intended to be used for data entry, you will need to turn the Read Only attribute off after defining the formula. Checklist - When you attach a calculation formula to a field, it is not necessary to include the field name in the formula. - SBase can stop you making simple mistakes, but you can still produce meaningless formulas if you try hard enough, or if you edit the formula directly in the Text box. - If you need to construct a very complex formula, it may be easier to set up some intermediate calculation formulas than to try to achieve the desired result in a single very large formula. - The maximum length of a calculation formula is 255 characters. - You may use a formula for cross-file calculation, where it extracts data from another file. - The number of decimal places in a calculation result may be set with the directly entered FIX function, e.g. FIX (amount/3,2) (Only in SBase Professional 4). Constants A constant formula works in the same way as a calculation formula. It generates a result from other fields, functions, and values, and places it in the field the formula is attached to. The difference is that the result is only generated once when the record is first created. After that the formula becomes defunct and no longer operates. If you make a constant field Read Only, the field will retain its initial data no matter what changes you make elsewhere in the record. If the constant field is not Read Only, the initial data it contains can be edited or overwritten. Typical applications for constant formulas are: - To show the date when a record is created. - To set the record's serial number using the SER function. - To set some other kind of reference field such as an account number or a customer number. Generating Serial Numbers You can use the SER function to assign a serial number to each record in a file. To do this: 1. Define a numeric field which will hold the serial number. 2. In the File Definition requester, click on the field name to place it the Field box. 3. Click on the Constant button. 4. Enter: [F2M]SER("filename") as the constant formula for the field. For example, to assign a serial number to the records in a file called Orders, first define a constant field for the number, then attach the formula: [F2M] SER("Orders") When you create the first record, it will be given the value 1. This value will then be incremented by one for each record you add to the file. Using the Ternary Operator The ternary operator is one of SBase's more unusual features. It may take you a while to grasp the idea behind it, but it is certainly worth the effort. Put simply, the ternary operator allows you to create calculation formulas which can choose between two (or more) alternative results. Formulas like this have a decision-making ability built into them. Suppose you wanted to create a file which stored the results of a school examination. One way of doing this would be to use one record for each student. The record would include a marks field, showing the marks gained in the examination as a percentage figure. It could also include a text field called grade which showed at a glance whether a student has passed or failed. By using the ternary operator we can define a formula which sets grade to either `Pass' or `Fail' according to the student's percentage mark: (mark.Results >>= 75) ? "Pass": "Fail" The effect of this formula could be translated as: If the number in the field mark is greater than or equal to 75, store `Pass' in grade, otherwise store `Fail' in grade. The general form (or syntax) of the ternary operator is this: 3mcondition ? value1 : value2 The words in italics represent the specific values or conditions that are entered in a formula. In the example above, `Pass' is value1, and `Fail' is value2. The condition argument must be an expression that is either true or false, such as: date.Clients >> "18 July 1990" or initial.Clients = "B" The parentheses around the condition are optional, but it is a good idea to include them. By separating the condition from the other parts of the ternary operator, the parentheses make it easier to understand how the formula works. If you are familiar with programming languages, you will recognize the ternary operator as a more concise way of expressing the IF THEN ELSE statement. We could translate the example above to read: IF mark >>= 75 THEN grade = "Pass" ELSE grade = "Fail" Extended Ternary Operations Instead of entering a value as the second alternative in a ternary operation, you can enter another ternary operator. In this way, you can create a formula which chooses between three (or more) alternatives: (mark.Results >>= 75) ? "A" : (mark.Results >>= 60 ) ? "B" : "C" Here, our example formula sets grade to `A', `B' or `C'. Marks greater than or equal to 75% are graded A, marks between 60% and 74% are graded B, any other mark is graded C. This formula can be modified again to provide further alternatives: (mark.Results >>= 75) ? "A" : (mark.Results >> = 65) ? "B" : (mark.Results >>= 55) ? "C" : (mark.Results >>= 45) ? "D": "E" The number of alternatives you can build into a formula is limited by the maximum number of characters. A formula must not exceed 255 characters, including spaces. Self-referencing Ternary Operations The ternary operator proves one of the exceptions to the rule we stated earlier, which advised against creating self-referencing formulas. Normally, the drawback with this type of formula is that it may give a different result every time it is calculated. Using the ternary operator, you can avoid the problem by placing the field name (the name of the field to which the formula is attached) at the end of the formula. In other words, the last value in the formula should be the field itself. There is no other way of making a self-referencing formula totally foolproof. Take, for example, the formula: code.Address = "a" ? "London": code.Address = "b" ? "New York" :"Other" The first time you enter the letter `a' in the field code.Address the formula will give the result `London'. But if the formula is calculated again, it will give the result `Other'. The reason for this is that code.Address will contain the word `London', not the letter `a'. This value does not satisfy either of the two conditions so it is replaced with `Other'. The correct way of insuring against a false result like this would be: code.Address = "a" ? "London": code.Address = "b" ? "New York": code.Address Now when the formula is calculated a second time, it leaves the result as it is. None of the conditions are satisfied, so the field is assigned the value in the last alternative. Since the last alternative refers to the data already in the field, the initial value remains unchanged. Further Information You will find a list of the SBase functions in Appendix B, together with a brief description of each. DML commands are explained in the DML User Guide. . See also Chapter 9 Using Functions.