Data Import

EZImport provides a set of tables that mirror Dynamics GP transaction tables and also provides inquiry windows and SmartList Builder objects that look at those tables.

None of the data in the EZImport tables is validated against GP setup. This means that you can import legacy Customers, Items, Units of Measure, and so on, without worry that it will be kicked-out by GP as invalid data.

Each table has the smallest number of Key Fields possible, and each table has only one key. To print a list of columns in each table, use the Table Resources window inside of Dynamics GP.

To access Table Resources go to Tools >> Resource Descriptions >> Tables. Click the … button to the right of the “Table” field.

For Product select EZImport.

Series will depend on which EZ series you want to print. Sales Transactions are in Sales, Purchasing are in Purchasing.

Double-click on a table name. This will return you to the Table Descriptions window, with information displayed for the selected table. Click the Print button to print a listing of the columns.

Each table will closely mirror the GP table that stores similar information. For example, SOP10100/SOP30200 (Sales Headers) has an EZImport mirror table called EZSOP10100. Most of the columns will be identical between the two tables.

Each EZImport table will have an additional array of string fields as the right-most columns in the table. You can use these fields to store additional legacy information that does not have a good place in an existing column.

Aside from the Key Fields in each table, all of the columns are created to allow NULLS. This allows you to import directly into the tables by specifying only the fields you want to populate. All other fields have default values (such as 1/1/1900 for a date, or 0.00 for a numeric field).

For example, a simple insert statement to copy data from SOP10100 into EZSOP10100 could be written as follows:

insert into EZSOP10100 (SOPNUMBE, SOPTYPE, CUSTNMBR)

select SOPNUMBE, SOPTYPE, CUSTNMBR from SOP10100

All other fields in EZSOP10100 will populate automatically with defaults/nulls.

PRO TIP: Although the EZImport tables mirror GP tables (i.e. EZSOP10100 is a duplicate of SOP10100), you do not need to use the fields in the EZImport table in the same manner as the GP table. For example, perhaps the legacy system has a Sales Order Revision Number field, which does not exist in GP. The Revision Number for the legacy system could be stored in ANY compatible field in the EZSOP10100 table, such as the ORIGTYPE or ORIGNUMB fields.

EZSOP Tables

All EZSOP tables have 5-string fields that are not found in the corresponding SOP tables. These fields are provided to store additional information.

EZSOP10100: Sales Headers

EZSOP10103: Sales Payments

EZSOP10106: Sales User Defined

EZSOP10107: Tracking Number

EZSOP10200: Sales Lines

EZSOP10201: Sales Line Serial/Lot

EZSOP40100: SOP User Defined Field Labels

EZSOP60100: SOP-POP Links

EZPOP Tables

Each table contains 5-string fields that can be used for storing additional information.

The POP Comments Table (EZPOP10150) is linked to both the header (EZPOP10100) and the lines (EZPOP10110). To link to the PO Header comment, add a record to EZPOP10150 with the PO Number and set ORD = 0 (zero). For PO Line Comments the key is PO Number and Ord, where Ord matches the PO Lines ORD field.

Data Manager

HelpID: 563122006

Navigation: Tools >> Utilities >> Company >> EZImport Data Manager