Almost all data fields have a FieldClass property. FieldClass has as much effect on the content and usage of a data field as the data type; in some instances, it has more effect. In the next chapter, we'll cover most of the field properties, but we'll discuss the FieldClass property options now.
When the FieldClass is Normal, the field will contain the type of application data that's typically stored in a table—the contents we would expect based on the data type and various properties.
FlowFields must be dynamically calculated. FlowFields are virtual fields that are stored as metadata; they do not contain data in the conventional sense. A FlowField contains the definition of how to calculate (at runtime) the data that the field represents and a place to store the result of that calculation. Generally, the Editable property for a FlowField is set to No..
Depending on the CalcFormula method, this could be a value, a reference lookup, or a Boolean. When the CalcFormula method is Sum, the FieldClass connects a data field to a previously defined SumIndexField in the table defined in the CalcFormula. The FlowField processing speed will be significantly affected by the key configuration of the table being processed. While we must be careful not to define extra keys, having the right keys defined will have a major effect on system performance and thus, on user satisfaction.
A FlowField value is always 0, blank, or false, unless it has been calculated. If a FlowField is displayed directly on a page, it is calculated automatically when the page is rendered. FlowFields are also automatically calculated when they are the subject of predefined filters as part of the properties of a data item in an object. (This will be explained in more detail in the chapters covering Reports and XMLports.) In all other cases, a FlowField must be forced to calculate using the C/AL RecordName.CALCFIELDS(FlowField1, [FlowField2],...) function or by the use of the SETAUTOCALCFIELDS function. This is also true if the underlying data is changed after the initial display of a page (that is, the FlowField must be recalculated to take a data change into account).
When a field has its FieldClass set to FlowField, another directly associated property becomes available—CalcFormula. (Conversely, the AltSearchField, AutoIncrement, and TestTableRelation properties disappear from view when FieldClass is set to FlowField). The CalcFormula method is the place where we can define the formula for calculating the FlowField. On the CalcFormula property line, there is an ellipsis button. Clicking on that button will bring up the following screen:
Click on the drop-down button to show the seven FlowField methods:
The seven FlowFields are described in the following table:
|
FlowField Method |
Field data type |
Calculated value as it applies to the specified set of data within a specific column (field) in a table |
|---|---|---|
|
Sum |
Decimal |
The sum total |
|
Average |
Decimal |
The average value (the sum divided by the row count) |
|
Exist |
Boolean |
Yes or No / True or False - does an entry exist? |
|
Count |
Integer |
The number of entries that exist |
|
Min |
Any |
The smallest value of any entry |
|
Max |
Any |
The largest value of any entry |
|
Lookup |
Any |
The value of the specified entry |
The Reverse Sign control allows us to change the displayed sign of the result for FlowField types Sum and Average only; the underlying data is not changed. If a Reverse Sign is used with the FlowField type Exists, it changes the effective function to does not Exist.
Table and Field allow us to define the Table and the Field within that table to which our Calculation Formula will apply. When we make the entries in our Calculation Formula screen, no validation checking is done by the compiler to check whether we have chosen an eligible table and field combination. This checking doesn't occur until runtime. Therefore, when we create a new FlowField, we should test it as soon as we have defined it.
The last, but by no means the least significant component of the FlowField calculation formula is the Table Filter. When we click on the ellipsis in the table filter field, the window shown in the following screenshot will appear:
When we click on the Field column, we will be invited to select a field from the table that was entered into the Table field earlier. The Type field choice will determine the type of filter. The Value field will have the filter rules defined on this line, which must be consistent with the Type choices described in the following table:
|
Filter type |
Value |
Filtering action |
OnlyMax-Limit |
Values-Filter |
|---|---|---|---|---|
|
Const |
A constant which will be defined in the Value field |
This uses the constant to filter for equally valued entries | ||
|
Filter |
A filter that will be spelled out as a literal in the Value field |
This applies the filter expression from the Value field | ||
|
Field |
A field from the table within which the FlowField exists |
This uses the contents of the specified field to filter equally valued entries |
False |
False |
|
If the specified field is a FlowFilter and the |
True |
False | ||
|
This causes the contents of the specified field to be interpreted as a filter (See Balance at Date field in the G/L Account table for an example) |
True or False |
True | ||
FlowFilters control the calculation of FlowFields in the table (when the FlowFilters are included in the CalcFormula). FlowFilters do not contain permanent data, but instead, they contain filters on a per-user basis, with the information stored in that user's instance of the code that is being executed. A FlowFilter field allows a filter to be entered at a parent record level by the user (for example, G/L Account) and applied (through the use of FlowField formulas, for example) to constrain what child data (for example, G/L Entry records) is selected.
A FlowFilter allows us to provide flexible data selection functions to the users. The user does not need to have a full understanding of the data structure to apply filtering in intuitive ways to both the primary data table and the subordinate data. Based on our C/AL code design, FlowFilters can be used to apply filtering on multiple tables that are subordinate to a parent table. Of course, it is our responsibility as developers to make good use of this tool. As with many C/AL capabilities, a good way to learn more is by studying the standard code designed by the Microsoft developers of NAV and then experimenting.
A number of good examples on the use of FlowFilters can be found in the Customer (Table 18) and Item (Table 27) tables. In the Customer table, some of the FlowFields using FlowFilters are Balance, Balance (LCY), Net Change, Net Change (LCY), Sales (LCY), and Profit (LCY) where LCY stands for local currency. The Sales (LCY) FlowField FlowFilter usage is shown in the following screenshot:
Similarly constructed FlowFields using FlowFilters in the Item table include Inventory, Net Invoiced Qty., Net Change, Purchases (Qty.), as well as other fields.
Throughout the standard code, there are FlowFilters in most of the master table definitions; there are the Date Filters and Global Dimension Filters (global dimensions are user-defined codes that facilitate the segregation of accounting data by groupings such as divisions, departments, projects, customer type, and so on). Other FlowFilters that are widely used in the standard code related to Inventory activity such as Location Filter, Lot No. Filter, Serial No. Filter, and Bin Filter.
The following pair of images shows two fields from the Customer table, both with a Data Type of Date. On the left side of the screenshot is the Last Date Modified field (FieldClass of Normal) and on the right side of the screenshot is the Date Filter field (FieldClass of FlowFilter). It's easy to see that the properties of the two fields are very similar, except for the properties that differ because one is a Normal field and the other is a FlowFilter field.
In our application, we have decided to have several FlowFields and a FlowFilter in Table 50000 – Radio Show. The reason for having these fields is to provide instant analysis for individual shows based on the detailed data stored in subordinate tables. In Chapter 2, we showed Table 50000 with fields 100 through 130 and 1090 but didn't provide any information about how the fields should be constructed. Let's go through the construction process now. Here's how the fields 100 through 130 and 1090 should look when we open Table 50000 in the Table Designer. If you didn't add these fields during the Chapter 2 exercise, do that now.
These five fields will be used for statistical analysis for each Radio Show, as follows:
- Field 100 – Average Listeners: The average number of listeners that are reported by the ratings agency
- Field 110 – Audience Share: The percentage of one station's total estimated listening audience per time slot
- Field 120 – Advertising Revenue: The sum total of the advertising revenue generated by the show
- Field 130 – Royalty Cost: The sum total of the royalties incurred by the show for playing copyrighted material
- Field 1090 – Date Filter: 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.
- If Table 50000 isn't already open in the Table Designer, then open it by navigating to Tools | Object Designer and select the Table button on the left as the object type. Find table 50000, Radio Show, select it, and then click on Design.
-
Scroll down to field 100, select it, and click on the properties icon at the top of the screen, or press Shift + F4. Highlight the FieldClass property, click on the drop-down arrow
and select FlowField. A new property called CalcFormula will appear, directly underneath the FieldClass property. An Assist Edit ellipsis button 
will appear. Click on it and the Calculation Formulaform will appear as follows:
-
Select Average from the Method dropdown, leave the Reverse Sign field unchecked, and type
Listenership Ledgeror50006into the Table field. We can either typeListener Countor click the Lookup arrow button
to select the Listener Count field from the table. Lastly, we need to define a filter to allow the Radio Show statistics to be reviewed, based on a user-definable date range. Click on the Assist Edit ellipsis button on the Table Filter field, and the following Table Filter screen will appear:
- Click on the Lookup arrow in the Field column and select Date from the Listenership Ledger – Field List.
- In the Type column, click on the drop-down arrow. You will see three choices for defining what type of filter to apply: CONST, FILTER, FIELD. In this case, we need to apply a field filter, so choose FIELD.
- 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 the Listenership Ledger, to control what data to use for the FlowField Average calculation.
- Click on OK and our Calculation Formula screen should look like this:
- Click on OK and the CalcFormula property will fill in with the following text:
- Since this is a text field, we can enter the syntax manually, but it's much easier and less error prone to use the Calculation Formula screen.
- Set the Editable property to
No. - For Field 110 – Audience Share, repeat the procedure that we just went through, but for Field, select Audience Share from the Listenership Ledger – Field List. Our result should look like the following screenshot:
- For the fields 120, Advertising Revenue, and 130, Royalty Cost, the FlowField calculation is a sum with multiple fields that have filters applied to them. 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.
- For Advertising Revenue, make the Method as Sum and for Table, enter Radio Show Ledger or the table number,
50005, and then set Field to Fee Amount. - Click on the Assist Edit button for the table filter. Fill in the first row with the Field as
Date, the Type asFIELD, and the Value with Date Filter. Fill in the second row with Field set toDataFormat, Type toFILTER, andAdvertisementin the Value column (since we are filtering for a single value, we could have also usedCONSTfor the Type value). The FlowField will now add up all the Fee Amount values that have a Format option selected asAdvertisementand fall within the range of the date filter applied from the Radio Show table.
Advertisement is an available value for the DataFormat field (Data Type Option). In the Radio Show Ledger, we typed a value that was not an Option value such as
Commercial, an error would have displayed showing us what the available Option choices were.
- Click on OK on the Table Filter form, and OK again on the Calculation Formula form.
- Start
Royalty Costthe 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 with the Field as Date, the Type as FIELD, and the Value with Date Filter. - Fill in the second row set Field to Format and Type to FILTER. In the Value column, enter
Vinyl|CD|MP3. This means that we will filter for all records where the field Format contains a value equal toVinylORCDORMP3(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 asVinyl,CD, orMP3and a date that satisfies the Date Filter specified in the Radio Show table.
- The last field that 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 as no calculation formula is required.
- Select the properties for the Date Filter field and set the FieldClass property to FlowFilter, as shown here:
- Close the Date Filter - Properties window and exit Table Designer, compiling the Radio Show table as we do so. If we do not previously exit and compile our table modifications through this exercise, 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 that were changed from Normal to either FlowField or FlowFilter. This is because a Normal field can store normal data, but the other two field types do not do this. 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 that we've made additional changes to this table.