"SBase Personal - 39. FORM CALCULATION FORMULAS" 39 FORM CALCULATION FORMULAS Creating a Calculation Formula Calculations Formula Evaluation Variable Calculations Assignment Calculations Evaluation Control Prefixes 39 FORM CALCULATION FORMULAS Only in SBase Professional 4 Calculations allow you to design forms which have their own built-in data processing facility. By means of calculations, forms can be used in SBase not only to display record data but also to carry out many of the tasks that would normally be left to a program. They can calculate results on existing data and they can operate on new data as you enter it. As the next section describes, there are many different types of calculation. Some of them can be regarded as special types of program variables; you will use these to store the results from other calculations. Other types of calculation have formulas attached to them and are closer to calculated fields in a SBase file. The calculations in a form, however, are not directly attached to fields nor are they stored in SBase files but exist only in a form. +-----+ Fig 1 | CLC | +-----+ Creating a Calculation The process of creating a calculation is very similar to defining a calculation formula for a SBase file definition. The main difference is that the calculation has to be given a name so that it can be referred to by other calculations or by programs. 1. Choose the Calculation (CLC) object type from the toolbox. 2. Click where you want the calculation to appear. - Use grid with Snap to Grid or Crosshairs to position the calculation. 3. Enter a name ending in $ for calculations that will hold a text or string result, and % for all other types. *** See Fig 39-1 *** 4. Click OK. *** See Fig 39-2 *** 5. Enter the formula for the calculation in the Calculation requester, unless it is a Variable Calculation (see below). - The requester will show a list of the fields (in the current file) which have been added to the form. If you want to refer to other calculations in the formula, click on the arrow button next to the file name. This cycles through all the available files. After the field lists the Form Designer Pro shows a list of the names of all calculations already defined as if they belonged to a file called Formcalcs. 6. Click OK. The Form Designer Pro positions the calculation on the form. You may now size or move it. - You may refer to fields that do not actually appear on the form. - You may refer to other calculations provided they have already been placed on the form. - A calculation name should only appear in the calculation if it is intended to be self-referencing. You can only incorporate the name of a calculation in its formula after it has been created and placed on the form. - The Form Designer Pro resets the default length of a calculation every time it is modified. Formula Calculations Formula calculations are the same as the calculations usually attached to fields in a file definition. See the chapter on Derived Values in Volume 1 for a full discussion of this topic. They may be of use on a form, but the results will not be stored in a database field unless you include extra instructions (see Evaluation Control Prefixes, below, and Chapter 42 Commands). Formulas combine field names, functions, operators and literal values to generate results, which are displayed in the calculation area. Typically, this type of calculation is used to derive a total from the contents of other calculations or fields and then to display it on the form. Another application would be to display a date by using the keyword TODAY in the formula. Examples are: Calculation name Calculation formula subtot1% Quantity * Price name$ Left$(Firstname,1) + ". " + Lastname When$ TODAY Formula Evaluation During data entry, a formula is evaluated and the result is displayed after each of the following events; - Saving a record. - Moving the insertion point through the calculation by pressing ENTER in the field that precedes it in data entry order, assuming the calculation is read only. - Clicking the calculation, provided it is read only. - When you press ENTER after typing in the calculation, provided it is NOT read only. - Retrieving another record. Variable Calculations These are calculations which do not have a formula attached to them. They are intended to be treated as variables and provide an interface to DML programs which process forms. A variable calculation may have data typed into it by the user; the data may then be accessed by a program. Suppose, for example, you define a blank calculation with the name x%. In the form's data entry order x% is number 5. If you want to input data to a program from the form which contains x%, you must include the program line: ENTER 5 When this line is executed, the insertion point appears on screen in the box belonging to x% and the value entered by the user is assigned to x%. If you now executed the MEMORY command, x% and its contents will be listed along with all the other program variables. And any other DML commands which operate on variables, such as CLEAR, will also work with a calculation variable. Note ENTER is the only input command that can be used with a form. Forms are object oriented rather than character oriented so you cannot use ASK or GET in combination with LOCATE. Another point to note is that when you use ENTER with a calculation, you can only identify the calculation by its field order number; a command such as: ENTER fred% will not work. Assignment Calculations This type of calculation is used to assign a value to a field or another calculation. The calculation requires the keyword LET at the beginning. An assignment calculation has two parts: an assignment and a formula. For example, the calculation y% may contain: LET fieldA.FILEA = fieldB.FILEB * 1.15 Here the right-hand part of the calculation will be updated and displayed in y% in the normal way. The result will be assigned to fieldA, which may belong to another file and does not have to be placed on the form. When SBase reads a new record - for example, when you select a record with the browsing controls - the right-hand part of the calculation is evaluated and displayed, but no assignment is made unless the calculation is evaluated as a result of editing or saving. Only one assignment per calculation is allowed. Multiple assignments may be achieved with a command. Evaluation Control Prefixes Two special prefixes may be used with Assignment Calculations and Formula Calculations to vary the way they work. AFTER The AFTER keyword causes a formula to be evaluated more frequently than usual. You may use this feature to force evaluation after any editing event; the formula AFTER : Quantity * Price will be evaluated and the result displayed whenever data changes on the form. By including a field reference the evaluation may be triggered by an editing event for that field: AFTER Item : Quantity * Price Now a result will be obtained when Item is edited or its value changes, as well as after the usual evaluation conditions. POST The POST keyword restricts evaluation of a calculation to when data is saved. This allows you to ensure that data is only assigned to a field once, after all the other calculations in the form have been evaluated. For example, you might wish to transfer data from an invoice form to a balance field in a completely different file. You would only want to do this once, not every time data was edited, so the syntax for a calculation z% should be: POST LET Balance.CUSTOMER = Balance.CUSTOMER + Total.ORDER The contents of Total.ORDER will only be added to Balance.CUSTOMER when all the other form calculations have been performed and the record is saved. The intermediate results will be shown whenever the formula is evaluated. Note If you are using an Assignment Calculation to send or `post' data to a record in another file, remember to save the record after completing a task.