Business Central Workaround: Cross-Keys in AL Table Extension
One important thing to know about keys when developing a table extension in Microsoft Dynamics 365 Business Central is that you can create one or more keys, but the scope of that key is just the extended table. You cannot reference fields from the table you are extending.
The primary key is implicitly included at the end of all the keys in your extended table. Let’s prove that below.
I have extended the table sales header with the new field, “Sales Category Code” and have created a new page list sorted by this new field. If my point is correct, this page will display the data sorted by Sales Category Code + Document Type, Document No. (PK).
As we can see, we proved that the PK is added to the end of all our extended keys.
Now the problem we need to somehow solve is how to include non-PK fields in the extended key (or even how to include PK fields in another position to the extended key) like at the beginning of the key.
It looks like the only solution is mirroring the fields that we need in our extended table. This means that we will have to replicate the fields in the extended table with a different name, create subscribers for the table events (OnInsert / OnModify), and synchronize the standard fields into our extension fields. This could be very tedious depending on the field that we need to mirror.
Finding a Workaround
So, let’s find a workaround that, although might not be the best, can help us to sort out some of the related issues.
What about using queries? In a query, we can index all of the fields we want.
Allow me to explain the idea with an example, using the new field in the Sales Header extension:
Let’s say we have the following list of sales orders with the new field on it and we need to order them by Sales Category Code + Sell-To Customer No.
We cannot add a new key to the extended table containing the field Sell-To Customer No., so we create a new query with the sorting needed.
Here is the query and indexed in the way that we need, we can even group it and make subtotals.
We can create queries for all the queries that we need to emulate the keys (that we don’t have) as long as we need them for sorting purposes.
For our example I will create a page list to display the data from the query.
And the result is a list sorted by Sales Category Code + Sell-To Customer No. as expected.
There is a subtotal in the last row as well.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.