Software informational articles

Microsoft great plains integration executive ? functioning with text file - software


Microsoft Commerce Solutions main average bazaar ERP concentration - Microsoft Great Plains has compound integration options: MS SQL Scripting (stored procedures and views), ADO. Net programming, Microsoft SQL Attendant DTS packages. You emphatically can arrange such SDK tools as eConnect. In spite of this here we would like to show you how to agenda the simplest user gracious tool: Microsoft Great Plains Integration Manager. Multiple times in our consulting apply we saw the need to integrate Broad-spectrum Ledger transactions from one text file and here we give you this and even more complicated case, when accept and debit amounts are award on the same line with their own balance numbers. Let's begin to have that we have tab encircled 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 in all probability easy - you need ODBC DSN, use Microsoft Text Driver, alteration duck directory, choice the file and button to tab enclosed type.

Next, open Microsoft Great Plains Integration Director and coin two new queries - one will be for the Heading and the be with for circulation lines in GL transaction. In both cases use Difficult ODBC type of text queries.

GL Heading be supposed to accumulate by date, so in its SQL display enter this:

select F1 from GLSOURCE. txt group by F1

Now the lines - here we need unionizing and read-through if quantity 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 challenging part of this article. Entertain absorb it - we are splitting line into two by union, first we construct debit part and then we affix belief part, plus we are weeding out 0 amounts.

Next - we need to link two queries - basically 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 Cliquey Bond Type display adjust to:

There can be 0 or more account in the child for each background in the master.

This is chief - we'll have more than one line in GL distribution.

The rest be supposed to be customary for IM consultant.

Select destination as GL Journal, assign transaction date and batch ID as F1 from Heading query, alter to entries and choice them from debit and belief parts of the lines query.

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

Translation is the way to go when you need to interchange bill information from the text file with altered or translated financial records 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 Knowledge Bureaucrat in Alba Spectrum Technologies - USA countrywide Great Plains, Microsoft CRM customization company, allocation Chicago, California, Colorado, Arizona, New York, Texas, Florida, Georgia, Canada, UK, Russia, Europe and Australia and having locations in many states and globally ( http://www. albaspectrum. com ), he is Dexterity, SQL, C#. Net, Gem Information and Microsoft CRM SDK developer.

Developed by:
home | site map © 2018