Ir al contenido

Extending Tables (TableExtension)

Creating database-level extensions to inject new physical fields and modify ERP behavior.

0views

The principle of modern development in Cloud ecosystems is non-intrusion. We do not have access to Microsoft's original C/AL code, nor do we want it (as it would break automatic updates).

To alter the database, we use the tableextension object. At the SQL engine level, what Business Central does behind the scenes is create a new supplementary table with our prefix, and the system automatically orchestrates a transparent JOIN (1-to-1) every time a record is read or written.

Before programming, we must know the security and design guidelines:

  • Development IDs: We must use the ranges assigned to our customer license (typically from 50000 to 99999).
  • What we CAN do: Add new fields, add new keys (keys), modify certain standard field properties (such as Caption).
  • What we CANNOT do: Delete standard fields, change the data type of a standard field, or delete standard primary keys.

Let's assume a real-world scenario from our environment: The purchasing department requires analyzing metrics based on the "Final Supplier" of the goods, but the standard reception table only records the logistics distributor.

We are going to inject the field into table 121 (Purch. Rcpt. Line).

File name: Tab-Ext50100.PurchRcptLineExt.al

// 1. Declaración: ID libre, Nombre de la extensión, y la tabla base que extendemos.
tableextension 50100 "Portelcore PurchRcptLine Ext" extends "Purch. Rcpt. Line"
{
fields
{
// 2. Creación del nuevo campo físico. ID libre y Tipo de dato (Texto de 100 caracteres).
field(50100; "Proveedor Final"; Text[100])
{
// PROPIEDADES DEL CAMPO
// Etiqueta visual que verán los usuarios y sistemas externos
Caption = 'Proveedor Final';
// DataClassification (GDPR): Obligatorio declarar qué tipo de información guardamos.
// Posibles valores: CustomerContent, EndUserIdentifiableInformation, SystemMetadata, etc.
DataClassification = CustomerContent;
// Descripción técnica para otros desarrolladores
Description = 'Registra el fabricante real de la materia prima, no el distribuidor intermediario.';
// LÓGICA DE NEGOCIO Y EVENTOS
trigger OnValidate()
begin
// Se ejecuta cada vez que el valor cambia y es aceptado.
// Aquí podríamos llamar a funciones de validación compleja o cruzar con otras tablas.
if Rec."Proveedor Final" = '' then
Message('Aviso DevSecOps: Se recomienda encarecidamente registrar el proveedor final para Power BI.');
end;
}
}
// 3. OPTIMIZACIÓN (Índices SQL)
keys
{
// Si prevemos que Power BI o el ERP filtrarán masivamente por este campo,
// debemos crear una clave secundaria (Non-Clustered Index) en SQL.
key(Key_ProvFinal; "Proveedor Final")
{
}
}
}

3. The Immediate Effect (And what is missing)

Sección titulada «3. El Efecto Inmediato (Y lo que falta)»

The moment we save this code and deploy to the Sandbox (by pressing F5 or Ctrl+F5 in VS Code), the SQL Server engine alters the physical schema.

From that second onward:

  • The field "Proveedor Final" exists.
  • It occupies disk space.
  • It can be immediately consumed by our APIs (Objects Query).
  • It can be used by any other code logic extension (Codeunits).

⚠️ The Clandestine Data: However, workers using the Business Central web interface CANNOT see or edit it. Physically it is there, but visually it is not. To expose it to users, we must create a visual interface extension (pageextension).

  • ✓ SQL Schema: Successful schema update in Azure SQL/SQL Server via automatic NST synchronization.
  • ✓ GDPR Compliance: Explicit DataClassification data classification in all new fields.
  • ✓ Performance: Creation of secondary keys (keys) for indexed fields for mass extraction in ETL/Power BI.>