Microsoft Great Plains Integration Manager ? Working With Text File

Microsoft Business Solutions main middle market ERP application - Microsoft Great Plains has multiple integration options: MS SQL Scripting (stored procedures and views), ADO.Net programming, Microsoft SQL Server DTS packages. You certainly can deploy such SDK tools as eConnect. However here we would like to show you how to program the simplest user friendly tool: Microsoft Great Plains Integration Manager. Multiple times in our consulting practice we saw the need to integrate General Ledger transactions from one text file and here we give you this and even more complex case, when credit and debit amounts are present on the same line with their own account numbers. Let's assume that we have tab delimited text file, GLSOURCE.txt. Here is how the line looks:

"11242004" (date) "11020016000" (debit account) 212446.68 (debit amount) "15260005400" (credit account) 212446.68 (credit amount)

Pretty challenging, isn't it?

Let's begin

First of all and this is probably easy ? you need ODBC DSN, use Microsoft Text Driver, change default directory, select the file and switch to tab delimited type.

Next, open Microsoft Great Plains Integration Manager and create two new queries ? one will be for the Header and the second for distribution lines in GL transaction. In both cases use Advanced ODBC type of text queries.

GL Header should aggregate by date, so in its SQL window enter this:

select F1 from GLSOURCE.txt group by F1

Now the lines ? here we need unionizing and checking if amount is not zero:

select F1, F2, F3, 0 from GLSOURCE.txt where F3'' union select F1 F4, 0,F5 from GLSOURCE.txt where F5''

The one above is the most difficult part of this article. Please understand it ? we are splitting line into two by union, first we create debit part and then we attach credit part, plus we are weeding out 0 amounts.

Next ? we need to link two queries ? simply link them by F1 field (date). Do it in query relationship. When you are done with linking, right click on the arrow, open properties and in Select Relationship Type window change to:

There can be 0 or more records in the child for each record in the master.

This is important ? we'll have more than one line in GL distribution.

The rest should be familiar for IM consultant.

Select destination as GL Journal, assign transaction date and batch ID as F1 from Header query, switch to entries and select them from debit and credit parts of the lines query.

As additional tools in filtering your text query you could use VBA scripting and even translation.

Translation is the way to go when you need to replace account numbers from the text file with different or translated accounts in Great Plains.

Happy integrating! if you want us to do the job - give us a call 1-630-961-5918 or 1-866-528-0577! help@albaspectrum.com

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ? USA nationwide Great Plains, Microsoft CRM customization company, serving Chicago, California, Colorado, Arizona, New York, Texas, Florida, Georgia, Canada, UK, Russia, Europe and Australia and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

---

Microsoft CRM for Corporate Business ? Working Offline

If your company has regional and worldwide operations, you might... Read More

Groupware and Online Collaboration: Collaboration Series #4

This article is the fourth of a series of articles... Read More

The Bluebird Project

The objective for Zandi Digital is to make available clever... Read More

S is for Spying, Surveillance -- and for Software as Well

Words we choose to describe things and phenomena often show... Read More

Reduce TCO: The Java Database Way

TCO (Total Cost Ownership) is the buzzword in... Read More

Brand Your Websites URL With a Favicon

Have you ever noticed that when you look at your... Read More

How To Develop Software For Your Business

Software development is a risky business.Many software developers are barely... Read More

Microsoft Great Plains Dynamics on Pervasive SQL.2000 Data Repair ? Overview For DBAs

Some introduction into Great Plains Software products, now Microsoft Business... Read More

Great Plains Dexterity ? Microsoft Great Plains Customization Overview

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More

Making The Decision To Automate Your Software Testing

Not every software testing project can or should be automated.... Read More

Microsoft Great Plains Implementation: Collection Management ? Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

Can Group Collaboration Software Meet Business Needs?

According to a survey conducted by InfoTrends/CAP Ventures entitled "Content-Centric... Read More

Assertion in Java

Assertion facility is added in J2SE 1.4. In order to... Read More

Windows Screensavers Explained

In this article you will find some background information about... Read More

How to Make Attractive and Effective PowerPoint Presentations

Microsoft PowerPoint has dramatically changed the way in which academic... Read More

Microsoft Business Solutions - Navision Customization: C/SIDE, C/ODBC, C/FRONT, XBRL

Microsoft bought Navision, Denmark based software development company, along with... Read More

5 Time-Saving Tips in Microsoft Word

Whether you have used Microsoft Word for years, have just... Read More

Great Plains Dexterity Development: FAQ

Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More

Microsoft Great Plains eCommerce: overview for developer

Microsoft Business Solutions Great Plains was designed back in the... Read More

Microsoft CRM Customization

Microsoft CRM customization techniques are very diversified and based on... Read More

Microsoft CRM ? Typical Customizations

Microsoft CRM was designed to be easily customizable. Microsoft CRM... Read More

Intro to UNIX Shells

A UNIX Shell is in simplest terms, a command line... Read More

Begun, The Browser Wars Have

As Mozilla Firefox nears 10% market share, with well over... Read More

What You Should Know About Installing Screensavers

Do you remember that frustrating feeling when you find an... Read More

Unofficial Windows 98 SE Service Pack 2.0 RC2

Microsoft has never released a service pack for Windows98 SE,... Read More

Great Plains Dexterity Customization Options ? Overview For Developers

Looks like Microsoft Great Plains becomes more and more popular,... Read More

How a Bug Challenges to a Software Professional

It is really interesting that a bug can create problem... Read More

Recovering Microsoft Great Plains Customization ? Tips for IT Director

Remember nice and prosperous Clinton era? When you implemented innovative... Read More

How to Get The Best Accounting Software For Your Small Business

Buying accounting software is a major investment. It's an important... Read More

Microsoft Great Plains in Metal Distribution: Implementation & Customization ? Consultant Overview

Microsoft Business Solutions Great Plains serves to the wide spectrum... Read More

10 Steps To Secure And Manage Your Passwords

Passwords protect your most sensitive personal, financial and business information.... Read More

OS 101: Suggestions for Choosing an Operating System

An operating system (abbreviated OS) is essentially the path through... Read More

What to Do if All Screensavers Fun is Grayed Out?

Finally, you have some time to personalize your desktop with... Read More