Business Central Workaround: Cross-Keys in AL Table Extension

By Maria Ocampo | February 28, 2020

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

Recommended Reading:

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X