---
url: /technical/process-libraries-business-central-integration-library.md
---
# Business Central Integration Library

::: info
This library is required to use the [Business Central Processes](example-processes#business-central-processes).
:::

To download this library, click [here](https://modlr-excel-downloads.s3.ap-southeast-2.amazonaws.com/docs/MODLR+Business+Central+Integration+Library.js)

Parts of this library will need to be configured based on your Business Central environment.

This includes the following:

* `authObj.clientId`
* `authObj.clientSecret`
* `authObj.tenantId`
* `authObj.companyId`
* `authObj.environment`

## Library Code

```vb
var authObj = {
    "clientId": "XXXXX",
    "clientSecret": "XXXXX",
    "tenantId": "XXXXX",
    "companyId": "XXXXX",
    "environment": "XXXXX"
}

//access token URL
const clientId = authObj["clientId"]
const clientSecret = authObj["clientSecret"]
const company_id = authObj["companyId"]
const tenantId = authObj["tenantId"]
const environment = authObj["environment"]
const scope = 'https://api.businesscentral.dynamics.com/.default';

const navURL = `https://api.businesscentral.dynamics.com/v2.0/${tenantId}/${environment}/api/v2.0/`;
const tokenURL = `https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`;

const ds = "Internal Datastore";
const schema = "business_central"
var sql = "";
const companyTable = "business_central_company"

var accessToken = null;
var accessTokenExpiresIn = null;
var accessTokenExpiredAt = null;

/*
Returns true if successful.
*/
function getAccessToken() {
    http.disableHostNameValidation();

    let data = `grant_type=client_credentials&client_id=${encodeURIComponent(clientId)}&client_secret=${encodeURIComponent(clientSecret)}&scope=${encodeURIComponent(scope)}`;

    http.headersAdd('content-type', 'application/x-www-form-urlencoded');

    let res = http.raw(tokenURL, "POST", data);
    let resObj = JSON.parse(res);


    if (resObj.code == 200) {
        let bodyObj = JSON.parse(resObj.body);

        accessToken = bodyObj.access_token;
        accessTokenExpiresIn = bodyObj.expires_in;
        accessTokenExpiredAt = new Date();
        accessTokenExpiredAt.setSeconds(accessTokenExpiredAt.getSeconds() + parseInt(accessTokenExpiresIn));

        return true;
    }
    return false;
}

function NAV(url, method, data) {
    if (accessToken == null) {
        if (!getAccessToken()) {
            console.log("Authentication Failed with NAV");
            return null;
        }
    }

    http.headersAdd("Authorization", "Bearer " + accessToken);
    http.disableHostNameValidation();

    let res = http.raw(url, method, data);
    let resObj = JSON.parse(res);

    if (resObj.code == 200) {
        let bodyObj = JSON.parse(resObj.body);
        return bodyObj;
    } else {
        console.log("NAV Request Failed.");
        console.log(resObj);
        return null;
    }
}

function getCompanies() {
    let records = JSON.parse(datasource.select(ds, `SELECT business_central_company_id,id FROM ${schema}.${companyTable}`, []));
    return records;
}


// ---------------- Table helpers ----------------

let fieldMapping = { //used in conjunction with the fields arg to create a table
    textDefaultField: "VARCHAR(512)",
    dateField: "DATE",
    dateTime: "DATETIME",
    int: "INT(11)",
    decimal: "decimal(10,2)"
};
var defaultDataType = "VARCHAR(512)";

function createTable(ds, schema, tableName, fields = {}, fieldsDataType = {}) {
    let array = [];
    if (tableExists(ds, schema, tableName)) {
        console.log("Table Already Exists");
        return;
    }
    let primaryKey = `\`${tableName}_id\``;
    let createStatement = `CREATE TABLE \`${schema}\`.\`${tableName}\` (
        ${primaryKey} int(11) NOT NULL AUTO_INCREMENT,`;
    for (let property in fields) {
        array.push(property);
        let dataType = fieldsDataType.hasOwnProperty(property) ? fieldsDataType[property] : defaultDataType;
        createStatement += `\`${property}\` ${dataType} DEFAULT NULL, `;
    }
    createStatement += `  PRIMARY KEY (${primaryKey})) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci`;
    if (array.length > 0) {
        console.log(`Creating Table \`${schema}\`.\`${tableName}\``, createStatement);
        datasource.update(ds, createStatement);
    } else {
        console.log("Failed to create table");
    }
}

function tableExists(ds, schema, tableName) {
    let sql = `SELECT * FROM information_schema.tables WHERE table_schema = ? AND table_name = ?`;
    let result = JSON.parse(datasource.select(ds, sql, [schema, tableName]));
    return result.length > 0;
}

function getTableFields(ds, schema, tableName) {
    // returns the table's actual column names, in column order, excluding
    // the auto-increment primary key column added by createTable()
    let sql = `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = ? AND table_name = ? AND COLUMN_NAME != ? ORDER BY ORDINAL_POSITION`;
    let result = JSON.parse(datasource.select(ds, sql, [schema, tableName, `${tableName}_id`]));
    return result.map(row => row.column_name);
}


function dropTable(ds, schema, tableName) {
    let sql = `DROP TABLE \`${schema}\`.\`${tableName}\``;
    console.log(sql);
    datasource.update(ds, sql);
}

function convertToMySQLDatetime(value) {
    // NOTE: this was referenced but not defined in the original script -
    // implemented here on the assumption it converts an ISO datetime
    // string from BC into 'YYYY-MM-DD HH:MM:SS' for MySQL. Adjust if your
    // original implementation did something different (e.g. timezone shift).
    if (!value) return null;
    let d = new Date(value);
    if (isNaN(d.getTime())) return null;
    return d.toISOString().slice(0, 19).replace("T", " ");
}

function schemaExists(ds, schema) {
    const sql = `SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?`;
    const result = JSON.parse(datasource.select(ds, sql, [schema]));
    return result.length > 0;
}

```