A better way to handle contact job responsibilities and industries in Business Central

Our Situation

Recently we were working with staff to take advantage of the improved word template functionality when logging a segment to see if we could use business central for more active marketing via email.

To make this usable we believed we needed to avoid nonsense data and use job responsibilities and industries to target the right people and companies. We viewed this as important regardless, as any third-party integration would also require better data.

An analysis of our data showed extremely low proportion of contacts having this data complicated and we wanted to understand why.

The Complexity

Put simply staff managing the data often forgot about it (even if trained originally) and didn't see how it would be useful in their everyday use of the system.

Looking into the problem we had to agree. The standard functionality for viewing and maintaining data that is held in many-to-many tables is downright awful.

Functionality buried in the menus. Data only visible as a count in fact boxes. Lack of streamlined functionality to cater for the 80%+ of cases in which there would only be one industry or job responsibility for the contact.

Whilst you can play with the menus to prioritise the item, that doesn't help discoverability or filtering in lists.

It is clear long ago when it was designed the developers wanted the CRM to be flexible. They made the data using a many-to-many type of relationship to a contact record to achieve this. Since originally implemented, there appears to have been almost no love given to the CRM functionality.

Alternatives

We could add Microsoft Sales and Marketing applications and integrate - but for our purposes (and we suspect many other customers) the addition of yet another user experience, differing data models and additional training didn't seem worth it.

We looked at cheap open source CRM like Odoo (which is also a competitor ERP).

Odoo tags

They certainly have done a better job implementing a many-to-many user interface component and allowing filtering through many-to-many relationships.

We could wait for our idea posted in the Microsoft Ideas platform to be implemented. However, until Microsoft fix both their ideas platform and moderation and product management process - contributing an idea seems a waste of time.

The last alternative was using the current available platform technology to do a better job.

Our Solution

We got bored of waiting and decided to implement our own solution. All up it took just four hours of coding and a couple of iterations. No doubt you could triple that for a robust product that is thoroughly tested and documented.

When thinking about what we could do to resolve the situation we focused on:

• Discoverability

• Simplicity

• Relevance

Our requirements were simple:

• Our staff member really wanted to deal with the 90% use case but have the option of supporting multiple job responsibilities and industries if appropriate

• We wanted to leverage existing fields and not create new data structures so that standard segmentation fields could be used out-of-the-box

• Everyone wanted to be able to take advantage of list filtering outside of segmentation tools to better manage routine data quality tasks

Our solution was novel as far as we can see:

• Extend the contact industry group and contact job responsibility tables with a new field isPrimary and add logic to the table to ensure there is only ever one primary industry for a contact.

• Add a flow field on the contact table with a lookup that used the isPrimary field to show a unique code to filter on

• Use a page field and variable to have a display-only field with intelligent drill down that provides easy to understand discoverability on cards and list pages

As you can see our staff can now on a list view of company type contacts see the primary

industry in plain English, click directly to edit and add an industry. A couple of edge cases existed including if you change which industry or job responsibility is the primary field.

By adding an unvalidated table relationship we get the benefit of a user being able to filter by the primary industry code field - even though this lookup itself is not the visible field in lists. You get this for free.

From a coding perspective we made a couple of choices which may not be best practice but seem to be patterns followed in the base application.

When it comes to a person, we found an interesting interplay between job title and primary job responsibility and a decision was made that the job responsibility is thought of as a capability/functional area of responsibility at an elevated level regarding how we engage with them - rather than all their responsibilities within a business.

• The function to return the visible text for primary industry and job responsibilities are on the record, not at the page level. This allows for common functionality to be leveraged in list/card views without defining yet another codeunit.

• We have not replaced the actual way that data is entered. We could have created a simplified dialog for adding a single industry or job responsibility since in most cases people aren't adding more than one.

• We did not use a controladdin which could potentially mirror the functionality of competitor products allowing more of an inline lookup, tag experience for many-to-many relationships.

The actual code is in our PTE and not broken out - but some examples below are shown on how we extended the underlying table.

An extension of the table for job responsibilities:

Then an example of getting the underlying text for display on a card or list.

Then an example of showing the list itself. One lesson we learnt is we can't rely on drill-down from a calculated lookup as it would only show the primary. We wanted the behaviour to show the whole list.

An example of how we structure the field on the page.

There are a few lessons learnt:

• Initially we had added a count of how many industries if there was more than one in the display field, but that led to odd behaviour in the new analyse page functionality. At most we wanted to segment those with one and those with more than one.

• The use of functions that get called at the page level will obviously have a performance impact. As we are a small instance (~1000 contacts and 5 users) this is negligible but larger instances may not like this approach.

• We looked at queries, but the lack of ability to use underlying advances functions that are native to SQL meant that we would still need to at least do calculations on each record as displayed.

• It is better to have text 'Not specified' that leave it blank as a blank field is very confusing on a drill down and not necessarily intuitive. Ideally drill-down could be disable at a record and field level - so that say the drill down does not display if it is not relevant - i.e. industry for an person and vice versa.