country kitchen remodel mchenry brytons bath historic remodeling glencoe adding rooms to existing house mundelein basement remodeling huntley .. Chicago Drug testing

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.

In The News:

Introduction To ISDN, Part III: PAP

Introduction To ISDN, Part III: Configuring PPP PAP AuthenticationNow we... Read More

Microsoft Great Plains Payroll Module Customization Scenarios

It is now common thing when large corporation selects mid-market... Read More

Microsoft Great Plains Inventory Control ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Recovering Microsoft Great Plains Customization ? Tips for IT Director

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

Inherent Dangers Of File Sharing Via The Internet.

Cyberspace has opened up a new frontier with exciting possibilities... Read More

How the Firefox: How to... Manual Helped Me

A few months back I really got sick of my... Read More

A Symons Mark II Function Point Counting Example

I provide, here clear explanations and a count of function... Read More

Microsoft CRM ? Typical Customizations

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

Bill of Lading ? Custom Reporting for Microsoft Great Plains ? Overview for Consultant

Bill of Lading is required report for Logistics and Freight... Read More

Microsoft Great Plains: Large Scale Implementation

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

Does Microsoft have any real competition?

Does Microsoft Have any Real Competition? Copyright (c) 2003 Gregory... Read More

Twelve Things You Should Know to Save on Computer Software

Do you want to get quality software at a reasonable... Read More

What is Spyware? Are You Being Watched?

Find out why Spyware Removal from computers is important and... Read More

Not All Project Management Software is Created Equal

The purpose of Project Management Software is to provide an... Read More

Builders Beware

Which Type of Shop Can Rely On A Home Built... Read More

Microsoft CRM Implementation & Customization: MS CRM Fax Gateway

With this small article we are continuing Microsoft Business Solutions... Read More

Microsoft Great Plains Implementation: Verticals - Wholesale Order Entry center ? overview

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

The Hidden Power of Online Manual

Writing software manuals is boring, isn't it? We often think:... Read More

Microsoft Great Plains Customization Recovery & Upgrade for Large Corporation

At the end of XX century, in the late 1990th... Read More

Seven Reasons Why a Hosted Inventory Management Solution Might Be Right For Your Organization

Upgrading. Downtime. Maintenance. Hardware obsolescence. Implementation issues. The litany of... Read More

Brand Your Websites URL With a Favicon

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

How to Choose the BEST Charting Software

I suggest that you do not spend a lot of... Read More

Spyware: What It Is and How to Combat It

Spyware is software or hardware installed on a computer without... Read More

What is a Document Manager without Version History?

Document Manager and Version HistoryIn previous articles I have discussed... Read More

MicroWorld Releases New Version of MailScan Ver. 4.5 - Antivirus and Content Security Software

MicroWorld Technologies, Inc. the leading solutions provider in the area... Read More

led garage light fixture led shoebox fixtures Pete's produce ..
led garage light fixture led shoebox fixtures Pete's produce ..