The last few examples will show how calculations are performed across several forms. We will create an invoice screen for the Demonstration Subscription System, showing more elaborate examples of calculated fields.
For these examples we need two additional files, an invoice header file and an invoice detail file. The invoice header file contains the following fields:
Name Data Field Description
Type Length
invoice string 16 invoice number (key field)
subscriber string 16 subscriber code
subtotal money 8 invoice subtotal
taxrate real 8 sales tax rate
tax money 8 tax amount (subtotal*taxrate)
total money 8 invoice total
The invoice detail file contains the following fields:
Name Data Field Description
Type Length
invoice string 16 invoice number (key field)
years integer 4 number of years in subscription
magazine string 16 magazine code
year_rate money 8 yearly rate for magazine
amount money 8 years * year_rate
The invoice screen is shown below.
The invoice screen is composed of three forms. The first form is the invoice header and contains the invoice and subscriber codes. This screen also has the subtotal, tax and total fields for the invoice. These fields show the values calculated when an existing invoice is displayed. The second form is repeating form for entering the individual invoice detail lines. This form appears below the field titles in the middle of the screen. Finally, there is a trailer screen that contains the subtotal and sales tax calculations.
In the screen above, each form is separated from the next by a dashed line. There are several detail lines shown in the above screen since an invoice can have any number of detail lines.
First, we need a calculation to determine the amount charged for each magazine that a subscriber receives. The value in the amount field is calculated by multiplying the years field by the year_rate.
Since form provides for repeating forms, the Copy/Calculate field also provides a technique for totaling a value across a repeating form. In the example above, the amount field in each detail line is totaled to get the total of all the subscriber's magazines.
Next, the total for all the magazines is taken and multiplied by the sales tax rate to determine the sales tax amount. Finally, the sales tax amount is added to the subtotal to get the total for the invoice. All these calculations are performed using the calculated fields features in the form program. Note that these calculations are performed across several different forms and across repeating forms.
The first calculation performed using the invoice forms is calculating each line item amount in the detail lines. The amount field has the following in its Copy/Calculate field:
round (years*year_rate)
To add together all the amount fields in the detail lines, another field that contains the total of all the amount fields in the detail form is created on the detail screen. This field, called subtotal, is invisible and uses the total statement to accumulate the value of all the amount fields. The total statement adds together the contents of all occurrences of the field named within the parentheses. This function is used when totaling one field in a repeating form. When totaling several fields in the same form, just use the addition (+) operator. A listing of just the detail line form is shown in Figure 10.
Form Name detail
Data File invoiced
Beginning form no Unique records no
Repeating form yes Append form yes
Freeze form no Additional form no
Required form no for one record
Next form name trailer
Field name years Field length 6
Entry Allowed yes Input required no
In Data File yes Invisible no
Field name magazine Field length 15
Entry Allowed yes Input required yes
In Data File yes Invisible no
Validation file mag
Validation Form magazine
Field name Field length 22
Entry Allowed no Input required no
In Data File no Invisible no
Lookup field title
Field name year_rate Field length 10
Entry Allowed yes Input required yes
In Data File yes Invisible no
Lookup field year_rate
Field name amount Field length 10
Entry Allowed no Input required no
In Data File yes Invisible no
Calculation round (years*year_rate)
Field name invoice Field length 15
Entry Allowed no Input required no
In Data File yes Invisible yes
Copy from pre- yes User edit no
vious field
Field name subtotal Field length 6
Entry Allowed no Input required no
In Data File no Invisible yes
Calculation total (amount)
Edit Data Type money
Figure 10
The subtotal field, the field receiving the total of the amount fields, is a form variable. It is not a field in a data file. The total function will not work properly when the field receiving the total is a field in a data file.
So for the detail lines in the subscriber invoice have been extended, and all the amounts for the invoice subtotaled. Now the subtotal will be used to calculate the sales tax amount and the invoice total.
These calculations are done on the trailer screen of the invoice. First, the invoice subtotal is displayed on the trailer screen in the field labeled Invoice Subtotal. This is done by merely copying the invisible subtotal field from the detail line screen into the subtotal field on the trailer screen. The Copy/Calculate field for the trailer subtotal field contains
detail.subtotal
which causes the subtotal to be copied to the trailer screen. The subtotal field in the detail screen was not made visible (although it could have been) so that the detail screen would be less cluttered.
The sales tax rate is entered into the taxrate field on the trailer screen. A tax rate of 6.8 percent is entered as 6.8 in the Sales Tax Rate field. To calculate the sales tax amount, the subtotal field is multiplied by the Sales Tax Rate field and then divided by 100. The Copy/Calculate field for the Tax field contains:
round (subtotal * taxrate / 100)
The round function is used here so that the tax amount will not have any fractional pennies after division by 100.
The last calculation performed is adding the tax amount to the subtotal to give the invoice total. The Copy/Calculate field for the Total field contains:
subtotal+tax
We now have an invoice form for the Demonstration Subscription System that extends detail lines, totals the invoice lines, calculates the tax amount from the subtotal, and calculates the invoice total. The complete form description is shown in Figure 11.
Form Name header
Data File invoiceh
Beginning form yes Unique records yes
Repeating form no Append form no
Freeze form yes Additional form no
Required form no for one record
Next form name detail
Field name invoice Field length 15
Entry Allowed yes Input required yes
In Data File yes Invisible no
Field name subscriber Field length 15
Entry Allowed yes Input required yes
In Data File yes Invisible no
Validation file sub
Field name Field length 35
Entry Allowed no Input required no
In Data File no Invisible no
Lookup field name
Field name subtotal Field length 14
Entry Allowed no Input required no
In Data File yes Invisible no
Field Name tax Field length 14
Entry Allowed no Input required no
In Data File yes Invisible no
Field name grandtotal Field length 14
Entry Allowed no Input required no
In Data File yes Invisible no
Form Name detail
Data File invoiced
Beginning form no Unique records no
Repeating form yes Appended form yes
Freeze form no Additional form no
Required form no for one record
Next form name trailer
Field name years Field length 6
Entry Allowed yes Input required no
In Data File yes Invisible no
Field name magazine Field length 15
Entry Allowed yes Input required yes
In Data File yes Invisible no
Validation file mag
Field name (none) Field length 22
Entry Allowed no Input required no
In Data File no Invisible no
Lookup field title
Field name year_rate Field length 10
Entry Allowed yes Input required yes
In Data File yes Invisible no
Lookup field year_rate
Field name amount Field length 10
Entry Allowed no Input required no
In Data File yes Invisible no
Calculation round (years*year_rate)
Field name invoice Field length 15
Entry Allowed no Input required no
In Data File yes Invisible yes
Copy from yes User Edit no
previous field
Field name subtotal Field length 6
Entry Allowed no Input required no
In Data File no Invisible yes
Calculation total (amount)
Edit Data Type money
Form Name trailer
Data File invoiceh
Beginning form no Unique records yes
Repeating form no Append form yes
Freeze form no Additional form yes
Required form yes for one record
Next form name header
Field name subtotal Field length 14
Entry Allowed no Input required no
In Data File yes Invisible no
Calculation detail.subtotal
Field name taxrate Field length 8
Entry Allowed yes Input required yes
In Data File yes Invisible no
Field name tax Field length 14
Entry Allowed no Input required no
In Data File yes Invisible no
Calculation round (subtotal * taxrate / 100)
Field name grandtotal Field length 14
Entry Allowed no Input required no
In Data File yes Invisible no
Calculation subtotal+tax
Figure 11