Hi,
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:
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-set-relationships-between-tables
Hi,
thanks for the links. I’ve been reading all sort of documentation over the last few weeks. Microsoft doesn’t offer any example that i could find that explains how to make “forms” like i’m looking to achieve. from what i gathered, i may have to look into JavaScript. i could be wrong but i’m not sure if you can have fields on a page that are not bound to the database.
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.
As for your page, you don’t have to create custom JavaScript controls - it’s possible, but there are easier ways to do it. Page controls don’t have to be bound to tables - a global variable in the page can be used as the source for a control.
page 50101 "Sample Page"
{
PageType = Card;
// SourceTable = TableName;
layout
{
area(content)
{
group(ItemGroup)
{
field(ItemCodeControl;ItemCode)
{
Caption = 'Item';
TableRelation = Item;
}
}
}
}
var
ItemCode: Code[20];
}
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.
Cheers
Alexander,
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…
Thanks again.
Yann
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;
layout
{
area(content)
{
group(AddressGroup)
{
field(CountryCodeControl;CountryCode)
{
Caption = 'Country';
TableRelation = "Country/Region";
}
field(CityControl;City)
{
Caption = 'City';
trigger OnLookup(var Text : Text) : Boolean;
var
PostCode: Record "Post Code";
PostCodesList: Page "Post Codes";
LookupConfirmed: Boolean;
begin
if CountryCode = '' then
exit(false);
PostCode.FilterGroup(2);
PostCode.SetRange("Country/Region Code",CountryCode);
PostCode.FilterGroup(0);
PostCodesList.LookupMode(true);
PostCodesList.SetTableView(PostCode);
LookupConfirmed := PostCodesList.RunModal() = "Action"::LookupOK;
if LookupConfirmed then begin
PostCodesList.GetRecord(PostCode);
Text := PostCode.City;
end;
exit(LookupConfirmed);
end;
}
}
}
}
var
CountryCode: Code[10];
City: Text[30];
}
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:
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-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:
layout
{
area(content)
{
field(ItemCodeControl;ItemCode)
{
TableRelation = Item;
}
field(ItemNameControl;ItemDescription)
{
Visible = IsDescriptionVisible;
}
}
}
procedure ShowFields(FieldsVisible: Boolean);
begin
IsDescriptionVisible := FieldsVisible;
end;
var
ItemCode: Code[20];
ItemDescription: Text[100];
IsDescriptionVisible: Boolean;
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.
field(DestProvinceVar; DestProvinceVar)
{
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…
Thanks
Yann
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.
Thanks Alexander,
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.
Yann
AL has some limitations compared to C/AL, but drop-down functionality is not one of them. You can do the same things in both.
To show certain fields in the drop-down list, create the Dropdown field group in your table: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-field-groups
As for the Zip code selection that populated several fields, here you need to do some coding to make it behave like this. Controls with lookups to the Post Code table call the function PostCode.ValidateCity in their OnValidate triggers. You will need something like this too - in the trigger OnValidate, assign all the field you need populated.
Me again!
I got my 2 drop downs working great. They both get their data from the same table which stores Provinces and Cities.
On the windows Client, when I type in the Province Field, the displayed table gets filtered down. When in the City Field, typing the city name filters things down.
Where i’m a little lost is how to get the “record” that was selected. I would like to get the selected record and store it into a record variable so that I can update the City field when working from the Province field or update the province field when working from the city field.
I am able to set the fields statically but I need the selected record to do it dynamically.
Thanks
Yann
This thread is turning into a “Complete guide to Business Central UI development” 
You can’t access the related table when the reference is configured as a “TableRelation” property. It’s all controlled by the platform, and it returns only one field value. You have full control over the related record if you implement it in the AL code, OnLookup trigger. But then the selection list can only be displayed as a lookup page, not a drop-dowm list. Oops, we have already been there…
So, I see four options here to choose from:
-
Make the user select the city in two steps: “Provinces” control shows only the list of provinces, “City” filters the list of cities by the selected province. That’s the traditional NAV way of work, and that’s what I recommend to do.
-
Do a trick and introduce a surrogate primary key to your province/city table - a simple integer number. TableRelation reference will yeild this number, and you can use it in the OnValidate trigger to retrieve the record. Drawback: when the user selects a record, they will see a strange number instead of the province code until they move the focus to another field.
-
Forget the drop-down and opt for a lookup page - do everything you need in the OnLookup trigger.
-
Custom controls in JavsScript.
Probably not a bad thing to have more information online about AL Programming…
I want to thank you very much for all your insight and help… I picked up Programming Microsoft Dynamics 365 Business Central, Sixth Edition by Mark Brummel last night and I’m only in chapter 2 and it starting to get much clearer. It has exactly what I was looking for…
I will follow up shortly with my solution.
Thanks again.
Yann
