Extending Tables (TableExtension)
Creating database-level extensions to inject new physical fields and modify ERP behavior.
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.
1. Object Rules and Limitations
Sección titulada «1. Reglas y Limitaciones del Objeto»Before programming, we must know the security and design guidelines:
- Development IDs: We must use the ranges assigned to our customer license (typically from
50000to99999). - What we CAN do: Add new fields, add new keys (
keys), modify certain standard field properties (such asCaption). - What we CANNOT do: Delete standard fields, change the data type of a standard field, or delete standard primary keys.
2. Development: Structure of a tableextension
Sección titulada «2. Desarrollo: Estructura de un tableextension»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).
4. Validation and Deployment Checklist
Sección titulada «4. Checklist de Validación y Despliegue»- ✓ SQL Schema: Successful schema update in Azure SQL/SQL Server via automatic NST synchronization.
- ✓ GDPR Compliance: Explicit
DataClassificationdata classification in all new fields. - ✓ Performance: Creation of secondary keys (
keys) for indexed fields for mass extraction in ETL/Power BI.>

