Ir al contenido

Base Architecture - Tables vs. Pages

Exhaustive analysis of the MVC pattern in Business Central, differentiating the physical model from the visual layer and its impact on data.

0views

To work with the ERP and create efficient data flows, we must intimately understand the strict separation Microsoft establishes between data storage (Backend) and its presentation (Frontend).

Business Central operates under a model very similar to the MVC (Model-View-Controller) pattern. In our terminology, we will talk about the Brain (Tables) and the Windows (Pages).

Tables represent the physical and real structure of the database (Azure SQL). It is the only place where information persists. If a piece of data does not exist in a Table, it simply does not exist in the system.

In AL, a table is not just a set of columns; it contains a lot of embedded business logic:

  • Fields (fields): Define the strong data type (Text, Code, Decimal, Date, Boolean).
  • Primary Keys and Indices (keys): Optimize SQL searches. The first key (Key) defined in a table is automatically the Primary Key (Clustered Index).
  • Triggers (triggers): Logic that executes automatically at the database level (OnInsert, OnModify, OnDelete, OnRename).
  • Field Validations (OnValidate): Logic that executes when a specific field changes value. For example, when changing the “Customer Code”, the system automatically looks up its name and fills it in.

1.2. The concept of “FlowFields” (Calculated Fields)

Sección titulada «1.2. El concepto de “FlowFields” (Campos Calculados)»

This is a critical concept for Data Engineering. Some fields in tables DO NOT store actual data. They are dynamic SQL formulas (FlowFields).

  • Example: The Customer “Balance” field. It does not exist in the database as a fixed number. It is a Sum of all their accounting movements.
  • Impact: FlowFields are lethal to performance if extracted massively without precautions.

Pages make up the User Interface (UI) layer. They do not store data; they “peek” into a specific table, read it, and render dynamic HTML in the user's browser.

  • Layout (Visual design): Defines the order in which columns are displayed, whether they are grouped into tabs (FastTabs), and whether they are editable or read-only.
  • Actions (Buttons): The top ribbon that allows executing processes (e.g., “Register Delivery Note”, “Print PDF”).
  • Visual Triggers: Frontend actions (OnOpenPage, OnAfterGetRecord) that alter colors, hide fields, or provide pop-up messages.

A golden rule in our architecture is that a single Table can have infinite Pages pointing to it.

  • The Source (Table): Table 121 (Purch. Rcpt. Line - Purchase Delivery Note Lines).
  • Window 1 (Standard): Page 528. Used by the Administration department, it shows 40 columns including G/L accounts and dimensions.
  • Window 2 (Custom): Page 50017. Custom-built for Warehouse staff, showing only 4 columns (Item, Quantity, Warehouse, and Vendor).

Both read and write to the same physical location. Modifying a row in Window 2 instantly updates what is seen in Window 1.

Here we can see the direct contrast in AL code between the physical definition of a data model (table) and its visual representation in a list (page):

// Definición física en Azure SQL (Model)
table 50100 "Portelcore Custom Table"
{
Caption = 'Tabla Personalizada Portelcore';
DataClassification = CustomerContent;
fields
{
field(1; "Id Registro"; Code[20])
{
Caption = 'Id Registro';
}
field(2; Descripcion; Text[100])
{
Caption = 'Descripción';
}
}
keys
{
key(PK; "Id Registro")
{
Clustered = true;
}
}
}
// Representación visual en la UI (View)
page 50100 "Portelcore Custom Page"
{
PageType = List;
SourceTable = "Portelcore Custom Table";
Caption = 'Lista Personalizada Portelcore';
ApplicationArea = All;
UsageCategory = Lists;
layout
{
area(content)
{
repeater(Group)
{
field("Id Registro"; Rec."Id Registro")
{
ApplicationArea = All;
ToolTip = 'Identificador único del registro.';
}
field(Descripcion; Rec.Descripcion)
{
ApplicationArea = All;
ToolTip = 'Descripción detallada.';
}
}
}
}
}

Understanding this separation dictates how we must structure our developments:

  1. Data Extraction Rule (ETL): When building APIs for Power BI, we NEVER expose a Page. Pages force the server to execute visual triggers and calculate unnecessary FlowFields, saturating the execution thread and causing the classic “Timeout” error. We must always point directly to Tables using Query API objects.
  2. ERP Expansion Rule: If the business asks us for a new information point (e.g., adding a “Final Provider” field), our workflow will obligatorily consist of two separate steps:
    • Expand the Brain (via tableextension).
    • Opening a gap in the Windows (via pageextension).
  • ✓ Strict Separation: Business logic encapsulated in Table and Codeunit objects; interface confined to Page objects.
  • ✓ ETL Performance: Exclusive exposure of API-type Query objects for extractions to Power BI, avoiding the penalties of page FlowFields.
  • ✓ Extensibility: Modification of the standard Microsoft model through decoupled extensions (tableextension and pageextension).