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

june

02jun2:00 pm2:30 pmBusiness Intelligence for Batch Manufacturers

16jun10:00 am10:30 amHow AP automation can speed up month-end close in D365 Finance

17jun11:00 am12:00 pmConfab Live with Stoneridge – Real Time Marketing Strategies for Success

23jun10:00 am10:30 amThe Modern Manufacturer: Cost Accounting in D365

30jun10:00 am10:30 amAP Automation Checklist for a 2022 Rebound

30jun2:00 pm2:30 pmAchieving Success: How Technology Is Helping Nonprofits Thrive

july

01jul11:00 am12:00 pmConfab Live with Stoneridge – Dataverse Virtual Entities

14jul12:00 pm12:30 pmBeyond Reporting - What Business Intelligence Can Do For Your Agribusiness

15jul11:00 am12:00 pmConfab Live with Stoneridge – Integration Strategies for End User Success

21jul10:00 am10:30 amThe Modern Manufacturer: Manufacturing Software in the Real World

21jul12:00 pm1:00 pmIs it Worth it to Upgrade to Dynamics 365 Finance and Supply Chain Management? - Everything AX Users Need to Consider

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