Field Descriptions for Key Tables

Issue

A user wants to add field descriptions for key tables.

Disclaimer

Any changes should be done and tested in your test/sandbox environment before applying to your live Intact Xline environment.

Solution

Xline features an incredibly detailed table and field structure. To identify the fields you wish to export or report on, it’s essential to understand the data contained in each field.

This guide highlights key tables and fields commonly needed for creating reports or exporting data via Excel or the Intact Exporter. Although this list is currently limited, it will be expanded over time. This article focuses on key fields in the nominal ledger and transaction tables, as well as those related to customers and suppliers.

Setup

No specific setup is required, as all tables and fields are available to all users unless individual restrictions have been applied. You don’t need to stop Codebase, and you can log in from any PC running the Xline Client or Xline Direct if no users are logged in.

One method for exporting data from these tables is to create an export file using the Excel link option in the Utilities menu. Data can be modified in Excel and then re-imported.

To do this, log in and select the Utilities menu. From the ribbon menu, choose the Excel Link option. Then, use the drop-down option “Create Export File,” and you will see the following screen:

In this example, the “Customers” option has been selected in the “Table to Export” field. The actual table name for the customers table (CUSTS) is displayed at the top of the box. The “Select fields to export” window lists the available fields. Some fields have user-friendly descriptions, while others are shown with their actual field names in CAPITALS.

To access similar information for suppliers, choose “Suppliers” from the “Table to Export” drop-down menu, which will display the table name SUPPS.

Similar options are available for other tables not covered in this document.

However, the tables for the nominal ledger (NOMLEDG) and transactions (TRANS) are not available in the same way. Instead, you need to select the “Custom” option in the “Table to Export” drop-down menu and then enter the actual table name, as shown in the example below for NOMLEDG:

To export data from the transactions table, select “Custom” in the “Table to Export” drop-down menu, then enter “TRANS” as the table name. The available fields will be displayed. Select individual fields for export by using the > box and then click “Export.”

Please note: Always take a backup before re-importing any amended data.

Below are descriptions of the key fields within these tables and the information they contain:

In both the NOMLEDG and TRANS tables, the field TRTYPE (which has the same name in both tables) contains definitions that indicate the type of transaction. These definitions are typically used in Excel to filter data or in the Intact Exporter to define the scope of the output:

NOMLEDG->TRTYPE
A: Accrual
B: Bank transfer
C: Credit Note
D: Discount
E: Calc Rounding
F: Foreign Currency Difference
I: Invoice
J: Journal
K: Purchase Invoice Register Invoice
L: Lodgement
M: Pro-Forma
O: Opening Balance
P: LEDGER=N (Nominal Ledger): Prepayment
P: LEDGER=Z (Prior Year End)
P: Payment
R: Receipt
S: Stock Adjustment
T: Deposit
V: Revaluation
W: Purchase Invoice Register Credit Note

TRANS->TRTYPE
A: Stock Adjustment
B: BOM Build
C: LEDGER=T (Stock Ledger): Transfer
C: Credit Note
D: LEDGER=S: Delivery
D: LEDGER=P: Goods Received Note
E: BOM Sub Item
I: Invoice
K: Kit Item
L: Loc Distribution
T: Transfer