Within the EARP application, we need to store information that is shared across the system for all the users. These values are unique in the application, and when changed they are valid immediately for everyone inside a company. They can be held in memory, and are only acquired from the database when changed. For this requirement, we can implement the Singleton Pattern.

Technical description

The Singleton pattern contains a table object with a specific primary key structure that is enforced by the system, allowing the table object to contain only one record. Users cannot add or remove records. When a new company is created, a record in each Singleton table is created automatically, although this is optional.

To enforce the existence of a record when it is required, each Page object that is based on the table should contain business logic to create the record, in case it does not exist yet. The reasons for the record to be missing can be that the table...

For Microsoft Dynamics NAV to run properly, we also require tables that contain more records to allow multiple values to be used in various scenarios. These values are almost as static as the Singleton tables, although the new records can be created during the lifecycle of the application.

The pattern for these tables is Supplemental & Subsidiary.

Technical description

The Supplemental & Subsidiary table has a single field Primary Key that is typically called Code; it has the Data type code, and has a length of 10 characters. Values in the Primary Key are manually determined by the application administrators. Normal users of the system have read-only access to these tables.

When the attributes in the table relate to another entity, the reference to this entity can be added as a second primary key.

The referenced entity is typically of the Master Data pattern. We will discuss Master Data later in this chapter.

The other...

A Compound pattern helps us to define attributes that depend on a combination of two entities. This allows us to create additional flexibility when designing the application setup, creating values that connect through a many-to-many relationship.

Technical description

When applying this pattern, the table has two fields of type code and a length of 10 characters. Both the values should allow blank values.

The following diagram explains how to implement the Compound pattern:

To use the compound table in the application, both the fields are members of the table that uses the attributes. Typically, the compound key values are inherited from the fields in Master Data.

Implementation

The Compound table is used to define attributes for a combination of two values in the system.

Examples

The pattern is used in Microsoft Dynamics NAV for the General and VAT/TAX Posting Setup, which is as follows:

The Microsoft Dynamics NAV EARP application is designed to work for small, medium-sized, and larger businesses. The Rules Pattern enables us to store information in the database with a sliding level of complexity.

Technical description

These tables have a composite Primary Key that has up to 20 fields, limited by the SQL Server or 900 bytes. The value being used in the application is based on rules. These rules are programmed in Codeunits.

The non-Primary Key fields typically determine the values that are being used after applying the rule, although these fields can also be a part of the Primary Key. For example, the selected printer, after filtering on the criteria, is an additional field in the table.

The following diagram explains how to implement the Rules pattern:

Implementations

The Rules Pattern is used for the setup tables, and for either pricing or discount information. It is also used to implement a Select Distinct...

The EARP systems are designed to deal with people and things. These people can be customers, vendors, or employees. The things can be the G/L Accounts, Items, Fixed Assets, or Loaners.

We will refer to people as the Person Master Data, and to Things as the Product Master Data.

Technical description

The Master Data tables have a single Primary Key of the Code length as 20. This code field is typically and automatically determined using the Number Series Pattern, although this is optional. To have a more humanly readable way to find records in the table, each Master Data has two text-fields of length 50, and a code-field of length 50.

The Number Series Pattern will be discussed in Chapter 3, Design Patterns.

The following diagram explains how to implement the Master Data Pattern:

For the People Master Data, these text fields are called Name and Name 2. The code fields is named Search Name. The Product Master Data has text...

In the Financial systems, Journals are used for accounting. Microsoft Dynamics NAV has an Architectural Pattern called Journal Template-Batch-Line that ensures data integrity, and acts as a datacontract for each transaction.

Technical description

Journals are the datacontracts, employed to create entries that are used for the accounting purposes. They can be used either manually via the user interface, or automated via processes, such as documents or batch programs.

Each Journal has a Journal Line table that has a Journal Template Code, and a Journal Batch Code. The Template can be used to influence the functional user interface, and the reports are being used in the process. The Batch allows different users to use the Journal at the same time without interfering with each other, or temporarily keep data in a Journal until it is ready to be posted.

The following diagram explains how to implement the Journal Template-Batch-Line Pattern...

Most EARP systems are data cemeteries. A lot of data comes in, nothing much ever comes out. To store data in Microsoft Dynamics NAV, the Entry Pattern is most popular with over 75 implementations in the standard product.

Technical description

Although there are many variations, all entry tables have the same technical structure that can be expanded upon the functional requirements.

The Primary Key for all the entry tables is a field called Entry No. of the type Integer. The value of the field is determined using either the AutoIncrement property, or a code algorithm as the following:

IF NextEntryNo = 0 THEN BEGIN

  ExLedgEntry.LOCKTABLE;

  IF ExLedgEntry.FINDLAST THEN

    NextEntryNo := ExLedgEntry."Entry No.";

    NextEntryNo := NextEntryNo + 1;

  END;

WITH ExLedgEntry DO

  "Entry No." := NextEntryNo;

The following diagram explains how to implement the Entry Pattern:

Entry tables contain...

The EARP systems are traditionally designed to help companies administer the paper trail, required to run their businesses, often from a legal perspective. To be able to register the paper documents in an electronic form, Document Pattern can be used.

Technical description

The Document pattern allows us to enter a header and line information. The header information is often related to Person Master Data (with whom I am doing business), whereas the lines are of the type Product (the kind of business that I am doing).

Each Document Pattern has two tables: a header and line. These names are important, the Sales Header and the Sales Line tables follow this convention, just as others.

The Header table

The header table has a primary key that is called No., and has a type Code with a length of 20 characters. Optionally, the primary key can also contain a reference to a normalized document type. Normalized in this case means that the share tables and business...

The Argument table has an ambiguous place in the categorization of patterns. The table is used to combine fields when we call a function. This can be compared to a class in object-oriented programming. Since Microsoft Dynamics NAV does not allow us to create a class, we use the table object instead. This will automatically create a table in the SQL Server, but this table will never contain any data. However, some tables are used for both arguments and normal data.

In computer programming, the general rule of thumb is to avoid functions that have too many arguments in the signature. When a function has more than three or four arguments, we should combine them into an argument table.

Technical description

The definition of the table should contain all the fields used in the arguments. The primary key of the table is not important, since the table will never contain actual data. However, the metadata in Microsoft Dynamics NAV requires at least one field as...

In this chapter, we have learned what Architectural Patterns are, and how to implement them in Microsoft Dynamics NAV. We have covered nine patterns that cover more than 95 percent of the tables that are defined with the standard application.

Using Architectural Patterns helps us to define tables when building our applications that others will recognize. This creates a common syntax within the community, and reduces the learning curve when bringing in new developers and architects.

In the next chapter, we will implement Design Patterns that will add elements to Architectural Patterns that define the usability of the application.