I have been asked many times about integrating data using GP Power Tools. While this is not what GP Power Tools – Developer Tools module was originally designed to do, it can be used to integrate data (for both adding and updating records).
The main reason for writing this article and creating the updated project was because a friend asking for a quick method for updating Item List Price information because their suppliers are providing new pricing information on a weekly (or less) basis.
The IMPORT_EXAMPLE Project take an original example script and massively improves on its capabilities to make it really simple method to integrate data for Accounts, Customers, Vendors and Items from a CSV (Comma Separated Values) text file. The file can be edited with Excel or your favourite text editor.
The original example I created imported 4 fixed columns for customers (Customer ID, Customer Name, Short Name and Statement Name). The source CSV file had no header row and if you wanted to add an extra column you would need to change the script to define what the extra column data will be. This example is contained in the IMPORT_EXAMPLE Runtime Execute Script in the project for reference only.
Disclaimer: The code in these import examples does not include full business logic to validate all the data entered. It is up to you to ensure that the data imported is valid.
Starting with this original example, I decided to change the code to allow the columns to be imported to be controlled by a header row in the CSV file. The header row is read and the field names validated against the Dexterity Technical Names to check they exist in the table. Then when the data is read, it will import the data and convert it based on the field’s datatype.
To add extra columns, you can just add the field names to the header row and add the data for the columns in the following rows. To get the actual field technical names, you can use the Resource Information window. You can either use it in Form, Window and Field mode and let it identify the fields as you move around the window, or in Table and Field mode to open the table Explorer to show all the columns in the table.
Note: Make sure to open the Resource Information window BEFORE opening the window you want to work with and enable the Show currently selected option to get the “following” functionality to work.
The project includes scripts for importing Accounts, Customers, Vendors and Items and also has triggers to add an Additional menu option to the Account Maintenance, Customer Maintenance, Vendor Maintenance and Item Maintenance windows. The attached archive file includes the project and sample CSV files for each importer.
Below is a summary of the components of the IMPORT_EXAMPLE project:
Accounts
The IMPORT_ACCT trigger calls the import script IMPORT_ACCT and can be used with the IMPORT_ACCT example CSV file. It supports all fields in the GL_Account_MSTR table.
Customers
The IMPORT_CUST trigger calls the import script IMPORT_CUST and can be used with the IMPORT_CUST example CSV file. It supports all fields in the RM_Customer_MSTR table and the 8 Internet Information fields associated with the primary Address ID. Customer Class can be imported to set default values.
Vendors
The IMPORT_VEND trigger calls the import script IMPORT_VEND and can be used with the IMPORT_VEND example CSV file. It supports all fields in the PM_Vendor_MSTR table and the 8 Internet Information fields associated with the primary Address ID. Vendor Class can be imported to set default values.
Items
The IMPORT_ITEM trigger calls the import script IMPORT_ITEM and can be used with the IMPORT_ITEM example CSV file. It supports all fields in the IV_Item_MSTR table (including the 6 User Defined Category fields), the 8 Internet Information fields and the List Price in the ivItemCurr table. Item Class can be imported to set default values.
It also can import the 6 additional User Defined Category fields added with IV CATEGORY Project, see below:
Important Notes
- GP Power Tools Build 28.9 (or Build 28.0 for v18.4) or later is required for these examples to work as they use Helper Functions only added in those builds.
- The key fields for each of the imports needs to be at the start of the columns on the CSV file and the column name in the header row must have an asterisk character added to the name.
- Field names can have single or double quotes around them, they will be removed automatically.
- The Class field should be early in the field list as it will overwrite table values from the class for a new record.
- The Note Index will be populated with the next index value as required.
- The Created Date and Last Modified Date information is updated automatically.
- The importers will also create/update supporting tables such as summary tables as required.
- The importers currently support importing up to 100 columns. This can be extended if required.
- The IMPORT_SUB and IMPORT_CLEANUP scripts are shared code which is used by all of the importers. It provides a single location to maintain the script and decreased the size of the scripts to allow more functionality to be added.
Downloading and Installing
Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):
The code will be active on next login or after switching companies, or you can start the triggers manually from the Project Setup window.
More free customizations on the GP Power Tools Samples page:
Feedback received so far from beta testers of this code has been very positive.
Enjoy
David
10-Jun-2022: Updated so that the importers can handle commas in the numbers if they are formatted.
12-Jul-2022: Updated to strip non-numeric characters from numbers and currency values.
01-Mar-2023: Updated the Item Importer to allow the importing of the Last Originating Cost field on the Item Vendor Maintenance window.
This article was originally posted on http://www.winthropdc.com/blog.
Winthrop Development Consultants Blog 