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 Great Plains eCommerce ? Stored Procedures Approach

Since Version 8.0 Microsoft Business Solutions Great Plains & Great... Read More

Microsoft Navision and Crystal Reports - An Overview

Microsoft Business Solutions ? Navision is an integrated solution for... Read More

Spyware Protection: A Must for Any Home Computer

Now that spyware is the single most dangerous threat to... Read More

These Items Are A Must Before Making The Decision To Purchase Any Off-The-Shelf Software

1. What determines the software price? Is it Per Seat... Read More

Crystal Reports - Microsoft SQL Server

Microsoft SQL Server is the leader for inexpensive and middle... Read More

Data Quality Best Practices for Salesforce.com

Executive SummaryAn effective plan for entering, cleaning and updating the... Read More

Exactly What Does FTP Stand for and What Can it do for Me?

FTP stands for "file transfer protocol". FTP is basically a... Read More

Perfectly New Database Query Tool - Foxy SQL Pro Released

Are you a database professional? Do you work with a... Read More

Microsoft Great Plains Food Processing ? Implementation & Customization Highlights

Microsoft Great Plains might be considered as ERP platform to... Read More

Do You Want Your Own Fully Programmable ERP? - Part 1

We are in a transition phase in the Managerial Administration... Read More

Make or Break Factors - When Considering Estimating Software

Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More

Anti-Spyware Protection ? Holes in the Shining Armor

Looking at all the ads which promise to get rid... Read More

About Microsoft Sysprep (System Preparation) Utility (Windows 2000)

The Microsoft system preparation utility for Windows 2000 is a... Read More

How to Backup Windows XP Home Edition

Your computer cost you from hundreds to thousands of dollars,... Read More

The End of Spyware?

The US House of Representatives has recently passed the "Spy... Read More

Freight Forwarding ERP: Microsoft Business Solutions Great Plains customization & setup ? overview

Microsoft Great Plains may be recommended for international freight forwarding... Read More

Start Developing Your Own Software

Learning how to program software seems like a daunting task... Read More

Most Dangerous Types of Spyware are on the Rise: How to Choose the Weapon

Bad News - the Threat is Bigger than it SeemedHow... Read More

3 Reasons Why Medical Billing Software is Leading the Way

Since technology changes so quickly, it is hard to begin... Read More

IT Department Skills to Support Microsoft Great Plains and Microsoft CRM

Microsoft Great Plains as ERP and Microsoft CRM as... Read More

C++ Tutorials: 3, Program Flow (If, Else, While, For)

Program Flow is what you think it is. How the... Read More

Integrating Microsoft Great Plains Accounting/ERP: RMS, CRM, eCommerce, Lotus Domino ? overview

Microsoft Business Solutions Great Plains has substantial market share among... Read More

Are You Waiting for Microsoft Longhorn Operating System

I love new technology. I am still ready to wait... Read More

Fleet Maintenance Software Reviews

Innovative Maintenance Systems (IMS) is one company that offers solutions... Read More

Increase Office Efficiency With One Simple Tool

When you need a phone number, you do a quick... Read More

SQL: Querying Microsoft Great Plains ? Overview for Database Administrator/Developer

Looks like Microsoft Great Plains becomes more... Read More

Microsoft Great Plains in Construction & Building ? Implementation & Customization Highlights

Microsoft Great Plains could be tuned and setup to fit... Read More

Save Your Resources - Combine Your IM Clients

There are so many different programs that clutter up your... Read More

Dont Choose Adobe When Working With PDF

While Adobe is the most known maker of PDF tools,... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability... Read More

Software Upgrades Arent Always the Best Move

When my daughter was getting into AOL instant messaging (AIM)... Read More

Microsoft Great Plains ? Licensing & Product Versions

Current Microsoft Business Solutions Great Plains has more that 10... Read More

Microsoft Great Plains: Getting New Users Licenses ? Annual Service Plan FAQ

Microsoft Business Solutions Great Plains, Solomon, Navision, Axapta, Microsoft CRM... Read More