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

september

01sep10:00 am10:30 amEnsuring Quality and Compliance for Batch Manufacturers in Life Sciences

01sep12:00 pm12:30 pmIs it Worth It to Move to the Cloud? A Look at Considerations for Current Agribusinesses Using Dynamics GP

01sep2:00 pm2:45 pmWhat’s New in Dynamics 365 Finance and Supply Chain

09sep11:00 am12:00 pmConfab Live with Stoneridge – Data Strategy and Reporting – Mining Decision Making Insights

15sep10:00 am11:00 amSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

22sep12:00 am12:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

22sep10:00 am10:30 amStreamlining Batch Manufacturing with Technology

22sep2:00 pm2:30 pmProcess Automation for Microsoft Dynamics D365 for Business Central, Finance and Operations and GP

23sep11:00 am12:00 pmConfab LIVE with Stoneridge - Riding the Wave 2 Release – Key Features Coming to Dynamics 365 this October

29sep10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

29sep12:00 pm12:45 pmUnderstanding Job Costing and Tax Management in Business Central

29sep2:00 pm3:00 pmDigitalizing Horticulture & Agriculture - How to Sell Plants Online and Simplify Business Management

30sep12:00 pm4:00 pmSecurity and Permissions Training for D365 Business Central or Dynamics NAV

october

06oct10:00 am10:30 amPreview of D365 Business Central Fall Release Features and Functionality

06oct12:00 pm12:30 pmInsider's Guide to New Features Available in the Fall Release of D365 Finance and Supply Chain

07oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Technical

13oct12:00 pm12:30 pmWave 2 Release – What’s Coming for Dynamics 365 Sales and Customer Service

21oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Functional

26oct(oct 26)9:00 am28(oct 28)5:00 pmStoneridge Connect Leadership and Community Conference

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