Logical columns in Dynamics 365

Logical columns in Dynamics 365

Microsoft Dynamics 365 provides end-users with a robust set of tools to easily access, review, and relate their data.  It also gives customizers an incredible amount of flexibility when it comes to configuring the system, especially with the Power Platform.  Dynamics includes several components that are hidden away, even to system customizers.  One of these components, logical columns (or fields, prior to the terminology change with the release of the Power Platform), are difficult to locate and can be even more troublesome when trying to troubleshoot dependency errors.

What is a logical column?

Put simply, logical columns in Dynamics 365 are used by the system to reference data.  These columns are not visible outright to an app user or to a customizer in Power Apps.  Furthermore, these columns are not editable or visible in a solution’s XML data (assuming the column is included in the solution).

For example, say we create a lookup column called Test Lookup on the Account table, and this column looks up to a contact row on the Contact table.  When our lookup column is created, a relationship to both the Account and Contact tables are also created, and it appears to us at least that we now only have a single column in Power Apps:

But, alas, we have actually created a few additional columns: the related logical columns.  These additional columns allow us to see the data in the lookup column not as a GUID, but as human-readable text, and it is this human-readable text that is displayed to the end-user on a form or in a view.

Accessing a logical column

Dynamics logical columns are difficult to access and, as of this writing, require the use of third-party tools such as XrmToolBox’s Metadata Browser.  We can leverage the Metadata Browser to dive into a table’s columns, and from here we can observe all of the properties that a lookup column has, including its related logical columns.

Here, we can see the Test Lookup column we created has two related additional columns, btg_testlookupName and btg_testlookupYomiName, each an attribute of the Test Lookup column.  If we look closer at the metadata, we can see that these two additional columns have their IsLogical property set to True:

In this case, when we look at the Test Lookup column on a form or in a view in Dynamics, we’re actually seeing data that’s populated in the btg_testlookupName (or btg_testlookupYomiName) logical column.  Otherwise, we’d be looking at a GUID similar to this: 6b8e50e1-3c94-49eb-bf01-3674385830af.

Troubleshooting logical column issues

When issues arise related to logical columns, they can be very difficult to diagnose.  When attempting to import a solution into another environment, you may run into dependency errors if a logical column is somehow missing or corrupted.  End-users may see GUIDs in a lookup column where they would expect to see a human-readable name.

Though it may be time-consuming, especially if a lookup column has a lot of dependencies, the best course of action may be to delete the lookup column from the environment completely and recreate it.  This will remove all related logical columns and recreate them, thus resolving any issues.  WARNING: Deleting a column from an environment also removes that column’s data, so be sure to export all data appropriately, especially if the issue is in a production/live environment.

Contact Beringer Today!

We love to implement Microsoft Dynamics 365 and Power Platform functionality here at Beringer.  We’ve been working with Microsoft Dynamics since its inception, and we’re always finding innovative ways to implement the latest tools and help automate business processes.

Beringer Technology Group, a leading Microsoft Partner specializing in Microsoft Dynamics 365 and CRM for Distribution also provides expert Managed IT ServicesBackup and Disaster RecoveryCloud Based Computing, Email Security Implementation and Training,  Unified Communication Solutions, and Cybersecurity Risk Assessment.