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.

monthly home cleaning Buffalo Grove ..
In The News:

Smart home hacking fears overblown? Expert reveals real cybersecurity risks and simple protection tips to keep your connected devices safe from hackers.
MIT develops needle-free glucose monitor using light technology. Revolutionary device could replace painful finger pricks for diabetes management.
The ClickFix campaign disguises malware as legitimate Windows updates, using steganography to hide shellcode in PNG files and bypass security detection systems.
Researchers from Osaka Metropolitan University designed a 21-foot dome that combines aquaculture and hydroponics to create a self-sustaining urban food system.
The Fox News AI Newsletter gives readers the latest AI technology advancements, covering the challenges and opportunities AI presents.
ChatGPT data breach exposes personal info of users through partner Mixpanel. OpenAI confirms names, emails compromised in security incident.
Android rolls out Emergency Live Video for 911 calls, letting dispatchers see real-time scenes during emergencies. Great for holiday travel safety.
Malicious Chrome and Edge extensions collected browsing history, keystrokes and personal data from millions of users before Google and Microsoft removed them.
Google's new Call Reason feature lets Android users mark calls as urgent before dialing, displaying an urgent label to recipients using Phone by Google app.
Medical history made as surgeons successfully restore sight to legally blind patient using world's first 3D printed corneal implant grown from human cells.
Data brokers aggressively collect your holiday shopping data to fuel scams and targeted ads. Learn how to delete your digital profile before 2025 starts.
Scammers are sending fake MetaMask wallet verification emails using official branding to steal crypto information through phishing links and fraudulent domains.
Learn what background permissions, push notifications, security updates, auto-join networks and app refresh mean to better manage your phone's privacy settings.
Criminals test stolen data by applying for deposit accounts in victims' names to prepare bigger attacks. Learn why banks won't share fraud details.
New study of 10,500+ kids reveals early smartphone ownership linked to depression, obesity, and poor sleep by age 12. Earlier phones mean higher risks.
A phone phishing attack compromised Harvard's alumni and donor database, marking the second security incident at the university in recent months.
AutoFlight's zero-carbon floating vertiport uses solar power to charge eVTOL aircraft while supporting emergency response, tourism, and marine energy maintenance.
A new phone return scam targets recent buyers with fake carrier calls. Learn how criminals steal devices and steps to protect yourself from this fraud.
New Anthropic research reveals how AI reward hacking leads to dangerous behaviors, including models giving harmful advice like drinking bleach to users seeking help.
The Fox News AI Newsletter gives readers the latest AI technology advancements, covering the challenges and opportunities AI presents.
Holiday email scams, including non-delivery fraud and gift card schemes, spike in November and December, costing victims hundreds of millions, the FBI says.
Holiday visits offer the perfect opportunity to help older parents with technology updates, scam protection and basic troubleshooting skills for safer digital experiences.
Swiss scientists create grain-sized robot that surgeons control with magnets to deliver medicine precisely through blood vessels in medical breakthrough.
Researchers exploited WhatsApp's API vulnerability to scrape 3.5 billion phone numbers. Learn how this massive data breach happened and protect yourself.
Travel companies share passenger data with third parties during holidays, but travelers can protect themselves by removing data from broker sites and using aliases.

Microsoft Great Plains Chemicals & Paint Industry Implementation & Customization Notes

Microsoft Great Plains fits to majority of industries, in the... Read More

Free PDF Publishing Software

In a previous article, I wrote about OpenOffice... Read More

Intro to UNIX Shells

A UNIX Shell is in simplest terms, a command line... Read More

Microsoft Blues

With the advent of 'Service Pack 2' for Windows XP... Read More

Microsoft Great Plains BR: Bank Reconciliation

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

Are You Waiting for Microsoft Longhorn Operating System

I love new technology. I am still ready to wait... Read More

CRM 101: Customer Relationship Management for Beginners

Customer Relationship Management, abbreviated "CRM," is the term for a... Read More

How to Evaluate Staffing Software

If you are in the market for new staffing software,... Read More

The Religion And Philosophy Of Small Internet Business

I have always had a tendency to focus on the... Read More

Microsoft Great Plains Integration with Microsoft Access ? Overview for Developer

... Read More

Microsoft Great Plains Food Processing ? Implementation & Customization Highlights

Microsoft Great Plains might be considered as ERP platform to... Read More

Microsoft Great Plains: Offshore Customization & Development ? Overview for Consultant

When you visit department stores and see that majority of... Read More

Crystal Reports For Microsoft RMS ? Overview For Developer/Report Designer

If you are software developer or database administrator - we... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to... Read More

Managing Stress in the Computer Industry - Five Steps to a Stress-free Life

It would be easy to think, like most people apparently... Read More

The Bluebird Project

The objective for Zandi Digital is to make available clever... Read More

Cisco Certification: Introduction To ISDN, Part IV

In part III of this ISDN primer, we learned that... Read More

Microsoft Great Plains: Getting New Users Licenses ? Annual Service Plan FAQ

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

What You Must Know About Spyware Right Now

Spyware is like the new technological nuclear weapon on the... Read More

These Items Are A Must Before Making The Decision To Purchase Any Off-The-Shelf Software

1. What determines the software price? Is it Per Seat... Read More

C++ Tutorial 1, Introduction to C++

Introduction to C++Why Learn C++?C++ may at first seem like... Read More

Introduction To ISDN, Part III: PAP

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

Microsoft Great Plains IV: Inventory Management

Great Plains Inventory Management (IV) module gives your business a... Read More

Data Quality Best Practices for Salesforce.com

Executive SummaryAn effective plan for entering, cleaning and updating the... Read More

Mapping Software: Putting Data Visualization on the Map

Mapping Software Improves Data VisualizationFrom the outset, it is important... Read More

scheduled maid service Mundelein ..