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.
Culver prom limo ..The fact that a software tester is a most infamous... Read More
A LOT OF UNWANTED FILES.When you uninstall an item of... Read More
It is now common thing when large corporation selects mid-market... Read More
Need help making sense of algebra? Have algebra lectures in... Read More
Microsoft has never released a service pack for Windows98 SE,... Read More
eStore Advantage allows front-office applications to communicate with back-office business... Read More
Current Microsoft Business Solutions Great Plains has more that 10... Read More
I have recently created my first Php program. I wanted... Read More
Should one use Windows Update?This topic has good and valid... Read More
Each Industry and market niche has business specific and unique... Read More
After seeing many people complain about their weak Internet security... Read More
Microsoft Business Solutions Great Plains has substantial market share among... Read More
Not all of us have the luxury of working both... Read More
It won't matter how effective your WinRunner Team is if... Read More
Remember nice and prosperous Clinton era? When you implemented innovative... Read More
Are you a whiz at calculating financial information? Not the... Read More
It's no secret that software companies operate in a very... Read More
There is many things more frustrating than surfing a website... Read More
Once a business idea is selected, it is highly recommended... Read More
Microsoft Great Plains may be recommended for international freight forwarding... Read More
An operating system (abbreviated OS) is essentially the path through... Read More
All of us know that Microsoft bought former Great Plains... Read More
A wiki is an editable text-based website. But you don't... Read More
Several years ago Microsoft purchased Great Plains Software, then Navision... Read More
When reading an article where some term is used often,... Read More
Wood Dale limo ..Microsoft Business Solutions Great Plains fits to majority of horizontal... Read More
What is Software?Software is a set of instruction written to... Read More
Microsoft Great Plains and Microsoft CRM become more and more... Read More
Microsoft Business Solutions Great Plains serves multiple industries in the... Read More
Sales are all about leverage, because there is only so... Read More
There are several kinds of software piracy. The bottom line... Read More
Microsoft Great Plains, former Great Plains Software Dynamics, eEnterprise has... Read More
Icons are used everywhere; right from software applications, to internet... Read More
Some companies that are in need of fleet management may... Read More
An integral part of any quality CRM system is lead... Read More
Although statistics often is blamed for various deadly sins --... Read More
The research in the field of Natural Language Processing usually... Read More
Introduction: The creating of a computer program involves a number... Read More
Whether you need to close a sale, gather end-user feedback,... Read More
MicroWorld Technologies, Inc. the leading solutions provider in the area... Read More
Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More
1. Replace Dexterity cursor with SQL Stored Procedure Dexterity... Read More
Mike Dunville* had a decision to make. As the new... Read More
Adobe Photoshop is one of the world's leading graphics editing... Read More
Microsoft Great Plains fits to majority of horizontals and retail... Read More
Lotus Domino/Notes ? Microsoft Great Plains tandem as ERP with... Read More
You would like to protect your documents, wouldn't you? Reasons... Read More
And kill the best layout software in the process of... Read More
As you probably know, when Microsoft purchased Great Plains Software... Read More
If you look back to the history, you will see... Read More
Software |