Database Templates

Overview

The database_template_export script exports selected data from an application database (set of books) to a text file that can then be imported into a different database using the corresponding database_template_import script.

The idea is to export a skeletal subset of the set of books representing its basic structure, such as the trial balance and financial reports, while dropping the bulk of unnecessary data. This exported template file can then be imported into a newly created set of books to more quickly set it up with reports and accounts. You typically want to retain the trial balance and financial reports (including their accounts), but leave behind the subsidiary ledgers such as customer, vendor, and product accounts. Transactions, sessions, and audit records are also left behind.

The export file is a flat text file that can be viewed and edited using a basic text editor. Because it contains the essential underlying structure of a set of books we refer to it as a database template file. Database template files are given the file extension .nv2_database_template.

How to use the database_template_export script.

To get started quickly we first provide brief step-by-step instructions, and then follow up with a more detailed description of how the script works.

1. Open the set of books from which you want to export.

   You have to open the set of books as the ADMINISTRATOR user. Security is a concern when exporting data so full access is required.

2. Position on the root report and select the reports to export.

   Click on the root report in the tree explorer in the left pane. A report table should appear in the right pane as shown in the figure above. If you only intend to select one report you can just position on it. Otherwise select any number of reports.

   If the right pane tab is not on reports, then click on it's report tab. All reports should be shown, even sub-reports. If this isn't the case click on the name, description or folder column title because then all reports will be displayed; even sub-reports.

3. Run the database_template_export script.

   Issue the Tools>Script Evaluate command. Click on the <List> button and select this script from a list of scripts. If the script doesn't appear for some reason then click <Browse> and navigate to c:/nv/nv2.dat.scripts, where c:/nv is assumed to be the NewViews installation folder.

   A dialog window appears with a set of check boxes and a file entry field as shown below.

   

4. Set the check box options as desired (seldom used).

   The check boxes enable you to export objects that don't appear on any report. Note also that this includes employee objects. They're offered for completeness but it's almost never necessary to check any of the boxes.

5. Specify the output file.

   The data will be exported to the file you specify. We refer to it as a database template file. The file must have the extension .nv2_database_template and this extension will be added automatically if you omit it.

   You can always type in the full path to the output file, but you can also pick from a recently used list or a file explorer by clicking on <List> and/or <Browse> buttons. You will be warned before overwriting an existing file. A useful technique is to pick an existing file just to select the folder, and then edit the file name.

   A good destination is the folder that contains the export database.

6. Click on <Export>.

   The database will be exported to the output file. You will be asked to confirm before overwriting an existing file.

How is data selected for export?

To select the data to be exported, you first mark some reports. Often it's enough to just select the trial balance as will soon become clear.

Suppose you mark only the trial balance report before exporting. The trial balance and all accounts on it are exported, but the export doesn't stop there. Remember that NewViews accounts total to other accounts. These total accounts will also be exported, even if they're on different reports. The reports that the total accounts are on will also be exported as well as any other accounts on them in turn. And if these accounts total to accounts on yet other reports, the process repeats.

So what is the result when you export only the trial balance? Accounts on the trial balance typically total to financial reports such as the income statement and balance sheet. Therefore, when the trial balance is selected, these reports and all accounts on them will also be exported. If accounts on the income statement or balance sheet in turn total to accounts on other reports, say summary financial reports, then these too will be exported.

On the other hand, suppose that in addition to the trial balance, you also select the income statement and balance sheet before exporting. This will actually change nothing because accounts on the trial balance total to balance sheet and income statement accounts and therefore these financial statements were already included when the trial balance was the only report selected for export.

What makes the trial balance so special?

The trial balance is a dividing point in your set of books between subsidiary reports (also called sub-ledgers), and the financial statements. Some view it as a choke point through which all information must pass on it's way from where it's collected to where it's reported. Accounts receivable (customers) and accounts payable (vendors), inventory, and so on, are examples of sub-ledgers. These sub-ledgers eventually total to a controlling account on the trial balance but they don't really contribute to the financial structure of the set of books. In the sense that they total to an account on the trial balance, they are below the trial balance.

On the other hand, the financial reports are ultimately built from the trial balance. Trial balance accounts total to accounts on financial reports so in this sense the financial reports are above the trial balance. Usually it is the trial balance and the financial reports above it that contain the structure that you generally want to transfer to a new set of books through a template.

What data is exported automatically?

In addition to the reports and accounts that are exported as described above, it is also useful to export several types of non-financial information to more quickly set up a new set of books. The data listed below is exported automatically.

1. Journals

   All journals are exported.

   Journals can contain information you may want in a new set of books. For example, sales and purchase journals specify trade tax payable and trade tax expense or refund accounts so these accounts and the reports they're on are included in the export.

2. Payrolls

   All payrolls are exported. Note that this does not imply that their employees or any paychecks are exported.

3. Users

   Users, along with their access rights, passwords, and options are exported. It may be useful to automatically set up the same users with the same options and access rights in a new database.

If some of the above objects are not desired in the new database, you can easily delete them. For example, the journals will not have transactions, users will not have sessions or audit records and payrolls will not have employees (unless explicitly exported) so they can be deleted from the new database if desired.

What data is not exported?

1. Transactions

   Transactions are left behind as they do not contribute to the structure of the set of books but instead represent the actual accounting data that we don't want when creating a new set of books.

2. Sessions and Audit Records

   Like transactions, sessions and audit records start "from scratch" in a new set of books. Note audit records will be created for the imported objects as usual.

3. Payruns

   A payrun is a collection of paychecks that were issued on a particular payday for a particular payroll (collection of similar employees). So like the transactions themselves, payruns start from scratch in a new set of books.

4. Old NV1 (NewViews for DOS) data.

   This is old information originally imported from NV1.

What are the check boxes for?

The check boxes are for data that doesn't appear on any report. Consider customers, for example. If your customers are on a report then you can control whether they're exported by whether you select that report or not. But more often, customers are added as sub-accounts of the AR account folder and therefore they don't appear on any report. However, if you check the customers box then these AR accounts will be exported.

The same applies to any of the other account classes. Even general and expense accounts could be added as sub-accounts of a folder and therefore might not appear on any report. Like customers, the check box let's you decide whether or not to export such accounts. Generally speaking, it is unlikely that you'll need to check any of the boxes. They're offered mainly for "completeness".

Employees

Employees don't appear on any report so they have their own check box. Normally you don't want to export the employees but there have been cases where a NewViews user wanted to transfer employees to a new set of books.

When employees are selected for export, all accounts on the employees are exported, along with any other account they total to, and their reports, such as those summarizing payroll activity. For example, withholding reports will automatically be exported when employees are exported.

Other Notes

As mentioned, when an account is exported, the accounts it totals to are also included in the export, and this is repeated recursively. There are a few other areas where this concept applies in a useful way.

We already mentioned one. Journals are always exported, and when they refer to other accounts such as the tax payable, or expense or refund accounts, then these accounts are also exported. This is true in general. For example, inventory accounts can specify associated expense or refund accounts, so if an inventory account is exported, so will any associated accounts. Similarly, sales accounts can specify associated inventory and/or cost of goods sold accounts so they'll be exported together.

The same logic applies to non-financial areas. Users are exported and so are their access rights, and the objects to which they have access are also exported.

What about schedules below the trial balance?

For example, suppose you have an expense schedule. That is, you keep detailed expenses on a separate report and total them to controlling expense accounts on the trial balance. If you select the trial balance for export, it will not include the expense schedule in the export, and maybe that's what you want. If that's the case, select the expense schedule for the export. Note that if you do select the expense schedule, then also selecting the trial balance becomes optional since expense schedule accounts total to it.

Editing the template file.

The template file is a flat text file and can be edited using a text editor, or a word processor if it has a low-level "flat" edit mode. The point is that you shouldn't use a word processor that introduces mark-up information into the file.

The template file contains data in the form of structures wrapped in braces. The content is reasonably self-evident. It can be edited for purposes such as finding and replacing text to change account names or descriptions, but to go farther than this generally requires additional care. Note however, that if importing a template fails, it generally causes little difficulty because the database that it is being imported into is usually a newly created database.

Template file security considerations.

Most often there is no information in a template that needs to be protected. In fact, templates are usually used as starting points for new databases, and in that case you'll want them accessible to others.

However, if you include information such as employees or customers in the export you do not want to make the template file accessible to others. Be aware that the template file is created in your directory system and is not password-protected like a NewViews database. In this case, you should use it for the intended purpose, such as importing the template into a password-protected NewViews database, and then immediately delete the template file.

Some Examples

In this section we describe the kinds of situations that can occur when exporting and importing database templates. They should help you understand not just how template export/import work, but also reveal their limitations.

Accounts may be exported and imported unexpectedly.

When we exported the trial balance from the demo_canada set of books and imported them into a newly created set of books, several unexpected customer and sales accounts appeared. They were unexpected because the customers are not on any report and we didn't check the customer account check box. The sales accounts also were not on a report and were below the trial balance; i.e. they total to accounts on the trial balance "above". So what happened?

Prices were the reason. Customer accounts and sales accounts have price objects and when an account is exported, so are it's prices. Although the customers weren't on any report, their super-account, i.e. the root AR account serves as the receivables control account on the trial balance and is therefore exported. In the demo_canada set of books the root AR account has a number of prices, so they are exported. Several of these prices also belong to sales accounts so although the sales accounts were "below" the trial balance and you would they would not normally be exported, they were in fact included in the export. Then these sales accounts had other price objects that reference the unexpected accounts.

This behavior is not a problem because the accounts in question can be deleted from the newly created set of books. It would be a bigger problem if they were dropped from the export because in many cases price information is a valuable part of the overall setup of an accounting database. The demo_canada books happen to have a lot more going on than most sets of books because they are a playground for the demonstration of a wider range of situations than normally encountered in one database.

Multiple accounting entities in a single set of books.

Consider a set of books that contains the accounting for three different companies: A, B, and C. We tried to export just Company A by selecting its trial balance and exporting to a template file. Note that you would rarely want to do this but it's instructional to see what happens. After importing the template into a newly created database we found many of the reports from Companies B and C as well as the expected Company A reports. Why?

The answer lies with the journals. When creating a template all journals are automatically exported and several of the journals used for Companies B and C referenced trade tax payable accounts for those companies. Therefore these trade tax accounts were also exported along with their reports, the accounts on those reports, the accounts they in turn total to, and so on. The result is that a number of reports, typically those at or above the trial balance, were also exported for companies B and C.

To address this issue we could have required that you select journals as well as reports for an export, but in practice this is too complicated to be viable. The vast majority of cases are simple and would become unnecessarily complicated by such requirements. In the multiple-company case above you can just delete the reports, accounts, and journals that belong to companies B and C, leaving those for company A. They have no transactions and therefore can be deleted. This approach is easier to understand and has the advantage of not placing an unnecessary burden on the vast majority of more straight-forward cases.