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.
Inexpensive Shopping on Software Engineering:
Ebooks & Digital Products On Software Engineering:
Live Software Engineering Auctions On Ebay Now:
Brand Name Deals & Coupons On Software Engineering:
Ezine Articles & Information On Software Engineering: Half.com deals on Software Engineering: mashups On tag software @ amazon:
|
 |
RELATED ARTICLES
The Dreaded Paper Label - Should it be Used?
While paper labeling CDs and DVDs may appear to be a cost effective solution for printing on your media, there are solid reasons why you should consider other options.1) Hand application of paper labels can be time consuming and frustrating.
Microsoft Great Plains as ERP and Microsoft CRM as CRM
If you have Microsoft Great Plains and support it for your company then you need to know some technical details about Great Plains version upgrade and what is going on behind the scenes, which options do you have in case of Dexterity, VBA, SQL customization, additional complexity comes with migration from ctree/Pervasive to MS SQL/MSDE.What is upgrade in the language of technology? Upgrade has server and client sides.
Guide to Software Marketing
Intro
This concise article will tell you in plain English how you can promote your software easily and effectively.Submit your software
I recommend submitting your software to upload.
Looking for an Alternative to Microsoft Office? You Should Be!
Now is the time to look at an alternative to Microsoft Office.Are you locked into using Microsoft Word, Excel, and PowerPoint? Are you spending extra because you need a database, and Access does not come with the basic Office package?Well, the time has come to look at alternatives, or at least the one I¡¦ll discuss here in a few moments.
Who's Watching What You Type?
If someone entered your home, uninvited and installed numerous cameras and listening devices in order to monitor your activities, you would quite rightly be outraged. While such a situation, unless you are living in the Big Brother House, would be considered ridiculous, the same cannot be said for the humble home computer.
Scrap Booking Online: Word Perfect or Corel Graphics Suite?
Scrapbooks are very popular these days. I think that almost everyone wanted to capture family histories and stories in any which way they can.
Great Plains DOS Support - Notes for Consultant
Great Plains Accounting, accounting package for mid-size and small companies was in its peak of popularity back in earlier 1990th. It first had DOS interface, later on migrated to classic Windows version (remember earlier event driven Windows and its strange and at the same time revolutionary programming model - guys who remember Borland C++ and Microsoft C++).
Microsoft Great Plains Upgrade - Things to Consider and FAQ
If you have Microsoft Great Plains and support it for your company then you need to know some technical details about Great Plains version upgrade and what is going on behind the scenes, which options do you have in case of Dexterity, VBA, SQL customization, additional complexity comes with migration from ctree/Pervasive to MS SQL/MSDE.What is upgrade in the language of technology? Upgrade has server and client sides.
Protect Your Computer...and Your Business!
We all take the computer for granted. I mean, all we have to do is switch it on and it's ready to go.
Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW - ReportWriter - Tips for Developer
Microsoft Business Solutions Great Plains is written in Great Plains Software programming tool: Great Plains Dexterity. Dexterity in turn was built with conception of graphical cross-platform transferability (in time - 1992 - mostly Mac and MS Windows).
Microsoft CRM Programming Secrets - Tips For Developers
This article is for advanced Microsoft CRM SDK C# developers. It describes the technique of direct SQL programming, when SDK doesn't have the functionality to do the job.
Increase Office Efficiency With One Simple Tool
When you need a phone number, you do a quick search on the
Internet and in a few seconds, you've got the information you
need. And you probably receive a lot of contact information right
inside the emails you receive every day.
CRM and Customer Life Cycle
Customer Relationship Management or CRM is a combination of enterprise strategies, business processes and information technologies used to learn more about customers' needs and behaviors in order to develop stronger relationships with them. CRM software systems automate many customer-related business tasks.
Why do Manufacturers Invest in Business Management Software?
With many manufacturing shops heading over seas in favor of lower cost, it is tough to compete in today's marketplace. As a result, the goal for manufactures who want to compete going forward is to run leaner, faster and more accurately.
Microsoft Great Plains version 8.5: Upgrade, Customization, VBA, Crystal Reports - Highlights
Microsoft Great Plains is one of the Microsoft Business Solutions family ERP products: Great Plains, Navision, Axapta, Solomon, Small Business Manager. MBS also has Microsoft CRM - Client Relation Management software and Microsoft Retail Management System (Microsoft RMS)Microsoft Great Plains 8.
What Stands Behind The Software Description?
With thousands of web pages added to the Net every day, it's getting more and more frustrating both to submit the information to the attention of those, who are interested in it and to find the information one needs.The problem is even aggravated with the fact that customers become more exigent to the products they are offered.
What You Must Know About Spyware Right Now
Spyware is like the new technological nuclear weapon on the internet. Spyware can not only damage the health of your computer but it can also steal your personal identity other personal information.
.Net Charts and Graphs Interact with Businesses and Customers
Bar charts, bar graphs, and any other chart or graph used in financial statements, inventory reports, sales reports, and a slew of other types of reports have typically been paper reports or online reports depicting various levels of complex information for tracking, investing, planning, and buying. However, until the .
eStore Advantage - Extending Microsoft eConnect for MBS Great Plains
eStore Advantage allows front-office applications to communicate with back-office business environments. It has a built-in support for electronic payment processing, and serves as a core integration platform for Nodus Technologies front-to-back office connectivity suites including RMS and CRM Advantage.
Put Some Pizazz in Your Software Arsenal
The software giants don't do everything and don't always produce the result that we want. At least if you're like me, and you're not where you would like to be (profitwise).
|
