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:
To begin with, we will set the calculation properties for the first FlowField, Average Listeners.
Listenership Ledger
or 50006
into the Table field. We can either typeListener Count
or 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:
No
.50005
, and then set Field to Fee Amount.Date
, the Type as FIELD
, and the Value with 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 for a single value, we could have also 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 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.
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 with the Field as Date, the Type as FIELD, and the Value with Date Filter.Vinyl
|CD
|MP3
. This means that 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 that satisfies the Date Filter specified in the Radio Show table.