Business Central.Export.Non Time Data

Used for exporting dimension data (dimension data in the data warehousing terminology, data that is not categorised by time). Commonly used for exporting data from Business Central Endpoints account, dimension and dimensionValues.

INFO

This process requires the Business Central Integration Library.

DANGER

The referenced script.library path must be aligned with the upload location of Business Central Integration Library in the file system

Process Code

script.library(script.variableGet("BusinessCentralIntegrationLibraryPath"));

function pre() {
    // This function is called once before the process is executed.
    // Use this to setup prompts.
    script.log('process pre-execution parameters parsed.');
    script.prompt("endpoint", "endpoint", "accounts");//accounts, dimensions, dimensionValues

}

function begin() {
    // This function is called once at the start of the process
    script.log('process execution started.');

    const tableName = `business_central_${endpoint}`.toLowerCase();

    let companies = getCompanies();

    let batch = null;
    let tableFields = null;
    let tableReady = false;

    for (let k = 0; k < companies.length; k++) {
        if (script.IsCancelled()) {
            if (batch != null) batch.flush();
            return;
        }

        let coId = companies[k].id;
        let coName = companies[k].company_name;
        let business_central_company_id = companies[k].business_central_company_id;

        let apiurl = `${navURL}companies(${coId})/${endpoint}`;
        let result = NAV(apiurl, "GET", "");

        if (result == null) {
            script.log(`Warning: API request "${apiurl}" for company "${coName}" failed.`);
            continue;
        }

        let results = result.value;
        if (!results || results.length === 0) {
            continue;
        }

        // First rows we see decide the table's shape.
        if (!tableReady) {
            let sampleRow = Object.assign({}, results[0]);
            sampleRow["business_central_company_id"] = business_central_company_id;
            sampleRow.lastModifiedDateTime = convertToMySQLDatetime(sampleRow.lastModifiedDateTime);

            let objFieldTypes = {
                "lastModifiedDateTime": "DATETIME",
                "business_central_company_id": "INT(11)"
            };

            if (tableExists(ds, schema, tableName)) {
                // table already exists - use its real column list/order rather
                // than re-deriving from the API response
                tableFields = getTableFields(ds, schema, tableName);
            } else {
                createTable(ds, schema, tableName, sampleRow, objFieldTypes);
                tableFields = Object.keys(sampleRow);
            }

            // full refresh of this table for this run
            datasource.update(ds, `TRUNCATE TABLE \`${schema}\`.\`${tableName}\``);

            batch = datasource.createBatch(ds, `${schema}.${tableName}`, tableFields);
            tableReady = true;
        }

        for (let i = 0; i < results.length; i++) {
            let row = results[i];
            row["business_central_company_id"] = business_central_company_id;
            row.lastModifiedDateTime = convertToMySQLDatetime(row.lastModifiedDateTime);

            let rowData = [];
            for (let f = 0; f < tableFields.length; f++) {
                let value = row[tableFields[f]];
                if (typeof value === "object" && value != null) {
                    value = JSON.stringify(value);
                }
                rowData.push(value === undefined ? null : value);
            }
            batch.insert(rowData);
        }
    }

    if (batch != null) {
        batch.flush();
    } else {
        script.log(`No data returned for endpoint "${endpoint}" - table "${tableName}" left untouched.`);
    }
}

function data(record) {
    // This function is called once for each line of data on the second cycle
    // Use this to build dimensions and push data into cubes
}

function end() {
    // This function is called once at the end of the process
    script.log('process execution finished.');
}