3305 Main Street Ste 019 Vancouver, WA 98663

Why do some tables appear more than once and end with "(1)" or "(2)"?

Scenario
When a list of table names is displayed in an Office Connector wizard, some tables may appear more than once and end with "(1)" and "(2)".

Explanation
Office Connector uses certain Microsoft database technologies that do not support the use of tables that have more than 255 columns.  Since there some tables in the Sage 300 Construction and Real Estate database that contain more than 255 columns, these tables are "split" into multiple tables.  This is done by the Timberline ODBC driver which is the component used by Office Connector to interact with Sage 300 Construction and Real Estate data.  A split table would appear as two tables in the table list and each has the same number of rows identifying the same records in Sage 300 Construction and Real Estate.  The first table will contain the first 255 columns of the table.  The second table will repeat the primary key columns (in order to identify the specific record) and then continue with the 256th column.

Where does the split occur?
The split always occurs at the 255th column.  For a given table, the 255th column might change between versions of Timberline if new columns have been added by a new version of the software.  The best way to see where the split occurs is to use an Office Connector wizard.  Select the first table and then click <Next>.  Scroll to the end of the column list and you'll see what the last column name is.  The second table will contain the remaining columns.

How does this apply?
In the example of the JC Job table, there are more than 255 columns.  You might decide to create a query that draws information from JC Job (1) but then want to also include columns from JC Job (2).  To accomplish that, you would add TSLookup functions adjacent to the query of the JC Job (1) table.  The TSLookup functions would lookup values from the JC Job (2) table using the Job number that appears on each row returned by the query.

Details
The table names displayed in Office Connector wizards reflect the user-friendly table captions.  When there is a split table, the table name will appear with a (1) or (2) appended to the end.  For example "JC Job (1)" and "JC Job (2)" would be an example of a split table.  When querying the data, Office Connector uses internal names (otherwise known as dictionary names).  The internal names of tables will reflect split tables using a _1 or _2 suffix.  For example "MASTER_JCM_RECORD_1_1" and "MASTER_JCM_RECORD_1_2" would be an example of a split table.