Ir al contenido

Native API Creation (Query)

Development and metadata structuring guide for publishing high-performance RESTful/OData V4 endpoints in AL.

0views

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).

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';
}
  • 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).

  • EntityName and EntitySetName: 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.

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) { }
}
}

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.

  • ✓ Routing: Explicit definition of APIPublisher, APIGroup, and APIVersion without 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.