Business Central Workaround: Cross-Keys in AL Table Extension

by | Updated January 7, 2021 | Dynamics 365 Business Central

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.

Related Posts

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

january

14jan11:00 am12:00 pmConfab with Stoneridge - Livestream - Looking Forward: Predictions for Technology in 2021

20jan10:00 am10:30 amThe Modern Manufacturer - Demand Forecasting Functionality

27jan12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 1 – Fit

28jan11:00 am12:00 pmConfab with Stoneridge - Livestream - Ask the Experts!

february

03feb10:00 am10:30 amThe Modern Manufacturer - Enterprise Asset Management

03feb12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 2 – Platform

10feb12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 3 – Implementation Partner

11feb11:00 am12:00 pmConfab with Stoneridge - Livestream - Live Agents, Power Virtual Agents, Omnichannel – Oh My!

16feb12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 4 – Cost

17feb10:00 am10:30 amThe Modern Manufacturer - Machine Maintenance Work Orders

25feb11:00 am12:00 pmConfab with Stoneridge - Livestream - Surfing Through the Dynamics 365 Wave 1 2021 Release

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X