How ClarisWorks Operates
Calculated fields contain three components:
1. The formula you enter into ClarisWorks,
2. The format you specify for your output, and
3. The data that ClarisWorks displays in the field.
ClarisWorks stores the formula and format, but not the results of the calculations. Instead, it performs the calculations each time you display or print the calculated field.
Types of Functions
Although we generally think of "calculations" as related to numbers, ClarisWorks can "calculate" numbers, dates, times, or text. The output you get depends on the "operation" or "function" you specify. For example, "AVERAGE" is an arithmetic function that generates the average of the values or field references within parentheses after the word AVERAGE.
"TEXTTODATE" is a date function that converts the current date into a numeric value that you can use in numerical calculations.
"PROPER" is a text function that capitalizes the first character in every word involved in the "calculations."
ClarisWorks offers eight different categories of functions: business and financial, date and time, information, logical, numeric, statistical, text, and trigonometric functions; 100 different database and spreadsheet functions in all.
Complete descriptions and sample applications of these functions appear in Appendix A in the ClarisWorks documentation and in the on-line help system available within ClarisWorks.
Types of Calculated Fields
The ClarisWorks database environment lets you use these functions in two different types of calculated fields:
1. "Record Calculation Fields", which display the results of the calculation within each database record. ClarisWorks calls these "Calculation Fields."
2. "Summary Calculation Fields", which perform calculations across records. ClarisWorks calls these "Summary Fields."
You would use a Calculation Field to count the number of days between a client's last payment and the current date. That is the type of information you want to display in the client's record.
You would also use a Calculation Field to determine a student's average test grade. Again, you would display that information in each student's record.
You would use a Summary Field to count the number of sales people on your staff or to determine their average sales. These numbers would appear in a summary area at the end of a report and not in an individual record.
Figure 1 depicts a database layout that lists the grades for some of the students in a class. The "Average Grade" field in Figure 1 is a Calculation Field that computes each student's average grade during the marking period and displays that grade as part of each student's record. The formula for the Average Grade is AVERAGE('TEST.1','TEST.2', 'TEST.3','TEST.4').
The "Student Count" and "Student Average" fields are Summary Fields that appear in the Grand Summary segment of the report. The formulas that perform the calculations are COUNT('FNAME') and AVERAGE('AVERAGE GRADE') respectively.
As you can see from these examples, both types of fields use the same formulas and functions. You "tell" ClarisWorks whether to calculate within records or across records when you define the field (see Figure 2). When you check "Calculation" as a field type, ClarisWorks calculates within each record. Check "Summary" as a field type and ClarisWorks calculates across records.
The Elements of Calculated Fields
You must specify a formula for every calculated field; each formula includes some combination of the following four elements:
1. Other database fields that you enclose in single quotes in the formula. For example, a Total Sales field might contain the formula 'Region1 Sales'+ 'Region2 Sales'+'Region3 Sales'.
2. A built-in ClarisWorks function, such as SUM( ), TEXTTODATE, or UPPER( ). For example, the formula SUM('SALARY','COMMISSION','OTHER') in a Calculation Field will compute the total earnings of every employee. ClarisWorks will store that total within each record.
The formula AVERAGE('WEIGHT') in a Summary Field will compute the average weight of all the "unhidden" individuals.
3. Data that you type in the formula. This can be numeric data such as the "12" in the formula 'Monthly Income' * 12 or text constants that you enclose in double quotes (to differentiate between the text and field names), as in the formula "Dear " & 'LNAME'. (This formula lets you create a new field that combines data from other fields in the record.)
4. Operators, which indicate the type of calculation you want to perform. The ClarisWorks manual lists the operators you can use in formulas within ClarisWorks database documents.
Figure 1.
Figure 2.