Handling many-to-many relationships in Business Central in 2021

Anyone reading this who is using some on the more modern no-code/low-code/web frameworks might be taking for granted that they have an easy way to add, define the behavior and create an excellent user experience to help users work with many-to-many relationship in their application.

However, with all its advanced features accreted over the years, Microsoft Business Central (formally NAV) still has no in-built and usable way to achieve this. I couldn't find any idea on Ideas Central that represented this (but as I have been critical of before - the sites search/filtering/editorial is abysmal). My new one is found here.

The real-world requirement

We needed one level of hierarchy below product category and above item - for which variants/grid items do not make sense. In the food business - we can have essentially the same product (Black Turtle Beans) that come from multiple countries, different vendors and have fundamental different specifications. Variants do not work.

So we came up with the concept of a Marketable Item (or Generic Item). . That marketable item is the master for image, long description and the many-to-many targeted market segment to which the item might appeal.

However, implementing the marketable item was perfectly possible and easily implemented with the current technologies of #msdyn365bc. It was the implementing of the relationship between the marketable item and the target market segments to which it related that caused me severe psychological pain - in no way did I want to copy the terrible user experience of the 'job responsibilities' functionality.

The challenge

The standard pattern for how business central handles the many-to-many relationship is represented by the humble (and underused) job responsibilities on a contact.

Buried deep in the menu structure is a list page that allows you to add job responsibilities to a contact. Any contact can have multiple job responsibilities and any one job responsibilities might be held by multiple contacts.

Right away you might spot some usability issues. Despite the fact I've already added the Purchase responsibility, it is shown in the dropdown. The underlying code and description is shown even though they are not meaningfully useful and create clutter. There are multiple ways to add a new line and it doesn't really make sense to search in this context. More importantly the ability to add and see this important data is hidden in a submenu.

Recently I set out to add some reasonably major new functionality to our per-tenant extension and I decided I wasn't satisfied with this approach. So I decided to hack, taking inspiration from the famous @hougaard.

My design principles were fairly simple - get as close to the best practice experience of tagging as possible. Given the audience for this article I thought I would use the example of a label in GitHub.

The use of tagging with labels has a couple of great features:

• Underlying complexity is hidden

• Selected tags are easily visible

• You can easily remove selected tags

• There is a way to search/filter

• You can't add a tag more than once

There are many different ways of implementing the same visual experience and some are older than others. For example NetSuite has the open to select multiple values on a drop-down.

NetSuite behind the scenes clearly does some work to create a many-to-many relationship table that means just simply selecting a checkbox in their visual designer will support the functionality.

So as an experiment I thought I would see how close I got.

The data design

As the team at #msdyn365bc had not yet delivered any underlying plumbing to transparently support many-to-many relationships it is left to the developer to build something. I chose a few principles to start with:

• I would use the new SystemID unique identifiers within the key

• I would only ever show the meaningful description/title to the user

• I would not store redundant data

I make use of lookup fields as a way of ensuring that the tag added always shows the latest description, without relying on the code/description pattern implemented elsewhere.

The page structure

On the user experience I have used a ListPart with a single field repeater field showing the market segment title flowfield and added a new group with a field (linked to a global variable) and a PagePart referring to that ListPart to support the concept of adding to the many-to-many relationship. This looks something like the code below:

A couple of explanations are in order:

• The field MarketSegment is linked to a global variable as there is no such actual field on the underlying table (of course). It is only there to allow you to lookup what additional segments are available and then add the chosen segment as a tag.

• We never want to show the user a segment that has already been selected. We use a procedure BuildExclusionFilter to ensure that anything previously selected is not shown.

• The ValidateNewSegment actually does the work of double checking the tag entry doesn't already exist and creating it if it doesn't.

• The ability to link based on SystemID GUID simplifies the filtering of the pagepart to show just the relationships for this particular Marketable Item.

This all ends up with the visual interface shown below.

I would argue that this has all the functionality of a tagging system, however is suboptimal on a number of levels:

1. It takes up too much room in the user interface (although I'll take advice on how to compact it down).

2. There is no ability to do look-ahead searching on available segments to add. You need to open the lookup (not a huge deal) - but not best practice.

3. It took a lot of custom coding with 1 x extension table, 2 x extension pages and multiple code procedures to tie it all together.

Whilst I suspect a better AL developer than I (I'm a customer hacking) could figure out a way to generalise the underlying code to filter tags and create entries in the many-to-many relationship table, I do not see how the user interface constraints could be solved without either a ControlAddin built in Javascript or Microsoft actually enabling a field type that supports many-to-many relationships transparently.

If you want access to this hacked together code and a lot of other ideas implemented to support a real-world business just go to my repository at github. Feel free to spot issues, improve the code via a pull request or borrow anything you want for your own purposes. I know I have from others.