Tables

"If you don't know where you are going, you might wind up someplace else."

– Yogi Berra

Successful design is anticipating the anticipations of others.

– John Maynard Keynes, paraphrased

The foundation of any system is the data structure definition. In Business Central, the building blocks of this foundation are the tables and the individual data fields that the tables contain. Once the functional analysis and process definition have been completed, any new design work must begin with the data structure. For Business Central, that means the tables and their contents.

A Business Central table includes much more than just the data fields and keys. A Business Central table definition also includes data validation rules, processing rules, business rules, and logic to ensure referential integrity. These rules are in the form of properties and AL code...

An overview of tables

There is a distinction between the table (data definition and data container) and the data (the contents of a table). The table definition describes the identification information, data structure, validation rules, storage, and retrieval of the data that is stored in the table (container). This definition is defined by its design and can only be changed by a developer. The data is the variable content that originates from user activities. The place where we can see the data being explicitly referenced, independent of the table as a definition of structure, is in the permissions setup data. In the following screenshot, the data is formally referred to as Table Data:

The table is not the data—it is the definition of data contained in the table. Even so, we commonly refer to both the data and the table as if they were one and the same. That is what we will do in this book.

All permanent data must be stored in a table. All tables are defined by the developer...

Components of a table

A table is made up of Fields, Properties, Triggers (some of which may contain AL code), and Keys. Fields also have Properties and Triggers. Keys also have Properties:

A table definition that takes full advantage of these capabilities reduces the effort that's required to construct other parts of the application. Good table design can significantly enhance the application's processing speed, efficiency, and flexibility.

A table can have the following components:

• Up to 500 fields
• A defined record size of up to 8,000 bytes (with each field sized at its maximum)
• Up to 40 different keys

Naming tables

There are standardized naming conventions for Business Central that we should follow. Names for tables and other objects should be as descriptive as possible, while kept to a reasonable length. This makes our work more self-documenting.

Table names should always be singular. The table containing data about customers should not be named Customers, but Customer. The table we created for our WDTU Radio Station Business Central enhancement was named Radio Show, even though it will contain data for all of WDTU's radio shows.

In general, we should always name a table so that it is easy to identify the relationship between the table and the data it contains. For example, two tables containing the transactions on which a document page is based should normally be referred to as a Header table (for the main portion of the page) and a Line table (for the line detail portion of the page). As an example, the tables underlying a Sales Order page are the Sales Header and the Sales...

Table numbering

There are no hard and fast rules for table numbering, except that we must only use the table object numbers that we are licensed to use. If all we have is the basic table designer rights, we are generally allowed to create tables numbered from 50,000 to 50,009 (unless our license was defined differently compared to the typical one). If we need more table objects, we can purchase licensing for table objects numbered up to 99,999. Independent Software Vendors (ISVs) can purchase access to tables in other number ranges.

When creating several related tables, ideally, we should assign them to numbers in sequential order. We should let common sense be our guide for assigning table numbers. It requires considerable effort to renumber tables containing data.

Table properties

The first step in studying the internal construction of a table is to open one in Visual Studio Code. We will use the Radio Show table we created in Chapter 1, Introduction to Business Central, to show you this.

We now have the Radio Show table open in Visual Studio Code, as shown in the following screenshot:

We can access the properties of a table. Place the cursor on the empty line above the fields (as shown in the preceding screenshot) and click on Ctrl + spacebar. If we hit Ctrl + spacebar while the focus is on a filled field line, we will see the properties of that field (not the table).

The options that you can see in Visual Studio Code should look as follows:

The table properties are as follows:

• Caption: This contains the caption that was defined for the currently selected language. The default language for Business Central is English, US (ENU).
• CaptionML: This defines the MultiLanguage caption for the table. For an extended discussion on the language capabilities...

Table triggers

To display the triggers with the table open in Visual Studio Code, enter the trigger keyword in the first line after the fields section, followed by Ctrl + spacebar. This is shown in the following screenshot:

The code contained in trigger is executed prior to the event represented by trigger. In other words, the code in the OnInsert() trigger is executed before the record is inserted into the table. This allows the developer a final opportunity to perform validations and to enforce data consistency such as referential integrity. We can even abort the intended action if data inconsistencies or conflicts are found.

These triggers are automatically invoked when record processing occurs as the result of user action. But when table data is changed by AL code or by a data import, the AL code or import process determines whether or not the code in the applicable trigger is executed:

• OnInsert(): This is executed when a new record is to be inserted in...

Keys

Table keys are used to identify records and to speed up filtering and sorting. Having too few keys may result in painfully slow inquiries and reports. However, each key incurs a processing cost, because the index containing the key must be updated every time information in a key field changes. Key cost is measured primarily in terms of increased index maintenance processing. There is also additional cost in terms of disk storage space (usually not significant) and additional backup/recovery time for the increased database size (sometimes very important).

When a system is optimized for processing speed, it is critical to analyze the SQL Server indexes that are active because that is where the updating and retrieval time are determined. The determination of the proper number and design of keys and indexes for a table requires a thorough understanding of the types and frequencies of inquiries, reports, and other processing for that table.

Every Business Central table must have at least...

SumIndexFields

Dynamics Business Central, which is inherited from Dynamics NAV (formerly Navision), has a unique capability called the SIFT feature. These fields serve as the basis for FlowFields (automatically accumulating totals) and are unique to Business Central. This feature allows Business Central to provide almost instantaneous responses to user inquiries for summed data, which are calculated on the fly at runtime and are related to the SumIndexFields. The cost is primarily that of the time required to maintain the SIFT indexes when a table is updated.

Business Central maintains SIFT totals using SQL Server indexed views. An indexed view is a view that has been preprocessed and stored. Business Central creates one indexed view for each enabled SIFT key. SIFT keys are enabled and disabled through the MaintainSIFTIndex property. SQL Server maintains the contents of the view when any changes are made to the base table, unless the MaintainSIFTIndex property is set to No.

...

Field groups

When a user starts to enter data in a field where the choices are constrained to existing data (for example, an Item No., a Salesperson Code, a Unit of Measure code, a Customer No., and so on), good design dictates that the system will help the user by displaying the universe of acceptable choices. Put simply, a lookup list of choices should be displayed.

In the Role Tailored Client (RTC), the lookup display (a drop-down control) is generated dynamically when its display is requested by the user's effort to enter data in a field that references a table through the TableRelation property (which will be discussed in more detail in the next chapter). The format of the drop-down control is a basic list. The fields that are included in that list and their left to right display sequence are either defined by default or by an entry in the FieldGroups table.

The fieldgroups table is part of the Business Central table definition, much like the list of keys. In fact, the ...

Bricks

In addition to the DropDown field group, we can also define a brick. Bricks are used differently in the web client and phone client. The brick for the Customers table is rendered as follows:

The same brick is rendered differently in the web client, as displayed in the following screenshot:

Enhancing our sample application

Now, we can take our knowledge of tables and expand our WDTU application. Our base Radio Show table needs to be added to and modified. We also need to create and reference additional tables.

Although we want to have a realistic design in our sample application, we will focus on changes that illustrate features in Business Central table design that the authors feel are among the most important. If there are capabilities or functionalities that you feel are missing, feel free to add them. Adjust the examples as much as you wish to make them more meaningful to you.

Creating and modifying tables

In Chapter 1, Introduction to Business Central, we created the Radio Show table for the WDTU application. At that time, we used the minimum fields that allowed us to usefully define a master record. Now, let's set properties on existing data fields, add more data fields, and create an additional data table to which the Radio Show table can refer.

Our new data fields are shown in the following layout table:

Field no.	Field name	Description
1000	Frequency	An Option data type (Hourly, Daily, Weekly, or Monthly) is used for the frequency of a show; Hourly should be used for a show segment such as news, sports, or weather that is scheduled every hour. A space/blank is used for the first option to allow a valid blank field value.
1010	PSA Planned Quantity	This is the number (stored as Integer) of Public Service Announcements (PSAs) to be played per show; this will be used by playlist generation and posting logic.
...

Assigning a table relation property

Finally, we will open the Radio Show table again. This time, add the TableRelation property between the curly braces of the Type field. Connect it to our new Radio Show Type table:

To check that the TableRelation property is working properly, we can publish and run the Radio Show List page again. We should highlight the RADIO SHOW TYPE field and click on the drop-down arrow to view the list of available entries. The following screenshot is of our Radio Show List page:

If all has gone according to plan, the RADIO SHOW TYPE field will display an assist edit image (the three dots). We will invoke the drop-down list for the Radio Show Type table, as shown in the preceding screenshot.

Assigning an InitValue property

Another property we can define for several of the Radio Show fields is InitValue. WDTU has a standard policy that states that news, sports, and weather should be broadcast for a few minutes at the beginning of every hour in the day. We want the Boolean (true/false) fields, News Required, Sports Required, and Weather Required, to default to true.

Setting the default for a field to a specific value simply requires setting the InitValue property to the desired value. In the case of the required Boolean fields, that value is set to true:

Adding a few activity-tracking tables

Our WDTU organization is a profitable and productive radio station. We track historical information about our advertisers, royalties owed, and listenership. We track the music that is played, the rates we charge for advertisements based on the time of day, and we provide a public service by broadcasting a variety of government and other public service announcements.

We aren't going to cover all of these features and functions in the following detailed exercises. However, it's always good to have a complete view of the system that we are working on, even if we are only working on one or two components. In this case, the parts of the system that aren't covered in detail in our exercises will be opportunities for you to extend your studies and practice on your own.

Any system development should start with a design document that completely spells out the goals and the functional design details. Neither system design nor project management...

New tables for our WDTU project

First, we need to create a Playlist Header table (table number 50102), which will contain one record for each scheduled Radio Show:

Then, we will create the associated Playlist Line table (table number 50103). This table will contain child records for the Playlist Header table. Each Playlist Line record represents one scheduled piece of music, advertisement, public service announcement, or embedded show within scheduled Radio Show that's defined in the Playlist Header table. Description for each of the option fields shows the information that needs to be entered into the OptionMembers property for those fields:

Now, we'll create our Playlist Item Rate table. These rates include both what we charge for advertising time and what we must pay in royalties for material we broadcast:

An entry table contains the detailed history of processed activity records. In this case, the data is a detailed history of all of the Playlist Line records for...

New list pages for our WDTU project

Each of the new tables we have created should be supported with an appropriately named list page. As part of our WDTU project work, we should create the following pages:

• 50103: Playlist document list
• 50105: Playlist item rates
• 50106: Radio show entries
• 50107: Listenership entries
• 50108: Publishers

Keys, SumIndexFields, and table relations in our examples

So far, we have created basic table definitions and associated pages for the WDTU project. The next step is to flesh out those definitions with additional keys, SIFT field definitions, table relations, and so on. The purpose of these are to make our data easier and faster to access, to take advantage of the special features of Business Central to create data totals, and to facilitate relationships between various data elements.

Secondary keys and SumIndexFields

The Playlist Line table's default primary key was Document No.. For the primary key to be unique for each record, another field is needed. For a Line table, the additional field is the Line No. field, which is incremented via AL code for each record. So, we'll change the key for table 50003 accordingly:

We know that a lot of reporting will be done based on the data in Radio Show Ledger. We also know that we want to do reporting on data by Radio Show and by the type of entry (individual song, specific advertisement, and so on). So, we will add secondary keys for each of those, including a Date field so that we can rapidly filter the data by Date. The reporting that is financial in nature will need totals of the Fee Amount field, so we'll put that in the SumIndexFields column for our new keys:

We know that to do the necessary listenership analysis, the listenership ledger needs an additional key, combined with SumIndexFields for...

Table relations

For the tables where we defined fields that are intended to refer to data in other tables for lookups and validation, we must define their relationships in the referring tables. Sometimes, these relationships are complicated and are dependent on other values within the record.

In table 50103, Playlist Line, we have the No. field. If the Type field contains Resource, then the No. field should contain Resource No.. If the Type field contains Show, then the No. field should contain Radio Show No.. Finally, if the Type field contains Item, the No. field should contain Item No.. The pseudocode (approximate syntax) for that logic can be written as follows:

IF Type = 'Resource' THEN No. := Resource.No. ELSE IF Type = 'Show' THEN No. := Radio Show.No. ELSE IF Type = 'Item' THEN No. := Item.No. 

The syntax to put in Visual Studio Code is displayed in the following screenshot:

Table 50004, Playlist Item Rate, has a similar TableRelation...

Modifying an original Business Central table

One of the big advantages of the Business Central system development environment is the fact that we are allowed to enhance the tables that are part of the original standard product. Many package software products do not provide this flexibility. Nevertheless, with privilege comes responsibility. When we modify a standard Business Central table, we must do so carefully.

In our system, we are going to use the standard Item table—table 27—to store data about recordings such as music and advertisements and PSAs that we have available for broadcast. To do this, we will create a table extension, as described in Chapter 1, Introduction to Business Central. One of the new fields will be an Option field. Another will refer to the Publisher table we created earlier. When the modifications for the Item table design are completed, they will look as follows:

Types of table

For this discussion, we will divide table types into three categories. As developers, we can change the definition and the contents of the first category (the fully modifiable tables). We cannot change the definition of the base fields of the second category, but we can change the contents (the content modifiable tables) and add new fields. The third category can be accessed for information, but neither the definition nor the data within is modifiable (the read-only tables).

Fully modifiable tables

The following tables are included in the fully modifiable tables category.

Master data

The master data table type contains primary data (such as customers, vendors, items, and employees). In any enhancement project, these are the tables that should be designed first because everything else will be based on these tables. When working on a modification, necessary changes to master data tables should be defined first. Master data tables always use card pages as their primary user input method. The Customers page is a master data table. A customer record is shown in the following screenshot:

The preceding screenshot shows how the card page segregates the data into categories on different FastTabs (such as General, Address & Contact, and Invoicing), and includes primary data fields (for example, Name and Address) and a FlowField (Balance (LCY)).

Journal

The journal table type contains unposted activity detail, which is data that other systems refer to as transactions. Journals are where the most repetitive data entry occurs in Business Central. In the standard system, all journal tables are matched with corresponding template tables (one template table for each journal table). The standard system includes journals for Sales, Cash Receipts, General Journal entries, Physical Inventory, Purchases, Fixed Assets, and Warehouse Activity, among others.

The transactions in a journal can be segregated into batches for entry, edit review, and processing purposes. Journal tables always use worksheet pages as their primary user input method. The following screenshots show two journal entry screens. They both use the General Journals table, but each have quite a different appearance and are based on different pages and different templates:

By comparing the preceding and following screenshots, we can see that the differences include not only...

Template

The template table type operates behind the scenes, providing control information for a journal, which operates in the foreground. By using a template, multiple instances of a journal can each be tailored for different purposes. Control information that's contained in a template includes the following:

• The default type of accounts to be updated (for example, Customer, Vendor, Bank, and General Ledger)
• The specific account numbers to be used as defaults, including balancing accounts
• What transaction numbering series will be used
• The default encoding to be applied to transactions for the journal (for example, Source Code and Reason Code)
• Specific pages and reports to be used for data entry and processing of both edits and posting runs

For example, General Journal Templates allow the General Journal table to be tailored so that it can display fields and perform validations that are specific to the entry of particular transaction categories, such as Cash Receipts, Payments...

Entry tables

The entry table type contains posted activity detail, which is the data other systems call history. Business Central data flows from a journal through a posting routine into an entry. A significant advantage of Business Central entry design is the fact that Business Central allows you to retain all transaction details indefinitely. While there are routines that support the compression of the entry data (if that's even feasible), we should retain the full historical detail of all activities. This allows users to have total flexibility for historical comparative or trend data analysis.

Entry data is considered accounting data in Business Central. We are not allowed to directly enter the data into an entry or change existing data in an entry; instead, we must post to an entry. Posting is done by creating journal lines, validating the data as necessary, then posting those journal lines into the appropriate ledgers. Although we can physically force data into an entry with...

Subsidiary (supplementary) tables

The subsidiary (also called supplementary) table type contains lists of codes, descriptions, or other validation data. Subsidiary table examples are postal zone codes, country codes, currency codes, currency exchange rates, and so on. Subsidiary tables are often accessed by means of one of the setup menu options because they must be set up prior to being used for reference purposes by other tables. In our WDTU example, tables 50001 Radio Show Type and 50007 Publisher are subsidiary tables.

The following screenshots show some sample subsidiary tables for location, country/region, and payment terms. Each table contains data elements that are appropriate for use as a subsidiary table, plus, in some cases, fields that control the effect of referencing a particular entry. These data elements are usually entered as part of a setup process and then updated over time as appropriate:

The Location List in the preceding screenshot is a simple validation list of...

Register

The register table type contains a record of the range of transaction ID numbers for each batch of posted Ledger Entries. Register data provides an audit trail of the physical timing and sequence of postings. This, combined with the full details that are retained in the Ledger Entry, makes Business Central a very auditable system, because we can see exactly what activity was done and when it was done:

The user views the register through a tabular page, as shown in the previous screenshot. We can see that each register entry has the CREATION DATE, SOURCE CODE, JOURNAL BATCH NAME, and the identifying entry number range for all of the entries in that batch. Another Business Central feature, the NAVIGATE function, which we will discuss in detail in Chapter 4, Pages – The Interactive Interface, also provides a very useful auditing tool. The NAVIGATE function allows the user (who may be a developer doing testing) to highlight a single Ledger Entry and find all of the other...

Posted document

The posted document type contains the history version of the original documents for a variety of data types, such as Sales Invoices, Purchase Invoices, Sales Shipments, and Purchase Receipts. Posted documents are designed to provide an easy reference to the historical data in a format similar to what would have been stored in paper files. A posted document looks very similar to the original source document. For example, a Posted Sales Invoice will look very similar to the original Sales Orders or Sales Invoices. The posted documents are included in the NAVIGATE function.

The following screenshots show a Sales Orders before posting and the resulting Posted Sales Invoice document. Both documents are in a header/detail format, where the information in the header applies to the whole order and the information in the detail is specific to the individual order line. As part of the Sales Orders page, there is information being displayed to the right of the actual order. This...

Singleton

The singleton table type contains system or functional application control information, often referred to as setup. There is one setup table per functional application area, for example, one for Sales & Receivables, one for Purchases & Payables, one for General Ledger, and one for Inventory. Setup tables contain only a single record. Since a setup table has only one record, it can have a primary key field that has no value assigned to it (this is how all of the standard Business Central setup tables are designed). The singleton table design pattern can be found at http://blogs.msdn.com/b/nav/archive/2013/07/19/nav-design-pattern-of-the-week-single-record-setup-table.aspx.

The Inventory Setup page is as follows:

Temporary

A temporary table is used within objects to hold temporary data. A temporary table does not exist outside the instance of the object where it is defined using a permanent table as the source of the table definition. A temporary table has exactly the same data structure as the permanent table after which it is modeled.

Temporary tables are created empty when the parent object execution initiates, and they disappear along with their data when the parent object execution terminates (that is, when the temporary table variable goes out of scope).

Temporary tables are not generally accessible to users, except on a display-only basis. They can directly be the target of reports, pages, and XML ports. In general, temporary tables are intended to be work areas, and as such, are containers of data. The definition of a temporary table can only be changed by changing the definition of the permanent table on which it has been modeled.

A temporary table technique is used by advanced developers...

Content modifiable tables

The content modifiable table category includes only one table type, the system table type.

System table

The system table type contains user-maintainable information that pertains to the management or administration of the Business Central application system. System tables are created by Business Central; we cannot create system tables. However, with full developer license rights, we can modify system tables to extend their usage. With full system permissions, we can also change the data in system tables.

An example of a system table type is the User table, which contains user login information. This particular system table is often modified to define special user access routing or processing limitations. Other system tables contain data on report-to-printer routing assignments, transaction numbers to be assigned, batch job scheduling, and so on. The following are examples of system tables in which definition and content can be modified. The first three relate to system security functions:

• User: The table of identified users and their security information
• Permission Set: The...

Read-only tables

There is only one table type included in the read-only table category, the virtual table type.

Virtual

The virtual table type is computed at runtime by the system. A virtual table contains data and is accessed like other tables, but we cannot modify either the definition or the contents of a virtual table. We can think of virtual tables as system data that's presented in the form of a table so that it is readily available to AL code. Some of these tables (such as the Database File, File, and Drive tables) provide access to information about the computing environment. Other virtual tables (such as the Table Information, Field, and Session tables) provide information about the internal structure and operating activities of our database. A good way to learn more about any of these tables is to create a list or card page that's bound to the table of interest. Include all of the fields in the page layout, Save the page, and Run it. We can then view the field definition and data contents of the target virtual table.

Some virtual tables (such as Date and Integer) provide...

Summary

In this chapter, we focused on the foundation level of Business Central data structure: tables and their internal structure. We worked our way through the hands-on creation of a number of tables and their data definitions in support of our WDTU application. We briefly discussed field groups and how they are used.

We identified essential table structure elements, including properties, object numbers, triggers, keys, and SumIndexFields. Finally, we reviewed the various categories of tables that can be found in Business Central.

In the next chapter, we will dig deeper into the Business Central data structure to understand how fields and their attributes are assembled to make up tables. We will also focus on what can be done with triggers. Then, we will explore how other object types use tables so that we can work toward developing a fully-featured Business Central development toolkit.

Questions

1. Which of the following is a correct description of a table in Business Central? Choose two:
   • A Business Central table is the definition of data structure
   • A Business Central table includes a built-in data entry page
   • A Business Central table can contain AL code, but that should be avoided
   • A Business Central table should implement many of the business rules of a system

2. All primary keys should contain only one data field. True or false?
3. It is possible to link a Business Central table to a table outside of the Business Central database using what property? Choose one:
   • DatabaseLink
   • ObjectPointer
   • LinkedObject
   • C# Codelet

4. System tables cannot be modified. True or false?
5. Which of the following are table triggers? Choose two:
   • OnInsert
   • OnChange
   • OnNewKey
   • OnRename

6. Keys can be enabled or disabled in executable code. True or false?
7. Because setup tables only contain one record, they do not need to have a primary key. True or false?
8. Table numbers that are intended to be used for...