Professional Planner provides you with the ability to import data from databases from back-office systems (ERP systems). In addition to the data transfer from the back-office system, this function enables you to automatically create and complement the structure in Professional Planner. If, for instance, an account has been newly defined in the back-office system, it will be transferred to Professional Planner during the next import. Accordingly, any data relating to this account are also imported. This function requires a defined structure of the data table.
Please note that the import function does not replace the Import Manager; they co-exist.
Structure of the data table, explained in an example
The data table is schematically structured. You can download an example of a data table here. The table contains columns and rows. The field reference number of Professional Planner to which this figure has to be transferred serves as the title of the individual columns.
The import is carried out on a row-per-row basis. For each row the values are transferred column after column. This means that the order of the columns defines which data will be transferred first to Professional Planner.
All cells of the data table showing the entry <NULL> will not be considered during the import. There may be several columns in a data table which are not considered relevant for the element. The field reference FR2201 “Stock Opening Balance” is, for instance, not defined for an account element and therefore cannot be transferred. If a figure had been indicated in this cell, Professional Planner would have in fact tried to transfer this figure, which would, however, have been impossible.
With large structures you have to label required fields with the entry <NULL>, as this can have an effect on how long it takes to process data.
Field references used in the example data table
It is vital that you use the abbreviation FR for the field references. Here is an overview of all field references used in the data table:
|FR33026||Org ID of the element above|
|FR2049||Expense/Income Profit Effect|
|FR2052||Expense/Income Accruals & Deferrals|
|FR2055||Exp/Inc A & D Bal Sh Acct Name|
|FR2002||Exp/Inc Net Profit|
|FR2211||Accounts Payable OB|
|FR700||FA Closing Balance|
|FR701||Stock Closing Balance|
|FR711||Accts Payable Closing Balance|
You can enter all profit and loss and balance sheet field references you need. The number of columns depends on the field references used; i.e. you can extend the data table without limitation.
The example data table contains the following field references:
- FR 4759 – Organizational ID: Please find a general description of the FR4759 here. In the ideal case the organization ID consists of a combination of cost center and account number from the back-office system assuring that it is clearly defined. The only limitation for the organizational ID is that it has to be numerical and must not exceed 10 digits.
- FR 4760 - Element description: For a description of the FR 4760 please click here.
- FR 33026 – Server organizational ID: This column, together with column FR4759, is used to describe the hierarchy of the structure to be created. While column FR4759 defines the organizational ID of the element to be created, column FR33026 shows the organizational ID of the element under which the element should be built. The element indicated in column FR33026 must therefore already exist in the structure.
In the data table in the first row, the element “CC 5001 Management” is, for instance, created with the organizational ID 57500000. Entering 57500000 in column FR33026 in row 4 defines that the element “41169 Fuel“ should be created in the structure under the element “CC 5001 Management”.
- FR 2049 - Expense/Income profit effect: With this field reference you specify the account and determine in which partial result of the profit and loss statement the planned net profit should be integrated. For a description and a list of all setting options please refer to FR2049.
- FR 4757 - Element type: For a description of the FR 4757 please click here.
- FR 2052 - Expense/Income Accruals & Deferrals: For a description and the setting please refer to FR2052.
- FR 2055 - Exp/Inc A & D Bal Sh Acct Name: For a description of the FR 2055 please click here. The accruals and deferrals of the balance sheet account define the organizational ID of the balance sheet element on which the account figure should be accrued and deferred. The entry of column FR2055 in the data table corresponds to the figure in column FR4759 of the relevant balance sheet account. In order to carry out accruals and deferrals, it is necessary that the structure of the respective balance sheet account already exists; therefore, when creating a structure, the balance sheet accounts have to be created prior to the accounts. The profit effect influences the accruals and deferrals. Therefore, the figures for column FR2049 have to be transferred already in advance. If no link exists to the main balance sheet account (for instance, accounts receivable) by default, FR2052 has to be created prior to column Account Accr.&Deferr.
In order to import data correctly from the back-office system, all balance sheet accounts have to be created prior to the creation of the profit and loss accounts. To obtain a correct balance sheet, you have to import profit and loss account data prior to the import of data for the opening balance; finally you import data for the closing balance.
- FR32002 - Time ID: This field reference defines the time ID; data, such as for instance the account net profit, should be transferred to this element. The time ID has to exist in the dataset and has to comply with the conventions of Professional Planner.
- FR2002 - Exp/Inc Net Profit: Together with column FR32002, this column contains data of the field reference account net profit for the element in the period indicated.
The field references below are available for the opening balances of the balance accounts:
- FR2200 - Fixed assets opening balance
- FR2201 - Stock opening balance
- FR2203 - Accounts receivable opening balance
- FR2204 - Other debtors opening balance
- FR2208 - Other current assets opening balance
- FR2206 - Prepayments opening balance
- FR2219 - Equity opening balance
- FR2210 - Reserves opening balance
- FR2216 - Accruals opening balance
- FR2211 - Accounts payable opening balance
- FR2212 - Other liabilities opening balance
- FR2218 - Long-term loans opening balance
- FR2214 - Deferred income opening balance
Here is a list of all closing balance field references:
- FR700 - Fixed assets closing balance
- FR701 - Stock closing balance
- FR703 - Accounts receivable closing balance
- FR704 - Other debtors closing balance
- FR708 - Other current assets closing balance
- FR706 - Prepayments closing balance
- FR719 - Equity closing balance
- FR710 - Reserves closing balance
- FR716 - Accruals closing balance
- FR711 - Accounts payable closing balance
- FR712 - Other liabilities closing balance
- FR718 - Long-term loans closing balance
- FR714 - Deferred income closing balance