Show/Hide All
Click a question below to view the answer.
Visual Integrator is a sophisticated tool that allows you to easily create import functions to import data from external software packages into Sage 100 data files. Data can be imported from word processing programs, databases, spreadsheets, and other accounting software products.
You must determine which file type is appropriate for the data to be imported. If your source file is not in one of the available formats, you must create a temporary transfer file using one of the supported formats.
You must determine which data file is the appropriate destination file for the information you want to import. The Data Dictionary Listing lists all data files for the Material Requirements Planning and Work Order modules. To view information for data files in all modules, click the File Layout and Program Information link on the Desktop's Resources page.
You must know where each piece of data you want to import is located in the source file. For example, if you are importing an ASCII text file, you must know the exact location of each character of each field; if you are importing a spreadsheet file, you must know the column location of each field, etc.
Some data files can store information in two or more record types. If you are importing files with multiple record types, you can identify each record type and match it to the corresponding record type for the destination file.
Certain data items in each data file are designated as key fields. These fields are used to uniquely identify each record in the file. You must either import or assign a unique value to these data items to ensure that the record will be properly written to the file. Any data item that is not imported or assigned a value will be automatically set to blank. You can identify key fields by printing the Data Dictionary Listing for the Job Cost, Payroll, Material Requirements Planning, Work Order, and TimeCard modules. To view information for data files in all modules, click the File Layout and Program Information link on the Desktop's Resources page. Each data item that is a key field will display YES in the KEY column.
The following FAQs apply to import jobs for all modules.
The fields selected on the Import Job Maintenance Data tab may be mapped to the wrong fields in the data source. Verify that all the data fields are mapped to the correct source fields using Import Field Properties.
The application used to construct the source file may not save to these older formats properly. When setting up an import for an Excel file, consider saving the file using the CSV format. On the Import Job Maintenance Configuration tab, select Delimited in the File Type field and enter a comma as the delimiter character.
The following FAQs apply only to import jobs for modules other than Material Requirements Planning and Work Order.
If batch processing is not enabled for the module, no batch number is required for the import job. A default batch number of 00001 is assigned to the record by the business object.
If batch processing is enabled for the module, you can either:
Import a blank field and allow the business object to assign the next batch number defined in the module's Options window (the batch number will be used for the entire import).
Import a batch number.
If you import a batch number, the batch number must be the same in each record imported for the records to be included in that batch. If the batch number changes in the import record, a new batch number will automatically be created if it is not already in the Batch file.
Temporary fields allow you to manipulate data. Sometimes the data comes from the import source record, sometimes it comes from the record being assigned, and sometimes it comes from some other external source, such as a file assign. In some circumstances, the Tempxxx variable must be placed after certain fields have been assigned, but before other fields have been assigned. The Assign Temp On field in the Import Field Properties window allows you to control exactly where the Tempxxx variable is placed.
Visual Integrator assigns values to the record in three stages:
The field or fields that make up the key to the record are assigned. Visual Integrator uses the Col/Pos number you defined during job setup to determine the order of assignment, but only for the fields being assigned.
The remaining fields in the header or standard record are assigned in the order of the Col/Pos number you defined during job setup.
The line fields are assigned to the line record if the import is a header-line table pair.
The Assign Temp On field allows you to specify during which of these three stages the Tempxxx field is assigned. Usually, all temporary fields can be assigned with the key fields (stage 1), so that the value assigned is available for all remaining assignments (stages 2 and 3). In some cases, however, making the assignment too early can result in a null value, because the expression assigning the Tempxx field relies on previous assignments having been made. Thus, it is important that you take any such dependencies into consideration when selecting an option in the Assign Temp On field.
When importing into CI_ITEM and the source file contains the item description that is greater than 30, the extended description is automatically updated to CI_ExtendedDescription if the Skip Data Truncation check box is selected in the Import Job Maintenance Header tab. If this check box is cleared, only the first 30 characters are imported.
Expire_Year field values will fail validation if they are presented in a two-digit format. The system requires that the values be presented in a four-digit format (for example, 2010). To correct this problem, edit the source file using a four-digit format for the Expire_Year field.
The following FAQ applies only to import jobs for the Material Requirements Planning and Work Order modules.
Record perform logic is required to create batch numbers for import jobs. Verify that the correct perform logic is set up, and that the record is defined.
On the Import Job Maintenance Records tab, double-click the record.
In the Record Properties window, verify that the record type is defined correctly.
Click the Perform button.
In the Record Perform Logic window, verify that the correct perform command appears in the Command list box. If is does not, click the Lookup button in the Command field and select the correct command for the file being imported. For example, if you are importing into WO.TRANSENT, select the command for WO.TRANSENT.
It may be necessary to remove the record from the Records tab and then redefine it, including the perform logic command.
