Friday, October 24, 2008

The often overlooked, yet powerful Table Import

Before Integration Manager, eConnect, Web Services, SnapShot, SQL Server Data Transformation Services (DTS), SQL Server Integration Services (SSIS), or any of the supercharged, techno-geek tool you can quickly think of, there was Table Import. Considered by many at the bottom of the food chain when it comes to integration tools, the fact is, it still holds its weight in today's XML-plagued world.

Background

In past releases of Microsoft Dynamics GP, Table Import was an absolute best (and fast!) approach to import data, especially because tools like the ones mentioned above have never been able to cover the entire spectrum of Microsoft integrating products and third party applications available. Take for example Manufacturing or Field Service. While these products have long been around, there's little in the form of tools that can actually get data into their tables in a safe and validated way.

You may be thinking or asking, "well, how do I know what tables I need to import data into?". The fact is, Table Import does require an understanding of GP's table structures and their relations -- this includes all integrating solutions too! However, Microsoft has put great emphasis in providing Software Development Kits (SDKs) that outline these tables and their columns, and in particular what values are required to be passed in a record for it to be valid.

Table Import Overview

The following is an example that will import a few customer records using the sample records in the Customer.txt file under the Integration Manager samples directory.

1) Open Table Import. Go to Microsoft Dynamics GP > Tools > Integrate > Table Import. This will open the Table Import Definition window. One advantage of the tool is its ability to save import definitions. For this example, I will use CUSTOMER.

2) Select a Source File Format. Table Import supports files that have been formatted as comma-delimited or tab-delimited. The sample Customer file is a tab-delimited file.

3) Choose the Source File. Click on the folder button, then locate the file to import. The source file for this example can be located under:

C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples\Customer.txt

4) Select a Destination table. Click on the Ellipses button to open the Chose a Table window. For this example, we will select the RM Customer MSTR table. All columns in the table will be displayed in the scrolling window.

NOTE: Knowing your tables in any product will facilitate this process.

5) Map each source column in the file to the destination column in the table. Highlight each row, then click on the ellipses button next to the Source prompt on the scrolling window header to select a column from the source file. If you need to map a constant value, enter it in the Constant field on the window, then click Add.

NOTE: For the customer class, I will use the constant USA-ILMO-T1.

Before processing the import, the definition window will look like this:





















6) Process the import. Lets go ahead and click on Import to bring in our records, choosing to Save when prompt to save our import definition.










Table Import will provide a status of the import while creating a rejection file. The rejection file contains the records that could not be processed and can be used to re-import the exceptions.

7) Run Check Links on the appropriate tables to build any missing records in related tables. This is quite critical, since most tables are inter related. The check links process will attempt to build those missing references.

















The above illustration shows a check links executed after the import. In this case, the customer summary records and address record have been created.

Summary

Table Import can be an effective way for the end-user with some tech savvyness to get some data quickly into GP. Don't let the overwhealming amount of tools out there shy you away from using it, especially when these tools are not able to address parts of the application you are interested in integrating data into. Be cautious of the limitations -- data validation being one of them -- and arm yourself with all information possible before attempting any data import. Be sure to validate your data externally and apply common sense to ensure a safe import. Run check links and reconciliation where possible and if provided by the ISV or if importing into standard GP tables. But be sure to check the following resources.

NOTE: if importing data into third party tables, be sure to work closely with the product's ISV. They are better equipped to guide you and help you through the process.

Software Development Kit (SDK) Resources

Microsoft Dynamics GP

Microsoft Dynamics GP v10 Software Development Kit -- PartnerSource, CustomerSource
Microsoft Dynamics GP v9 Software Development Kit -- PartnerSource, CustomerSource
Microsoft Business Solutions - Greate Plains 8.0 SDK -- PartnerSource, CustomerSource

Field Service
Software Development Kit (SDK) for Field Service 8.0 -- PartnerSource, CustomerSource

Manufacturing
Manufacturing Order Processing SDK for Great Plains 8.0 -- PartnerSource, CustomerSource

Other Useful Resources

Many of my fellow MVPs have also blogged at some point on table integrations:

David Musgrave, MSFT. David recently published an article with 14 different ways of obtaining table information in Dynamics GP. This article also includes links to other blogs were this topic has been discussed.

Victoria Yudin, MVP. Check her series of articles on GP Reports. Victoria provides complete details on some of the tricky flags that exists in some of the GP tables, to be considered when importing more sophisticated data, such as Sales Orders, Purchase Orders, etc.

Mark Polino, MVP. Mark has a posted a few downloads on his DynamicAccounting.net blog page. Take a look at his GP 10 Table Reference and GP 9 Table Reference Microsoft Excel files.

Former MVP, Richard Whaley continues to deliver some of the best books in the market on everything GP. In particular, you won't want to miss the Information Flow and Posting title. If you are interested, just click on the Accolade Publications link on the right of my blog.

[10/27/2008 - UPDATE] Tools Resources
This section has been added to include other tools that allow users to import/export data into GP, but that provide table information as well:

SnapShot - Click here to download SnapShot from David Musgrave's blog site. SnapShot works by copying the contents of selected tables to Ctree files in a separate folder, thus creating a SnapShot of the data. This separate folder can then be copied to a target system and the data inserted back into the actual tables.

Support Debugging Tool - I have written extensively in the past about this debugging tool. SDT has an XML import/export feature that allows users to export the data of a table into XML format and reimport it back. Click here to find all links to download SDT.

I will continue to update this article with more and more resources, so be sure to check regularly. However, feel free to submit your own resources by dropping a comment with a link to them.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

19 comments:

Anonymous said...

I agree with you but i tend to get scared esp. for fixed assets because i dont know why the move was made away from table import and import utility and now integration manager with econnect has been created for fixed assets...is there an advantage to using the two mentioned over the table import for fixed assets?

Anonymous said...

Nice post. I just imported customer and vendor master, address and class data and it worked like a charm. Thanks for the reminder to check links.

Anonymous said...

How could I set up a table import for payables?

Mariano Gomez said...

By using the table import guidelines outlined in the Microsoft Dynamics GP Software Development Kit.

MG.-

Anonymous said...

I understand, thanks, One more question, if I wanted to use the example file in C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples\Pmtrx.txt what destination table would I use?

Anonymous said...

Any ideas Mr. Gomez, I am pretty new at this and we do not have IM installed so I figured we might be able to do this with the table import feature.

Mariano Gomez said...

@Anonymous,
I don't mean to sound repetitive, but this is all described in the Microsoft Dynamics GP Software Development Kit. According to the SDK Table Integration import guidelines for Payables Management Transactions, you will need to consider the following tables:

BATCH_HEADERS
PM_TRANSACTION_WORK
PM_KEY_MSTR
PM_DISTRIBUTION_WORK_OPEN
PM_TAX_WORK
MC_PM_TRANSACTIONS
VAT_INTRA_WORK

Note that you may or may not need all these tables depending on what information you are trying to import.

Once again, download and install the SDK (it's also available under the Tools folder of the Microsoft Dynamics GP installation media.

If you need further assistance, I suggest you contact Microsoft.

MG.-
Mariano Gomez, MVP

Gina said...

Hi Mariano, I use Table Imports quite a bit, especially for Project Accounting imports. However, the definition is only saved in the workstation that the definition was originally created. How do you copy over the definition to other workstations?

Mariano Gomez said...

Gina,

Copy the SY50000.* and SY50100.* Ctree files from your MS Dynamics GP Data subfolder to the other workstation in question, under the same Data folder.

That should do,

MG.-

Anonymous said...

Worked like a charm. Thanks, Mariano!

punugu said...

Hi Mariano,

I remember a tool available from Microsoft which will clear all transactions and keep the module setup and masters. Do you know what is the name of the tool? Is it available for GP version 10, 2010 and 2013? I remember I was using on GP 8 or GP 10 few years back.

Thanks,
Mohan
punugu@gmail.com

Anonymous said...

Always love your posts!! One question- there any way to change the location of the RJT file?

Heidi Jensen said...

Mariano,
I set up the table import for a user who did not have access initially to the Table Import window. I granted her security to the Integration Site Enabler window but she receives the message "Table Security Violation" when clicking Import on the window.

What did I miss? Is there something in SQL I need to adjust?
Thanks!!
Heidi

Mariano Gomez said...

Heidi,

Most likely, you need to run the GRANT.SQL utility script to grant that user access to the actual physical table in SQL. You can find the script under the SQL\Util folder in your Microsoft Dynamics GP installation folder.

MG.-
Mariano Gomez, MVP

Heidi Jensen said...

Thanks Mariano, but dumb question - how do I run the Grant script against a specific table? I do have the script, just not sure how to run it against a specific table. Do I need to change the script?
Thanks so much!

Mariano Gomez said...

Heidi,

You can type the following in a SQL Server Management Studio query window:

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO DYNGRP;

Good luck,

MG.-

Heidi Jensen said...

Thanks Mariano, I was able to successfully run the Grant script you provided on the table, in all of the company databases, however the user still receives the same message.

Any other ideas? :)

Mariano Gomez said...

Heidi:

Setup a DEXSQL.LOG. This should show you what object you are having problems with.

MG.-

OSr Group said...

Nice post. I agree with all your points.
Plywood Manufacturer in India