---
url: /technical/process-business-central-export-general-ledger-entries.md
---

# Business Central.Export.generalLedgerEntries

Used for exporting dimension generalLedgerEntries and the expanded dimensionsetlines into seperate tables. Loads for one month at a time, completing a complete delete, and then reloading all data from Business Central.

::: info
This process requires the [Business Central Integration Library](process-libraries-business-central-integration-library).\
:::

::: danger
The referenced script.library path must be aligned with the upload location of [Business Central Integration Library](process-libraries-business-central-integration-library) in the file system
:::

## Process Code

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

var dateFilterField = "postingDate"

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("Load Year", "loadYear", "2023");
    script.prompt("Load Month", "loadMonth", "2");
}

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

    const endpoint = "generalLedgerEntries";
    const mainTableName = `business_central_${endpoint}`.toLowerCase();
    const dimTableName = `${mainTableName}_dimensionsetlines`;

    const { lastDayOfPriorMonth, firstDayOfMonth, firstDayOfNextMonth } = getAdjacentMonths(loadYear, loadMonth);

    let companies = getCompanies();

    // Per-table state, keyed by the actual table name.
    let tables = {
        [mainTableName]: { sampleRow: null, batch: null, tableFields: null },
        [dimTableName]: { sampleRow: null, batch: null, tableFields: null }
    };

    let tableReady = false;

    function flushAllBatches() {
        for (let t in tables) {
            if (tables[t].batch != null) tables[t].batch.flush();
        }
    }

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

    // Inserts one GL entry row into the main table, and one row per
    // dimension set line into the dimension lines table. dimensionSetLines
    // is an array of dimension line objects on the source row, so it's
    // normalized here into one child row per line rather than stored as
    // a single blob on the parent row.
    function processRow(row, business_central_company_id) {
        row["business_central_company_id"] = business_central_company_id;
        row.lastModifiedDateTime = convertToMySQLDatetime(row.lastModifiedDateTime);
        insertRow(tables[mainTableName], row);

        let dimLines = row.dimensionSetLines || [];
        for (let d = 0; d < dimLines.length; d++) {
            let dimRow = Object.assign({}, dimLines[d]);
            dimRow.postingDate = row.postingDate;
            dimRow.generalLedgerEntries_id = row.id;
            dimRow.business_central_company_id = business_central_company_id;
            insertRow(tables[dimTableName], dimRow);
        }
    }

    for (let k = 0; k < companies.length; k++) {
        if (script.IsCancelled()) {
            flushAllBatches();
            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}?\$filter=${dateFilterField}%20ge%20${firstDayOfMonth.replace(/ /gi, "%20")}%20and%20${dateFilterField}%20lt%20${firstDayOfNextMonth.replace(/ /gi, "%20")}\&$expand=dimensionSetLines`;
        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;
        }
        let nextPage = result['@odata.nextLink'];

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

            // dimensionSetLines is an array of dimension line objects - sample
            // the first element's shape, not the array itself.
            let firstDimLine = (sampleRow.dimensionSetLines && sampleRow.dimensionSetLines[0]) || {};
            let dimSampleRow = Object.assign({}, firstDimLine);
            dimSampleRow.postingDate = sampleRow.postingDate;
            dimSampleRow.generalLedgerEntries_id = sampleRow.id;
            dimSampleRow.business_central_company_id = business_central_company_id;

            tables[mainTableName].sampleRow = sampleRow;
            tables[dimTableName].sampleRow = dimSampleRow;

            let objFieldTypes = {
                "lastModifiedDateTime": "DATETIME",
                "business_central_company_id": "INT(11)",
                "postingDate": "DATE",
                "debitAmount": "decimal(10,2)",
                "creditAmount": "decimal(10,2)",
                "additionalCurrencyDebitAmount": "decimal(10,2)",
                "additionalCurrencyCreditAmount": "decimal(10,2)",
                "dimensionSetLines": "JSON"
            };

            for (let t in tables) {
                let table = tables[t];

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

                // full refresh of this month's data for this company
                let delSql = `DELETE FROM \`${schema}\`.\`${t}\` WHERE business_central_company_id = ? AND ${dateFilterField} > ? AND ${dateFilterField} < ?`
                console.log(delSql)

                datasource.update(
                    ds,
                    delSql,
                    [business_central_company_id, lastDayOfPriorMonth, firstDayOfNextMonth]
                );

                table.batch = datasource.createBatch(ds, `${schema}.${t}`, table.tableFields);
            }

            tableReady = true;
        }

        for (let i = 0; i < results.length; i++) {
            if (script.IsCancelled()) {
                flushAllBatches();
                return;
            }
            processRow(results[i], business_central_company_id);
        }



        // Follow pagination for this company until there are no more pages.
        while (nextPage != null) {
            if (script.IsCancelled()) {
                flushAllBatches();
                return;
            }

            result = NAV(nextPage, "GET", "");
            if (result == null) {
                script.log(`Warning: refresh of GL entries page for company "${coName}" failed.`);
                break;
            }

            results = result.value;
            nextPage = result['@odata.nextLink'];

            for (let i = 0; i < results.length; i++) {
                if (script.IsCancelled()) {
                    flushAllBatches();
                    return;
                }
                processRow(results[i], business_central_company_id);
            }
        }
    }

    flushAllBatches();
}

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.');
}

function getAdjacentMonths(year, month) {
    // Get the last day of the prior month
    const lastDayOfPriorMonth = new Date(year, month - 1, 0);
    const firstDayOfMonth = new Date(year, month - 1, 1);
    const firstDayOfNextMonth = new Date(year, month, 1);

    return {
        lastDayOfPriorMonth: lastDayOfPriorMonth.toISOString().slice(0, 10),
        firstDayOfMonth: firstDayOfMonth.toISOString().slice(0, 10),
        firstDayOfNextMonth: firstDayOfNextMonth.toISOString().slice(0, 10),
    };
}```
````