Show/Hide All

 Overview

Often, the information you need to import may be contained in more than one table. When importing from an ODBC data source, you can link several tables together and import data contained in any of the linked tables. You must first select the tables to import on the ODBC Table Selection Tables tab, and select each data field to import on the Fields tab.

After the tables and the data fields are selected, you must then logically link the tables together, so that related information from all of the tables can be imported at the same time.

When linking multiple tables, you must determine which table to link first. The table that you specify as the "from table" on your first link is called the primary table. The primary table determines how many records are processed and provides a basis from which to link other tables.

 

To link multiple tables

1. Select Visual Integrator Main menu > Import Job Maintenance.

2. In the Import Job Selection window, enter a job name and table name, and then click Accept. For more information, see Import Job Selection - Fields.

3. In Import Job Maintenance, in the File Type field, select ODBC Data Source. For more information, see Import Job Maintenance - Fields.

4. In the ODBC Table Selection window, on the Tables tab, select the tables to import. For more information, see ODBC Table Selection - Fields.

5. Click the Fields tab. Select the fields to import.

6. Click the Links tab. In the From Table field, select the table to link first. This table will be the primary table. Select the field to link in that table.

7. In the To Table field, select the table to link to. Select the field to link in that table.

8. Click Add Link to add the defined link to the list of links in the list box. Click Del Link if you need to delete a link from the list box.

After you have linked the tables, you can define additional links based on the data fields in the tables.

 Example - Linking multiple ODBC tables

To link one table to another, you must define a relationship between a data field from one table to a data field in another table.

Assume that you want to import data from two tables (for example, a customer table and an invoice table) containing customer information. You must define a link between the Customer_ID field in the customer table and the Customer_ID field in the invoice table. This allows the Visual Integrator module to import data from both the customer table and the invoice table in a logical manner. If the link information is not provided, the invoic e information retrieved will not match the customer information.

 

 Example - Determining the primary table

If you are importing customer information, the primary table will be the customer table. You can then link other tables using the data fields contained in the customer table (salesperson table, terms code table, etc.). The following table illustrates the link information.

From Table	Field	Relation	To Table	Field
Customer	TermsCode	Equal (=)	Terms	TermsCode
Customer	SalespersonID	Equal (=)	Salesperson	SalespersonID

If you are importing invoice information, the primary table will be the invoice table and customer table will be linked using the customer ID. After the customer table is linked to the primary table, you can define additional links based on the data fields in the customer table.

From Table	Field	Relation	To Table	Field
Invoice	CustomerID	Equal (=)	Customer	CustomerID
Customer	TermsCode	Equal (=)	Terms	TermsCode
Customer	SalespersonID	Equal (=)	Salesperson	SalespersonID