Directly link to a record from data quality controls

The goal of Data Quality controls is to find and quickly fix data quality errors in source systems and processes. When the source system allows you to directly link into a specific record, you can direct data owners to that record for fast resolution.

In this tutorial we will use Business Central as an example to link to.

Understanding the URL

First you need to understand the source system URL to be able to regenerate it.

For example, Business Central URLs are of the type:
https://businesscentral.dynamics.com/TENANTID/ENVIRONTMENT/?company=COMPANYAS&page=PAGENUMBER&dc=0&bookmark=BOOKMARK

The page refers to the type and for example the G/L Account Card is number 15.

Each record is identified via Bookmark which we can’t recreate but instead we can filter down to a single record with the “filter” parameter.

So if we remove the bookmark parameter and instead add: &filter='No.' IS '10102'

then we are linking directly to account number 10102.

So the final URL becomes https://businesscentral.dynamics.com/TENANTID/ENVIRONTMENT/?company=COMPANYAS&page=PAGENUMBER&dc=0&filter='No.' IS '10102'

Building the Query

To link to the card we need to generate results on the format:

"LINK NAME":URL

For example in a PowerShell OData Query the code would be:

Get-Action -Action "accounts" -CompanyId $CompanyId |
 	Where-Object { $_.blocked -eq "$false"} |
 	Select-Object -Property number, displayname, category, subcategory, blocked, accounttype, @{Name='Open in BC'; Expression={"https://businesscentral.dynamics.com/TENANT/ENVIRONMENT/?company=COMPANY&page=17&dc=0&filter='No.' IS '$($_.number)'" }}

In a SQL query the construction would be something like:

SELECT *
  , '"Open in BC":https://businesscentral.dynamics.com/TENANT/ENVIRONMENT/?company=COMPANY&page=17&dc=0&filter='No.' IS '''+a.No_ +'''
FROM dbo.Account a

Configuring the Control

In Column Formatting make sure to set the Column Data Type as Textile:
 

This option changes the value to a link.