Uncover AL Page Extension and Report Extension translation to SQL statements

April 3, 2023

Share This Post

Earlier in February 2023, I blogged about how AL compiler talks to SQL Server. Please visit my earlier blog here. I explained how basic AL language statements like SetLoadFields(), IsEmpty() or “Data Transfer” data type methods get mapped to sql statements.

Today I am going to look at how page extensions and report extensions in AL get translated into SQL statements.

Let’s start with page extensions.

Page Extensions -> SQL statement

You can check my video:

Page Extensions to SQL statements

While the statement is large, the core of it can be summarized through a “join” AL builds and sends to the service instance for SQL execution:

SELECT * 
FROM 
                "db_bcprodus_t01393148_20220713_09491404_5639".dbo."MyCompany, Inc_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" AS "Customer" WITH(READUNCOMMITTED)  
JOIN 
                "db_bcprodus_t01393148_20220713_09491404_5639".dbo."MyCompany, Inc_$Customer$e0f77d2d-3299-4d19-83cb-208e8c3f170d" "Customer_e211"  WITH(READUNCOMMITTED)  
ON 
                ("Customer"."No_" = "Customer_e211"."No_")

It is, in essence, a join between Base App Customer table and my extension Customer table.

See the query here.

Report Extensions -> SQL statement

For this example, I added the field “Woo Id” from my previous example to the “Customers Top 10 List” report. You can see my demo below:

Report extensions to SQL statements

The query, when the compiler hits the new column in the Customer dataset, can be summarized by this:

SELECT  TOP (@0) "18"."timestamp","18"."No_","18"."Shipping Agent Code","18"."Country_Region Code","18_e211"."Woo Id","18"."$systemId","18"."$systemCreatedAt","18"."$systemCreatedBy","18"."$systemModifiedAt","18"."$systemModifiedBy"
    FROM 
        "db_bcprodus_t01393148_20220713_09491404_5639".dbo."MyCompany, Inc_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" "18"  WITH(READUNCOMMITTED)  
JOIN "db_bcprodus_t01393148_20220713_09491404_5639".dbo."MyCompany, Inc_$Customer$e0f77d2d-3299-4d19-83cb-208e8c3f170d" "18_e211"  WITH(READUNCOMMITTED)  
    ON 
        ("18"."No_" = "18_e211"."No_") ORDER BY "No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Again, we notice the “join” between the main table Customer from Base App and the new Customer table in my extension, connected through primary key, “No.” field.

We’ve seen in today’s blog how AL sends commands to the service tier for execution by the database server.

Hope this is helpful.

Business Central Musings