Software Information |
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.
MORE RESOURCES: Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exiting |
RELATED ARTICLES
Dont Choose Adobe When Working With PDF While Adobe is the most known maker of PDF tools, your business will be much better off, if you select third party tools to work with PDF documents.ABC Amber PDF converterPriced at eleven dollars, this tool may very well be the cheapest PDF software on the planet. How To Make Good Use of Spreadsheets Most computer users use spreadsheets software such as Microsoft Excel in their daily home and office. However very few are aware of the potential of spreadsheets in helping them in financial accounting and statistical analysis. 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. What to Do if All Screensavers Fun is Grayed Out? Finally, you have some time to personalize your desktop with an interesting screensaver. But wait. Review on QuarkXpress 6.0 After almost two decades of existence, Quark has become the basic building block of any print designer's toolkit. It has deservedly gained a reputation for reliable printing, offering consistent and dependable output that prevents costly mistakes. Outlook... Not Just for Email! Using Your Outlook Calendar Microsoft Outlook is one of the most widely used software programs for email, but if you're only using it for email, you're missing out on a great productivity tool! This month's column looks at a few ways you can use the Outlook calendar to make scheduling easier.* Add public holidays to your calendar automatically. Keeping Track One of the things we can be as certain of as death and taxesis that in every meeting at least one person will hand out adocument for comments and discussion.But think of the huge workload this gives rise to for theauthor. Story Development Software: Good or Evil? In the early days of the personal computer, we're talking the mid-'80s here, there was speculation that someday books would all be writen by computers. It sounded a little too science-fictiony for most writers. Great Plains Accounting Migration to Microsoft Great Plains - Overview for IT Specialist This is a short article, written in question/answer/FAQ style to give IT Specialist/developer/programmer balanced top level information on Great Plains Accounting migration to Microsoft Business Solutions Great Plains. If you have Great Plains Accounting as main accounting and ERP system you need to know some technical details on the migration to Great Plains and what is going on behind the scenes. Microsoft CRM or Siebel Siebel is traditional CRM market leader, however and mostly due to recession 200-2003, Siebel lost sizable portion of CRM market to new tiger, such as Microsoft CRM. Microsoft CRM s recent (2001) CRM answer from Microsoft and attempt top get market share from traditional vendors: Siebel, Oracle, Onyx. Cisco Certification: Introduction To ISDN, Part V The major reason I recommend getting your hands on real Cisco equipment rather than a simulator is that real Cisco routers give you the chance to practice and learn show and debug commands.The knowledge you acquire from debugs is invaluable. Keep Your Software Simple! A Review of EditPlus I like my software simple. If it's too complex or difficult to figure out, I don't use it. What is Groupware? Vince Lombardi once said that, "The achievements of an organization are the results of the combined effort of each individual." If this is the case, then what do we need to do in order to insure success in our companies? How can we combine the efforts of each individual as Mr. Navision Attain C/ODBC Crystal Report - Customization Example Microsoft Business Solutions Navision is main ERP application for European, Brazilian markets plus it serves vertical segments of the USA market, such as light manufacturing, POS, CRM. As our experience indicates - Navision implementation requires more customization and tuning in comparison to another mid-market ERP solution from MBS: Microsoft Great Plains. Assertion in Java Assertion facility is added in J2SE 1.4. String in Java Handling character strings in Java is supported through two final classes: String and StringBuffer. The String class implements immutable character strings, which are read-only once the string has been created and initialized, whereas the StringBuffer class implements dynamic character strings. Microsoft Navision Customization Upgrade - Tips For Programmer/IT Specialist Currently Microsoft Business Solutions is on the way of creating so-called suites: Microsoft Financials, Microsoft Distributions, Microsoft HR, Microsoft Manufacturing. It is very difficult to say which product will be the core of which suite, but the best guess is this: Microsoft Great Plains - Microsoft Financials and probably Distribution/Supply Chain Management and HR, Solomon - Microsoft Project or Professional Services, Navision - Microsoft Manufacturing. 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. Where to Find Free Fleet Maintenance Software Costs of fleet maintenance software can vary widely. It is generally expected that the fleet manager will look at the needs of the company to determine what software package is best suited for their particular needs. eConnect: eCommerce Development for Microsoft Great Plains Microsoft Business Solutions Great Plains has several options to enable web ordering. Traditionally Great Plains Dynamics/eEnterprise had eOrder - this is ASP pages based ordering application, enabling you to place or retrieve your Sales Order Processing (SOP) Sales Orders over the web. |
home | site map | contact us |