In our application, we have decided to have several FlowFields and a FlowFilter in Table 50000 Radio Show. The reason for these fields is to provide instant analysis for individual shows based on the detailed data stored in subordinate tables. In Chapter 2, Tables, we showed Table 50000 with fields 100 through 130 and 1,090 but didn't provide any information on how those fields should be constructed. Let's go through that construction process now. Here's how the fields 100 through 130 and 1,090 should look when we open Table 50000 in the Table Designer. If you didn't add these fields as described in the Chapter 2, Tables, section Creating and Modifying Tables, do that now:

The following five fields will be used for statistical analysis for each Radio Show:

• Field 100 (Average Listeners): This is the average number of listeners as reported by the ratings agency
• Field 110 (Audience Share): This is the percentage of one station's total estimated listening audience per time slot
• Field 120 (Advertising Revenue): This is the sum total of the advertising revenue generated by show
• Field 130 (Royalty Cost): This is the sum total of the royalties incurred by the show by playing copyrighted material
• Field 1090 (Date Filter): This is a filter to restrict the data calculated for the preceding four fields

To begin with, we will set the calculation properties for the first FlowField, Average Listeners:

1. If Table 50000 isn't already open in the Table Designer, then open it through Tools | Object Designer, select the Table button on the left as the object type. Find table 50000, Radio Show, select it and click Design.
2. Scroll down to field 100, select it and click the properties icon at the top of the screen or click Shift + F4. Highlight the FieldClass property, click on the drop-down arrow and select FlowField. A new property will appear called CalcFormula, directly underneath the FieldClass property. An Assist Edit ellipsis button will appear. Click on it and the Calculation Formula form will appear:

3. Select Average from the Method dropdown, leave the Reverse Sign field unchecked, and type Listenership Entry or 50006 into the Table field. We can either type Listener Count or click the lookup arrow button to select the Listener Count field from the table.
4. Lastly, we want to define a filter to allow the Radio Show statistics to be reviewed based on a user definable date range. Click the Assist Edit ellipsis button on the Table Filter field. The following Table Filter screen will appear:

5. Click on the Lookup arrow in the Field column and select Date from the Listenership Entry - Field List.
6. In the Type column, click on the drop-down arrow. We see three choices for defining what type of filter to apply: CONST, FILTER, FIELD. In this case, we want to apply a field filter, so choose FIELD.
7. The last part of the Table Filter definition is the Value column. Click on the lookup arrow in the Value column and choose Date Filter from the Radio Show - Field List. This will cause the Date Filter field value in the Radio Show record to be applied to the values in the Date field in Listenership Entry, controlling what data to use for the FlowField Average calculation.
8. Select OK and our Calculation Formula screen should look like this:

9. Click on OK and the CalcFormula property will fill in with the following text:

10. Since this is a text field, we could enter the syntax manually, but it's much easier and less error prone to use the Calculation Formula screen.
11. Set the Editable property to No.
12. For Field 110, Audience Share, repeat the procedure we just went through, but for Field select Audience Share from the Listenership Entry Field List. Our result should look like the following:

13. For Field 120, Advertising Revenue and  Field 130, Royalty Cost, the FlowField calculation is a sum with multiple fields having filters applied. For each field, the first step will be to set the FieldClass property to FlowField, then click on the Assist Edit button in the CalcFormula property to call up the Calculation Formula screen.
14. For Advertising Revenue, make the Method option as Sum, for Table enter Radio Show Ledger or the table number, 50005, and set Field to Fee Amount.

15. Click on the Assist Edit button for the table filter. Fill in the first row with the Field value as Date, Type as FIELD, and Value as Date Filter. Fill in the second row with Field set to DataFormat, Type to FILTER, and Advertisement in the Value column (since we are filtering to a single value, we could also have used CONST for the Type value). The FlowField will now add up all the Fee Amount values that have a Format option selected as Advertisement and which fall within the range of the date filter applied from the Radio Show table:

Advertisement is an available value of the field DataFormat (Data Type Option). If in the Radio Show Entry we had typed a value that was not an Option value such as Commercial, an error message would have displayed showing us what the available Option choices are:

16. Click OK on the Table Filter form, and OK again on the Calculation Formula form.

17. Start Royalty Cost the same way (Method is Sum) all the way through the Table (table 50005) and Field choices in the Calculation Formula form. Click on the Assist Edit button for the table filter. Just as before, fill in the first row Field with a value of Date, the Type with FIELD, and the Value with Date Filter.
18. Fill in the second row set Field to Format and Type to FILTER. In the Value column, enter Vinyl|CD|MP3. This means we will filter for all records where the field Format contains a value equal to Vinyl OR CD OR MP3 (the Pipe symbol is translated to the Boolean OR). As a result, this FlowField will sum up all the Fee Amount values that have a Format option selected as Vinyl, CD, or MP3, and a date satisfying the Date Filter specified in the Radio Show table:

19. The last field we will define in this exercise is the Date Filter field. We have already been referencing this Radio Show table field as a source of a user-defined date selection to help analyze the data from the listenership, payable, and revenue data, but we have not yet defined the field. This one is much easier than the FlowFields because no calculation formula is required.
20. Select the properties for the Date Filter field and set the FieldClass property to FlowFilter as shown here:

21. Close the Date Filter - Properties window and exit Table Designer, compiling the Radio Show table as we do so. If through this exercise, we had not previously exited and compiled our table modifications, we will get an error message beginning with The schema synchronization may result in deleted data. The following destructive changes were detected:. This is followed by a list of all the fields in which we made changes that could affect previously stored data. In this case, that is a list of all the fields which were changed from Normal to either FlowField or FlowFilter. This is because a Normal field can store normal data and the other two field types do not. Since we have no data in any of the changed fields, we should choose the Synchronize Schema option of Force to override the error message and complete the save and compile step. Ideally we should also update the Version List field of the table object to indicate we've made additional changes to this table.