How to query OData services from Business Central

In this article, you will learn how to create query OData services from Business Central or other sources.

Note that all code in the pictures are at the end of the article.

Connecting to the Service

Business Central OData services consist of an endpoint and authentication. This article uses App Registrations that need to be setup within Azure, consisting of client id and secret.

For reusability Authentication and querying is wrapped by Query Snippets in TimeXtender Orchestration and Data Quality.

1. Add the following Global Parameters after registering in Azure  like in the picture.
   1. The Parameters needed are:
      1. Client ID
      2. Client Secret
      3. Tenant ID
      4. Company GUID
      5. Environment
          

         

2. Create a Query Snippet with the authentication and querying code.

   

    

Querying

With TimeXtender Orchestration and Data Quality you can either query the services directly in Queries or transfer the data using DataTransfer. In this example we are using Query but the exact same script works as well in DataTransfer.

Create a Query and add the Query Snippet you created above with {NAME}.

In the case below we are querying all customers within Business Central and filter those that do not have an contact email. Then we select specific columns from the results.

Best Practices

• The script above is best used when the number of results is not high (e.g. less than 100.000).
• Querying services can be resource intensive and it's important to choose where to put Where-Object filters.
• When executing multiple rules on-top of a single service consider using Data Transfer to stage the data and then create Queries on top of the staged data.
• You can directly link to specific records in Business Central from the data quality controls using the guide here: 
  G

  • Directly link to a record from data quality controls

   

Scripts from pictures

Authentication and Query Snippet

# Add config here
$TenantId = "{$AppRegBC_Tenant}"
$ClientId = "{$AppRegBC_ClientId}"
$ClientSecret = "{$AppRegBC_ClientSecret}"
$Environment = "{$AppRegBC_Environment}"
$Url = "https://api.businesscentral.dynamics.com/v2.0"
$CompanyId = "{$AppRegBC_Company}"

function Get-AccessToken {
    param (
        [string]$TenantId,
        [string]$ClientId,
        [string]$ClientSecret
    )

    $Body = @{
        grant_type    = "client_credentials"
        client_id     = $ClientId
        client_secret = $ClientSecret
        scope         = "https://api.businesscentral.dynamics.com/.default"
    }

    $TokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Method Post -Body $Body
    return $TokenResponse.access_token
}

function Get-Companies {
    param (
        $Headers
    )

    $compUrl = "$Url/$Environment/api/v2.0/companies?tenant=$TenantId"
    $results = Invoke-RestMethod -Uri $compUrl -Headers $Headers -Method Get

    return $results.value
}

function Get-Action {
    param (        
    	[string]$Company,
        [string]$Action
    )

    $itemsUrl = "$Url/$Environment/api/v2.0/companies($CompanyId)/$Action"
    $results = Invoke-RestMethod -Uri $itemsUrl -Headers $Headers -Method Get

    return $results.value #| Select-Object -Property * -ExcludeProperty '@odata.etag'
}

$AccessToken = Get-AccessToken -TenantId $TenantId -ClientId $ClientId -ClientSecret $ClientSecret
$Headers = @{ Authorization = "Bearer $AccessToken" }

$Companies = Get-Companies -Headers $Headers

Query/DataTransfer script

{BusinessCentralConfig}


Get-Action -Action "customers" -CompanyId $CompanyId |
 	Where-Object { $_.email -eq ""} |
 	Select-Object -Property number, displayname, type, country, email