Native API Creation (Query)
Development and metadata structuring guide for publishing high-performance RESTful/OData V4 endpoints in AL.
To build our optimized data architecture (ELT), we develop our own APIs in AL language using the Query object.
Unlike traditional APIs where we program explicit routes and controllers, in AL we define a declarative structure that the Business Central engine automatically exposes as a RESTful endpoint (OData V4).
1. Header Structure and Metadata
Sección titulada «1. Estructura y Metadatos de Cabecera»Every query object of API type must begin with a very strict header that defines its routing and categorization.
query 50100 "Movs_Productos_API"{ // 1. Declaración obligatoria para exponerlo como Endpoint QueryType = API;
// 2. Enrutamiento de la URL OData APIPublisher = 'portelcore'; APIGroup = 'api_portelcore_1'; APIVersion = 'v1.0';
// 3. Nomenclatura del recurso JSON EntityName = 'movimiento'; EntitySetName = 'movimientos';
// 4. Integración con Power BI / Excel QueryCategory = 'api_portelcore_1';}Analysis of Critical Parameters
Sección titulada «Análisis de los Parámetros Críticos»-
QueryType = API;: This line is what converts the SQL query into an HTTP Web Service accessible from the outside. -
Routing (The Microsoft Restrictive Pattern): The final URL we will consume always follows this immutable pattern:
.../api/{APIPublisher}/{APIGroup}/{APIVersion}/companies({id})/{EntitySetName}DevSecOps Note: Microsoft prohibits the use of forward slashes
/to attempt to create logical “subfolders”. Therefore, if we want hierarchy, we use naming conventions with underscores (api_portelcore_1). -
EntityNameandEntitySetName: They define how objects are named in the resulting JSON.EntitySetName(plural) will be the final table name in the OData URL. -
QueryCategory: By assigning this value, we allow the native Business Central connector in tools like Excel, Power Automate, or Power BI to graphically locate our API within the “Advanced APIs” folder.
2. The Query Body (elements)
Sección titulada «2. El Cuerpo de la Consulta (elements)»The elements section of the query is equivalent to our SELECT and FROM SQL clauses. Instead of declaring generic columns, we only “project” those that provide analytical value.
elements { // Esto equivale al FROM "Item Ledger Entry" dataitem(itemLedgerEntry; "Item Ledger Entry") { // Las columnas proyectadas (El SELECT) column(Tipo_Movimiento; "Entry Type") { } column(Num_Documento; "Document No.") { } column(Fecha_Registro; "Posting Date") { } column(Cantidad; Quantity) { }
// Opcional: El SystemId es vital si el destino requiere una clave única o UUID column(systemId; SystemId) { } } }Architectural Advantage (Column Renaming)
Sección titulada «Ventaja Arquitectónica (Column Renaming)»As seen in the code, on the left side (column(MiNombre; "Nombre Original")), we can rename the columns.
This allows us to apply Data Governance principles from the source. Navision databases have names with spaces, dots, and strange notations (Shortcut Dimension 1 Code). By rewriting them as Cod_Dimension_1, we save transformation steps in Power Query and deliver a much cleaner analytical model to the Business Intelligence team.
3. API Query Validation Checklist
Sección titulada «3. Checklist de Validación de API Query»- ✓ Routing: Explicit definition of
APIPublisher,APIGroup, andAPIVersionwithout forward slashes/to avoid compilation errors. - ✓ Governance: Columns properly renamed at the source using clean Snake_Case notation to facilitate modeling.
- ✓ Optimization: Inclusion of
SystemId(UUID) to serve as a unique primary key in the destination storage.

