Data Types and Fields

"You can't build a great building on a weak foundation. You must have a solid foundation if you're going to have a strong superstructure."

– Gordon B. Hinckley

"Perfection is achieved, not when there is nothing more to add, but rather when there is nothing more to take away."

– Antoine de Saint-Exupéry

The design of an application should begin at the simplest level, that is, with the design of the data elements. The type of data our development tool supports has a significant effect on our design. Because Business Central is designed for financially-oriented business applications, Business Central data types are financially- and business-oriented.

In this chapter, we will cover many of the data types that we will use within Business Central. For each data type, we will cover some of the more frequently modified...

Basic definitions in Business Central

First, let's review some basic Business Central terminology:

• Data type: This defines the kind of data that can be held in a field, whether it be numeric (such as integer or decimal), text, a table RecordID, time, date, Boolean, and so forth. The data type defines what constraints can be placed on the contents of a field, determines the functions in which the data element can be used (not all data types are supported by all functions), and defines what the results of certain functions will be.
• Fundamental data type: This is a simple, single component structure consisting of a single value at any point in time, for example, a number, a string, or a Boolean value.
• Complex data type: This is a structure that's made up of or relates to simple data types, for example, records, program objects, such as pages or reports, Binary Large OBjects (BLOBs), DateFormulas, external files, and indirect reference variables.
• Data element...

Fields

A field is the basic element of data definition in Business Central—the atom in the structure of a system. The elemental definition of a field consists of its number, its description (name), its data type, and, of course, any properties that are required for its particular data type. A field is defined by the values of its properties and the AL code contained in its triggers.

Field properties

The specific properties that can be defined for a field depend on the data type. There is a minimum set of universal properties, which we will review first. Then, we will review the rest of the more frequently used properties, some of which are data dependent and some are not. Check out the remaining properties by using Microsoft Documents.

We can access the properties of a field while viewing the table in Visual Studio Code, highlighting the field, and then clicking inside the curly braces and pressing Ctrl + spacebar. All of the property screenshots in this section were obtained in this way for fields within the Radio Show table. As we review various field properties, you will learn more if you follow along in your Business Central system using Visual Studio Code. Explore different properties and the values they can have. Use Microsoft Docs liberally for additional information and examples.

When a property value is not explicitly set, the default...

Field triggers

To view the field triggers, let's look at our 50100 "Radio Show" table. Add the following triggers to the No. field within the curly braces:

Each field has two triggers, the OnValidate() trigger and the OnLookup() trigger, which function as follows:

• OnValidate(): The AL code in this trigger is executed whenever an entry is made by the user. The intended use is to validate that the entry conforms to the design parameters for the field. It can also be executed under program control through use of the VALIDATE function (which we will discuss later).

• OnLookup(): Lookup behavior can be triggered by clicking on the lookup arrow in a field, as shown in the following screenshot:
  
• If the field's TableRelation property refers to a table, then the default behavior is to display a drop-down list to allow for the selection of a table entry to be stored in this field. The list will be based on Field Groups that have been defined for the table....

Field events

Instead of placing your business code in the OnValidate trigger of a field, you can also subscribe to system generated events. For each field, we can subscribe to the OnBeforeValidate event and the OnAfterValidate event. These events are triggered, as their name suggests, before and after the code we place in the OnValid­ate trigger.

Subscribing to these events can be useful to avoid making modifications to the objects that are shipped by Microsoft so that future updates are easier to implement and merging code is prevented.

More information about events in Dynamics Business Central can be found on MSDN: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-events-in-al.

Data structure examples

Some good examples of tables in the standard product to review for particular features are as follows:

• Table 18 Customer has a variety of data types and field classes. This table contains some fairly complex examples of AL code in the Triggers table. A wide variety of field property variations can be seen in this table as well.

• Table 14 Location and Table 91 User Setup both have good examples of OnValidate trigger AL code, as do all of the primary master tables (Customer, Vendor, Item, Job, and so on).

Field numbering

The number of each field within its parent table object is the unique identifier that Business Central uses internally to identify that field. We can easily change a field number when we are initially defining a table layout, but after other objects, such as pages, reports, or codeunits, reference the fields in a table, it becomes difficult to change the number of referenced fields. Deleting a field and reusing its field number for a different purpose is not a good idea and can easily lead to programming confusion.

We cannot safely change the definition of, re-number, or delete a field that has data present in the database easily. The same can be said for reducing the defined size of a field to less than the largest size of the data that's already present in that field. However, if we force the change, the force function will override the system's built-in safeguards. This action can truncate or delete data.

When we add new fields to standard Business Central...

Field and variable naming

In general, the rules for naming fields (data elements in a table) and variables (data elements within the working storage of an object) are the same, and we will discuss them on that basis. Information on this can be found in the released C/AL Coding Guidelines at https://community.dynamics.com/nav/w/designpatterns/156.cal-coding-guidelines, including a How do I video.

Variables in Business Central can either be global (with a scope across the breadth of an object) or local (with a scope only within a single function). Variable names should be unique within the sphere of their scope.

Uniqueness includes not duplicating reserved words or system variables. Refer to the AL Reserved Words list in the Developer and IT pro help for more information.

Avoid using any word as a variable name that appears as an uppercase or capitalized word in either the Developer and IT-pro help or any of the published Business Central technical documentation. For example, we shouldn...

Data types

In this section, we are going to segregate the data types into several groups. First, we will look at fundamental data types and then complex data types.

Fundamental data types

Fundamental data types are the basic components from which the complex data types are formed. They are grouped into Numeric, String, and Date/Time data types.

Numeric data

Just like other systems, Business Central supports several numeric data types. The specifications for each Business Central data type are defined for Business Central, independent of the supporting SQL Server database rules. But some data types are stored and handled somewhat differently from a SQL Server point of view than the way they appear to us as Business Central developers and users. For more details on the SQL Server-specific representations of various data elements, refer to the developer and IT pro help. Our discussion will focus on the Business Central representation and handling for each data type.

The various numeric data types are as follows:

• Integer: This is an integer number ranging from -2.147.483.646 to +2.147.483.647.
• Decimal: This is a decimal number in the range of +/- 999.999.999.999.999,99. Although it is possible to construct larger numbers, errors such as overflow, truncation, or loss of precision might occur. In addition, there is no facility to...

String data

The following are the data types that are included in String data:

• Text: This contains any string of alphanumeric characters. In a table, a Text field can be from 1 to 250 characters long. In working storage within an object, a Text variable can be any length if there is no length defined. If a maximum length is defined, it must not exceed 1,024. Business Central does not require a length to be specified, but if we define a maximum length, it will be enforced. When calculating the length of a record for design purposes (relative to the maximum record length of 8,000 bytes), the full defined field length should be counted.

• Code: Help says the length constraints for Code variables are the same as those for text variables, but the AL editor enforces limits of 1 to 250 characters in length. All of the letters are automatically converted into uppercase when data is entered into a Code variable; any leading or trailing spaces are removed.
• TextBuilder: Allows for easy string manipulation...

Date/Time data

Date/Time data display is region-specific; in other words, the data is displayed according to local standards for date and time display. The following are the data types that are included in Date/Time data:

• Date: This contains an integer number, which is interpreted as a date ranging from January 1, 1754 to December 31, 9999. A 0D (numeral zero, letter D) represents an undefined date (stored as a SQL Server DateTime field), which is interpreted as January 1, 1753. According to the developer and IT pro help, Business Central supports a date of 1/1/0000 (presumably as a special case for backward compatibility, but not supported by SQL Server).

A date constant can be written as the letter D, preceded by either six digits in the format MMDDYY or eight digits as MMDDYYYY (where M for month, D for day, and Y for year). For example, 011917D or 01192017D both represent January 19, 2017. Later, in DateFormula, we will find D interpreted as day, but here, the trailing D is interpreted...

Complex data types

Each complex data type consists of multiple data elements. For ease of reference, we will categorize them into several groups of similar types.

Data structure

The following data types are in the data structure group:

• File: This refers to any standard Windows file outside the Business Central database. There is a reasonably complete set of functions to allow a range of actions, including creating, deleting, opening, closing, reading, writing, and copying data files. For example, we could create our own Business Central routines in AL to import or export data to or from a file that had been created by some other application.

With the three tier architecture of Business Central, business logic runs on the server, not the client. We need to keep this in mind whenever we are referring to local external files, because they will be on the server by default. Use of Universal Naming Convention (UNC) paths can make this easier to manage.

• Record: This refers to a single data row within a Business Central table that consists of individual fields. Quite often, multiple variable instances of a record (table) are defined in working storage...

Objects

Page, report, codeunit, query, and XMLport each represent an object data type. Object data types are used when there is a need for a reference to an object or a function in another object. Some examples of this are as follows:

• Invoking a report or an XMLport from a page or a report
• Calling a function for data validation or processing is coded as a function in a table or a codeunit

Web services

To work with web services, Business Central supports a set of HTTP, XML, and JSON variables. Documentation on these types can be found in the Microsoft Docs: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-restapi-overview.

DotNet

Business Central supports using .NET in on premises installations.

DotNet allows for the definition of a variable for .NET Framework interface types within an assembly. This supports the access of .NET Framework type members, including methods, properties, and constructors from AL. These can be members of the global assembly cache or custom assemblies.

Input/output

The following are the input/output data types:

• Dialog: This supports the definition of a simple UI window without the use of a page object. Typically, Dialog windows are used to communicate processing progress or to allow a brief user response to a go/no-go question, although the latter could result in bad performance due to locking. There are other user communication tools as well, but they do not use a Dialog type data item.
• InStream and OutStream: These allow you to read from and write to external files, BLOB data type's, and objects of the automation and DotNet data types.

DateFormula

DateFormula provides the definition and storage of a simple, but clever, set of constructs to support the calculation of runtime-sensitive dates. DateFormula is stored in a non-language dependent format, hence supporting multi-language functionality. DateFormula is a combination of the following:

• Numeric multipliers (for example, 1, 2, 3, 4, and so on)
• Alpha time units (all must be uppercase)
• D for a day
• W for a week
• WD for the day of the week, that is, day 1 through day 7 (either in the future or in the past, not today); Monday is day 1, and Sunday is day 7
• M for calendar month
• Y for year
• CM for current month, CY for current year, and CW for current week
• Math symbols interpretation: + (plus), that is, CM + 10D means the current month end plus 10 days (in other words, the 10^th of next month), and - (minus), that is, (-WD3), which means the date of the previous Wednesday (the third day of the past week)
• Positional notation (D15 means the 15^th day of the month...

References and other data types

The following data types are used for advanced functionality in Business Central, and sometimes support an interface with an external object:

• RecordID: This contains the object number and primary key of a table.
• RecordRef: This identifies a record/row in a table. RecordRef can be used to obtain information about the table, the record, the fields in the record, and the currently active filters on the table.
• FieldRef: This identifies a field in a table, hence allowing access to the contents of that field.

• KeyRef: This identifies a key in a table and the fields in that key.

Since the specific record, field, and key references are assigned at runtime, RecordRef, FieldRef, and KeyRef are used to support logic that can run on tables that aren't specified at design time. This means that one routine built on these data types can be created to perform a common function for a variety of different tables and table formats.

• Variant: This defines variables...

Data type usage

About 40% of the data types we talked about previously can be used to define the data that's stored either in tables or in working storage data definitions (that is, in a global or local data definition within an object). Two data types, BLOB and TableFilter, can only be used to define table stored data, not working storage data. About 60% of these data types can only be used for working storage data definitions.

The following list shows which data types can be used for table (persisted) data fields and which ones can be used for working storage (variable) data:

Table data types	Working storage data types
	Action
BigInteger	BigInteger BigText
BLOB
Boolean	Boolean Byte Char
Code	Code Codeunit
Date	Date
DateFormula	DateFormula
DateTime	DateTime
Decimal	Decimal Dialog DotNet
Duration	Duration
Enum	Enum ExecutionMode FieldRef File
GUID	GUID HttpClient HttpContent HttpHeaders HttpRequestMessage...

FieldClass property options

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 does and, in some instances, more. In the next chapter, we'll cover most of the field properties, but we'll discuss the FieldClass property options now.

FieldClass – Normal

When FieldClass is Normal, the field will contain the type of application data that's typically stored in a table, which is the content we would expect based on the data type and various properties.

FieldClass – FlowField

FlowFields must be dynamically calculated. FlowFields are virtual fields that are stored as metadata; they do not contain data in the conventional sense. 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 FlowField is set to No.

Depending on the CalcFormula method, this could be a value, a reference lookup, or a Boolean value. When the CalcFormula method is Sum, FieldClass connects a data field to a previously defined SumIndexField in the table defined in CalcFormula. 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...

FieldClass – FlowFilter

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 contain filters on a per user basis, with the information stored in that user's instance of the code 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.

FlowFilter allows us to provide flexible data selection functions to users. The user does not need to have a full understanding of the data structure that's used in apply filtering in intuitive ways to both the primary data table and also to subordinate data. Based on our AL code design, FlowFilters can be used to apply filtering on multiple tables, whether this be from a subordinate to a parent table. Of course...

FlowFields and FlowFilter for our application

In our application, we have decided to have several FlowFields and FlowFilters 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 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 Chapter 2, Tables, in the Creating and modifying tables section, do that now:

The following five fields will be used for statistical analysis for each radio show:

• Field 100—Average Listeners: The average number of listeners, as reported by the ratings agency
• Field 110—Audience Share: The percentage of the...

Filtering

Filtering is one of the most powerful tools within Business Central. Filtering is the application of defined limits on the data that is to be considered in a process. When we apply a filter to a Normal data field, we only view or process records where the filtered data field satisfies the limits defined by the filter. When we apply a filter to FlowField, the calculated value for that field will only consider data that satisfies the limits defined by the filter.

Filter structures can be applied in at least three different ways, depending on the design of the process:

• The first way is for the developer to fully define the filter structure and the value of the filter. This might be done in a report that's designed to show information on only a selected group of customers, such as those with an unpaid balance. The Customer table would be filtered to report only on customers who have an outstanding balance greater than zero.

• The second way is for the developer to define the...

Experimenting with filters

Now, it's time to do some experimenting with filters. We want to accomplish a couple of things through our experimentation. First, we want to get more comfortable with how filters are entered; and second, we want to see the effects of different types of filter structures and combinations. If we had a database with a large volume of data, we could also test the speed of filtering on fields in keys and on fields not in keys. However, the amount of data in the basic CRONUS database is small, so any speed differences will be difficult to see in these tests.

We could experiment on any report that allows filtering. A good report for this experimentation is the Customer/Item list. This reports which customer purchased what items. The Customer/Item list can be accessed on the role tailored client Actions menu via Reports | Customer | Customer/Item Sales.

When we initially run Customer/Item Sales, we will see just three data fields listed for the entry...

Accessing filter controls

When a page such as the customer list is opened, the filter section at the top of the page looks as follows. On the upper-left corner is a place to enter multiple-field filters. This is called Search, but the result will be a multi-column filter:

If we click on the All button with the chevron button in the upper-left corner to expand the Filter pane, the result will look similar to the following. This filter display includes an additional filtering capability, Filter list by:, which allows for the entry of filters for the filter totals by type:

If we apply a filter, the traditional filter icon will appear on the column with a filter, as shown in the following screenshot:

If we click on the filter icon, we can clear the filter. We can also select Filter to This Value, which will add a filter with the value of the currently selected row.

Summary

In this chapter, we focused on the basic building blocks of Business Central data structure: fields and their attributes. We reviewed the types of data fields, properties, and trigger elements for each type of field. We walked through a number of examples to illustrate most of these elements, though we have postponed exploring triggers until later, when we have more knowledge of AL. We covered data type and FieldClass properties, which determine what kind of data can be stored in a field.

We reviewed and experimented with the date calculation tool, which gives AL an edge in business applications. We discussed filtering, how filtering is considered as we design our database structure, and how users will access data. Finally, more of our Business Central Radio Show application was constructed.

In the next chapter, we will look at the many different types of pages in more detail. We'll put some of that knowledge to use to further expand our example Business Central application...

Questions

1. The maximum length for an AL field name or variable name is 250 characters long. True or false?
2. The table relation property defines the reference of a data field to a table. The related table data field must be one of the following:

• In any key in the related table
• Defined in the related table, but not in a key
• In the primary key in the related table
• The first field in the primary key in the related table

3. How many of the following field data types support storing application data such as names and amounts—1, 2, 3, or 4?

• FlowFilter
• Editable
• Normal
• FlowField

4. The ExtendedDataType property supports the designation of all but one of the following data types for displaying an appropriate action icon (choose the one that isn't supported):

• Email address
• Website URL
• GPS location
• Telephone number
• Masked entry

5. One of the following is not a FlowField method (choose one):

• Median
• Count
• Max
• Exist
• Average

6. It is important to have a consistent...