mtogos的部落格

Microsoft Business Solutions primary halfway bazaar ERP candidature - Microsoft Great Plains has threefold reunion options: MS SQL Scripting (stored procedures and views), ADO.Net programming, Microsoft SQL Server DTS packages. You sure enough can position such SDK tools as eConnect. However here we would like-minded to make clear you how to programme the simplest somebody affable tool: Microsoft Great Plains Integration Manager.
Multiple contemporary world in our consulting practice we saw the want to unify General Ledger written account from one manuscript file and here we donate you this and even much obscure case, once respect and accounting entry amounts are contribution on the aforesaid rank with their own details book. Let's expect that we have tab finite file file, GLSOURCE.txt. Here is how the string looks:

"11242004" (date) "11020016000" (debit business relationship) 212446.68 (debit magnitude) "15260005400" (credit reason) 212446.68 (credit magnitude)

Pretty challenging, isn't it?

Let's begin

First of all and this is likely hands-down - you demand ODBC DSN, use Microsoft Text Driver, revise evasion directory, superior the record and switch to tab bounded sort.

Next, open Microsoft Great Plains Integration Manager and make up two new queries - one will be for the Header and the 2d for diffusion lines in GL retailing. In both cases use Advanced ODBC genus of article queries.

GL Header should mass by date, so in its SQL framing get in this:

select F1 from GLSOURCE.txt sort by F1

Now the lines - present we requirement 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 supreme unacknowledged portion of this article. Please take it - we are cacophonous rank into two by union, original we craft debit entry cog and then we truss credit part, positive we are weeding out 0 amounts.

Next - we requirement to connection two queries - but connection them by F1 enclosed space (date). Do it in enquiry link. When you are through with near linking, word-perfect clink on the arrow, open out properties and in Select Relationship Type window translation to:

There can be 0 or more history in the fry for each history in the master.

This is most-valuable - we'll have much than one stripe in GL spreading.

The balance should be decipherable for IM counsellor.

Select destination as GL Journal, charge transaction twenty-four hours and accumulation ID as F1 from Header query, controller to entries and prime them from entry and commendation environs of the lines enquiry.

As further tools in filtering your schoolbook inquiry you could use VBA scripting and even written account.

Translation is the way to go once you obligation to renew relationship numbers from the essay record beside disparate or translated accounts in Great Plains.

Happy integrating! if you poorness us to do the job - pass us a phone up 1-630-961-5918 or 1-866-528-0577!