I'm new to AL Language and would like to build a Freight Calculator as project to get started.
I was able to create a Table, a list page and a card page using VS Code. With this created, I also managed to use the Configuration Packages Page to import data into my newly created Table... this was somewhat self explanatory.
Where I'm having a hard time is finding information about the followings:
My Table has 11 columns, Origin City, Service Type, Destination City, Destination Province, Base Rate, then 6 other columns of rates based on weight.
I need to create a UI where the user can select their Origin City, Service Type, Destination City and Province and input the weight that they would like to ship. The result would be the price for the freight.
I understand that this might not be as easy as doing it in c# Winform, but there must be a way.
I'm not looking for someone to write it for me but more info on where to get started.
Thanks in advance.
Don't be scared, it's not so complicated as it might look, even compared to WinForms.
Previous post gives a good starting point for UI, and there is more documentation and examples available online. For example, here you can find an overview of different page types in BC: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-pages-overview
What you are looking for is probably a List type page, but you can experiment with various page types and see what suites your purpose.
To enable lookups to other tables, you will need to set up table relationships. This actually means that you define foreign keys in your custom table and specify respective primary keys. This is all it takes to enable the lookup functionality:
i/'ll be certain to post my findings online as there isn't much about AL anywhere. Even stack overflow is very lean in that department.
thanks for your time.
Unfortunately, now information on AL development is not as pletiful as developers would desire. Dynamics NAV is in the state of rapid transition to Business Central, and I can't say that this process is absolutely smooth and painless. Technology is changing faster than the related documentation is updated. Hopefully, it is going to be fixed soon - by the developers' community, as well as by Microsoft.
page 50101 "Sample Page"
PageType = Card;
// SourceTable = TableName;
Caption = 'Item';
TableRelation = Item;
You can remove the property "SourceTable" completely if the page is not based on any table. Just declare global variables you need and bind them to page controls. Property "TableRelation" can be defined in the page instead of a table to support the lookup functionality.
This looks like exactly what I want to do... Thanks!
I'm surprised I haven't been to find an example online... Alright, I'll give it a try.
Thanks to your answer I was able to put the following for together rather quickly. My next step is to figure out how to populate the Origin City as well as the Destination Province and City. These are all stored in 1 table with the rates.
I'd like to have drop downs of each options so that when I select the Origin City, only the Provinces that have rates would populate. The same way, if I picked one of the destination provinces, only the relevant cities would appear in the Destination city drop down.
The fuel surcharge might be coming from a Web Api Query, not sure yet. If not, I could have 1 table where an employee would be in charge of updating it. I love automation but our vendor might not offer the Web Service.
I haven't found a way to do SQL style table queries so that I can use my 1 table and get all the data out via 3 or 4 queries to generate the freight quote. Because of the way Dynamics is priced out that you pay per table when adding your own, you have to be thrifty and find ways to minimize the number of table you use...
If you used table fields for controls, TableRelation property could help you filter the dropdown list - it can be extended to apply a filter from another field of the same table. But since it's all variables, TableRelation will not work, it's not so flexible to pick filter values from variables. In this case, you'll have to write code for the OnLookup trigger of the control. For example, this is how it would work for standard tables "Country/Region" and "Post Code". Btw, take a look at these tables - probably they can help you normalize the data structure.
page 50102 "Address Sample"
PageType = Card;
Caption = 'Country';
TableRelation = "Country/Region";
Caption = 'City';
trigger OnLookup(var Text : Text) : Boolean;
PostCode: Record "Post Code";
PostCodesList: Page "Post Codes";
if CountryCode = '' then
LookupConfirmed := PostCodesList.RunModal() = "Action"::LookupOK;
if LookupConfirmed then begin
Text := PostCode.City;
Regarding filtergroups and functions like LookupMode and SetTableView, you can read related MSDN articles on older versions on NAV - functions are the same.
There is no way to write SQL queries in AL. There is a substitute for SQL called query object:
But in most cases there is no benifit in query compared to AL code. It is only recommended if you need to join large tables where performance is critical.
Thanks... Once again, very helpful!
I modified your code so that when I choose a Destination Province, the Destination City shows the Rate List with only the records that are in that province. Is there a way to only display the list of Cities? At the moment, it shows all the columns from my rates table... I would like it to behave more like a drop down...
Because I don't know how to do it in AL, at the moment, it looks like the easiest way to achieve what I'm look for would be to have multiple tables. One for Origin City, one for Destination City, one for Destination Province, another for Service Type and yet another for the actual rates, so that I can place a filter based on the selected fields.
The other option would be to use Query Option which can get expensive as we have to pay for them...
It is good to have separate tables for cities, provinces and services types - just in memory of dr. Edgar Codd. Then the table containing service rates should store city, province, etc. as foreign keys.
Still, it's possible to show or hide page fields dynamically:
TableRelation = Item;
Visible = IsDescriptionVisible;
procedure ShowFields(FieldsVisible: Boolean);
IsDescriptionVisible := FieldsVisible;
Property "Visible" controls visibility of a field. You can bind a variable to its value and add a global function to change the value. Then call this function from the OnLookup trigger.
But if you need to do tricks like this, it means that the table is not even in 2NF and should be normalized.
I'm not sure I understand your last example. But I found something called Option Fields where I define a var of Type Option and associate the field to this variable and I'm now able to define the OptionCaption for the field. The result is a drop down.
Caption = 'Destination Province';
OptionCaption = 'AB, BC,MB,NB,NF,NS,NT,ON,PE,QC,SK';
Unfortunately I haven't found a way to make my list dynamic. I wrote a procedure in a CodeUnit that gets the distinct Provinces and another one that gets the Cities, based on the selected provinces but I cannot find a way to use their return value. I cannot return a LIst of [Text] from the procedure. I converted one to a TextBuilder which should be comma separated but I cannot convert that to a String...
No, there is no way to update option list of an Option type variable dynamically. Correct solution for this problem is to extract each separate entity into its own table. It means create tables for cities and provinces and setup table relations.
I followed your suggestion and created a new Table for my Destinations. I've setup the table with 2 fields. Province and Cities.
Unfortunately, the Province drop down only displays the cities. I would like to have it behave like the Postal Code (Zip Code) table. Where if you start typing in the city, it shows you both city and province then when you have a smaller number of record visible, you select 1 and it populates both the city and province field.
Am I asking for too much at this time in AL?
Was all of this possible in C/AL but they haven't implemented it in AL?
Should I start developing in C/AL and switch to AL when it gets better?
I have to say, this language is very non-intuitive. Nothing like a C based language.
Thanks again Alexander for your time and your responses... Hopefully I can pay it back later.