Visual Integrator FAQs

Click a question below to view the answer. For more information about importing, see Visual Integrator Import FAQs.

How do I match related fields when linking ODBC tables?

When linking a table to another table, you must specify fields in both tables that are related. Typically, but not always, the names of the two fields are identical (for example, in the above table, Invoice.CustomerID is linked to Customer.CustomerID). Normally, you will use the Equal (=) relation, so that the value of one table matches the other exactly. You should only use other relations, such as Not Equal (<>) and Greater Than (>), if you fully understand how SQL joins work.

When matching fields from one table to another, make sure that the data types of the two fields are the same. For example, you cannot match a field that contains numeric data to one that contains date or text information. If you are unsure of the type of data contained in the field, click the Browse button on the ODBC Table Selection Fields tab to display the content of a specific data field.

How are ODBC tables sorted in Export Job Maintenance?

Normally, an ODBC database has sorting information defined for its tables. As information is exported to the database, the predefined indexes are updated automatically. The sorting information defined on the Export Job Maintenance Sort tab has no effect when exporting to an ODBC table with predefined indexes.

If the ODBC table has no predefined indexes and no key fields are identified, all exported data is inserted into the table in the order specified on the Sort tab.

How are selected records sorted in ODBC Table Selection?

When only a single table is specified, the selected records are retrieved in the natural order of the table. For example, if the customer table is defined in the database to be ordered by customer ID, the Visual Integrator module processes the import data in customer order. If multiple tables are linked together, the retrieved data is sorted by the first field of the primary (first) table.

What functions are available for building expressions?

A large selection of numeric and string functions are available. A list of available functions appears in the Expression Builder window, along with a sort description. Functions consist of a three-character code followed by an open and close parenthesis. Selecting a function and selecting a field will insert the field name inside of the function's parentheses automatically. The variable or the expression must be either a numeric or string type, depending on the function.

The function also returns either a numeric or string value.

Examples - Numeric functions

The following functions return a numeric value.

Function	Return
ABS({AR.CUST.HIGH_BALANCE})	Returns an absolute value of a numeric expression (for example, ABS(-100) returns 100)
INT({AR.CUST.SALES_MTD})	Returns the integer value of a numeric expression (for example, INT(123.45) returns 123)
NUM({AR.CUST.PRICE_LEVEL})	Returns the numeric value of a string expression (for example, NUM("123.45") returns 123.4

Examples - String functions

The following functions return a string (text) value.

Function	Return
LCS({AR.CUST.ADDRESS1})	Returns the lowercase letter text of a string expression (for example, LCS("ABC") returns "abc"
UCS({AR.CUST.ADDRESS1})	Returns the uppercase letter text of a string expression (for example, UCS("abc") returns "ABC"
STR({TEMP01})	Returns the string value of a numeric expression (for example, STR(123.45) returns "123.45")

How can I change the validation logic?

Note The information in this topic applies only to Job Cost, Payroll, Material Requirements Planning, Work Order, and TimeCard.

When a job is created, the default set of validation logic is assigned automatically. The default validation logic can be changed or removed from the Import Job Maintenance Validation tab. Any change or removal of default validation logic must be made carefully or invalid data may be imported into the data files, which can cause application programs to error.

Warning Changing or removing default data validations can cause data corruption and may compromise the integrity of your accounting system.

Removing a validation logic should be done only if the default logic is preventing data from being imported that is otherwise correct.

You can add validation logic in addition to the default logic without risk; however, incorrect validation logic may prevent you from importing data that is otherwise correct.

Can I execute a job from an external application?

You can execute a Visual Integrator job directly from another application. Microsoft Office products, such as Access and Excel, allow you to use the Visual Basic script language to execute external programs. Refer to the technical reference manual for the external product on how to execute external programs using their script language.

For more information, see Create a Shortcut to a Job.

What are header records and detail line records?

Most data files with multiple record types are organized into header records and detail line records. Header records contain information common to a group of records. The detail line records contain additional information relating to the associated header record. To view information for data files in all modules other than Job Cost, Payroll, Material Requirements Planning, Work Order, and TimeCard, click the File Layout and Program Information link on the Desktop's Resources page

The Data Dictionary Listing also provides a list of data items and indicates whether each item is a header record or line detail record item.

Note The Data Dictionary Listing is available only for the Job Cost, Payroll, Material Requirements Planning, Work Order, and TimeCard modules.

When you are importing data for a header/line file, you must first determine whether the import file contains the necessary header information in a separate record, or if the header information is included with each record in the file. If the header information is included with each detail record, you must select the Header Information Included in Detail Line check box on the Import Job Maintenance Configuration tab. If you clear the check box, it is assumed that the header record information is contained in a separate header-type record.

If the Header Separate From Line Record check box is selected, you must specify the record selection information on the Import Job Maintenance Records tab. A header-type record is displayed, along with any other record types defined for the data file. You must select the Header record in the same manner as any other record type.

For more information, see Data File Structure.

Example - Selecting header-type data items

The Accounts Receivable Invoice Data Entry file consists of header and line detail records. The header record contains information relating to an invoice as a whole (for example, invoice number, customer number, terms code, and salesperson). The line detail record contains additional detail information consisting of the item type, description, price, quantity, etc. The line detail records consist of three record types: Miscellaneous Items, Miscellaneous Charges, and Comment lines.

What do the results displayed in the Import Summary or Job Summary mean?

In the Import Summary window, the import summary information represents the following:

Records Read represents the number of records processed from the import file.
Records Imported represents the number of records that were selected for import that had no invalid information. In a nontest import, these records would have been imported into data files.
Records Skipped represents the number of records skipped, based on record selection criteria.
Invalid Records represents the number of records not selected for importing due to invalid data.

In the Job Summary window, the summary information represents the following:

Records Read represents the number of records processed from the import or export file.
Records Passed represents the number of records that were selected for import or export that had no invalid information. In a non-test import or export, these records are imported or exported into data files.
Records Skipped represents the number of records skipped, based on record selection criteria.
Records Failed represents the number of records not selected for importing or exporting due to invalid data.

Note If a record contains a required field that fails assignment, the record fails and the log displays a warning message in red. If only non-required fields fail assignment, the record is written and the log displays "Passed with Invalid Fields" in blue. For example, a record is written even if it contains a non-required field that fails because it contains invalid data or cannot be assigned because a module required to support it is not integrated.