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.txtwhere F3''unionselect F1 F4, 0,F5 from GLSOURCE.txtwhere 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.
|
|
|
Software Upgrades Arent Always The Best Move
When my daughter was getting into AOL instant messaging (AIM) and using all the cool add-ons, I looked for more as it's a great way to learn about extending applications. While doing research, I learned that if you wanted to use AIM themes, you don't want to upgrade to AIM 5.9. A post at MyThemes suggests sticking with or downgrading ...(related: Software)
Microsoft Great Plains Implementation: Collection Management ? Overview For Consultant
Microsoft Business Solutions Great Plains is very good fit for mid-size company and probably good budget solutions for large corporation. Being matured product ? Great Plains provides such horizontal solutions as Collection department automation. The most common question we hear in collection department is how we assign problem clients to sp...(related: Software)
Preventive Maintenance Software Companies
Several software companies design programs for preventive maintenance. Most of the preventive maintenance software companies produce programs for facilities and businesses. Because many programs are adaptable, they can be used for different types of facilities. Some of the best know preventive ...(related: Software)
Removing Incoming Email In Ms Exchange, C# Example
The purpose of one of our projects was MS Exchange handler for processing incoming email.The basic source of knowledge was this article "Developing Managed Event Sinks/Hooks for Exchange Server Store using C#" by Logu Krishnan, published to the address What To Do When Windows Wont Boot
When Windows fails to boot it is normally caused by you installing a program or device and it has caused a conflict with one or more other programs.This will no doubt give you plenty of heartaches if you're not certain which program caused Windows to not boot up.If you recently installed a program or application and know where it was installed,you may be in much better shape as for as correcting the error.Here are common ways to correct the problem of your computer not completely booting up or not booting up at all.If your computer will not boot-up at all,hopefully you have made a good emergency boot disk. You can always make a windows startup disk by creating one from another compu...(related: Software)
Secure File Transfer Using Ssh Plus Additional Audit & Automation - Fsa Reporting
In order to meet regulatory and corporate compliance requirements reporting members will require a cost effective solution that may be installed at one side of the data transfer without being tied to any proprietary protocol.Considerations inc...(related: Software)
Constructionalist Parsing - Deciphering Natural Language
The research in the field of Natural Language Processing usually assumes the existence of a syntactic "Generative Engine" that combines words and word-like elements into syntactic structures, and then sometimes displaces them by "syntactic movement". A Linguistic Parser must "undo" all the effects of syntactic movement, which results in a structure where the relations between the words are represented more directly.In recent years, a new theoretical framework was introduced, i...(related: Software)
What Is Shareware?
Shareware is software that you can try before you buy; shareware is a kind of marketing method for software. Software developers post trial versions of their software on websites. Consumers can then download the trial version to their computer and evaluate it. If the consumer likes the software they can purchase it. Shareware is also called try before you buy.Today almost every big software compan...(related: Software)
Crystal Reports For Microsoft Navision - Overview For Programmer/it Specialist
Let us give you - developer some hints in the report creation.
- C/SIDE database - use C/ODBC to create the connection for your Crystal Report. You have to make all the links inside the report. The drawback of this approach - report becomes inflexible if you would like to change database mapping (re-link from work to historical tables for example) - it is probabl...(related: Software)
site-map - Copyright © 2008 | Contact Webmaster | All Rights Reserved. | Software