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.

Dowagiac prom limousines .. Lockport Chicago limo O’Hare
In The News:

Tech expert Kurt “CyberGuy" Knutsson says ChatGPT learns from chats, but you should avoid sharing sensitive information to protect your privacy.
Beyond Aero's BYA-1: Hydrogen-electric jet with zero emissions, reduced costs, 2030 launch. Kurt “CyberGuy" Knutsson discusses a potential game changer in the evolution of air travel.
Kurt "CyberGuy" Knutsson explains how you can creatively repurpose your old unused Android devices, which are full of potential and hidden value.
Mech the super-humanoid robot can lift up to 132 pounds and is designed to tackle stressful and repetitive tasks that often lead to workplace injuries.
Double-clicking is something we all do, often without giving it a second thought. But it could be giving hackers permission to do something dangerous.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
Kurt the CyberGuy offers some of his easy expert tips to free up storage space on your Android if your device is running short on storage.
You can reset your internet router remotely if you're not home when your power goes out. Kurt "CyberGuy" Knutsson explains how this can be done.
The EO Canopy is a self-sustaining, solar-powered camping platform designed to provide all the comforts of home while completely off the grid.
Experts say hackers who used to focus on Windows operating systems are increasingly targeting Apple IDs as part of a new phishing campaign.
Unitree, a Chinese robotics company that developed a backflipping robot, has now introduced a humanoid robot capable of doing a side flip.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
According to the FBI, cybercriminals are sending extortion letters, threatening to release victims' sensitive information unless a ransom is paid.
FireSat is a satellite project designed to detect and track wildfires early, aiming to detect a fire that's the size of a classroom within 20 minutes.
Tech expert Kurt “CyberGuy" Knutsson reports that researchers have uncovered a Chrome vulnerability used in a cyber espionage campaign.
Preserving voicemails securely on Android: Tech expert Kurt “CyberGuy" Knutsson reveals easy methods to keep memories alive forever.
This phishing kit bypasses 2FA via session hijacking and real-time credential theft. Kurt “CyberGuy" Knutsson offers four ways to stay safe from Astaroth phishing attacks.
Tech expert Kurt “CyberGuy" Knutsson discusses Joby Aviation and Virgin Atlantic planning to launch 200-mph U.K. air taxis linking airports and cities.
Fake Apple virus warnings, security alerts and messages are tactics used to prompt you to call a number or click on a malicious link. The CyberGuy explains how to protect your devices.
The combination of artificial intelligence and neuroscience allows a paralyzed man to manipulate a robotic arm by using his brain to imagine movements.
With the help of artificial intelligence, sophisticated fraud techniques emerging today are virtually undetectable to the untrained eye.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents
If you need to free up space on your Mac, consider deleting duplicate photos in your image library. Apple makes it easy to do, and the CyberGuy explains the process.
Microsoft is discontinuing Skype in May after 22 years of service. Kurt the CyberGuy addresses other options for internet-based phone and video service.
Tron 1, a Chinese company's two-legged robot, is versatile and can walk, roll and pivot, even on rough terrain. Tron 1 stands 33 inches tall and weighs 44 pounds.

Microsoft eCommerce Web-development: Great Plains eConnect .Net ? Highlights for Programmer

In our small article we'll consider Microsoft Business Solutions Great... Read More

SQL scripts for Project Accounting: Microsoft Great Plains series ? overview for developer

Microsoft Business Solutions Great Plains has Project Accounting module where... Read More

Microsoft CRM Integration with IBM Lotus Notes Domino ? Machinery Dealership Example

IBM Lotus Notes with Domino email server is traditional document... Read More

Free Microsoft Word Online Training Tutorial Resources

Microsoft Word is one of the most popular office applications... Read More

Seven Reasons Why a Hosted Inventory Management Solution Might Be Right For Your Organization

Upgrading. Downtime. Maintenance. Hardware obsolescence. Implementation issues. The litany of... Read More

Begun, The Browser Wars Have

As Mozilla Firefox nears 10% market share, with well over... Read More

5 Time-Saving Tips in Microsoft Word

Whether you have used Microsoft Word for years, have just... Read More

Outlook... Not Just for Email! Using Your Outlook Calendar

Microsoft Outlook is one of the most widely used software... Read More

Microsoft Office Selecting Secrets

Stop the Runaway MouseWhat's the "runaway mouse?" You've seen it...you... Read More

Microsoft Moves to Small Business Accounting/Retail Market ? Stakes and Thoughts

In this small article we will be looking at the... Read More

Microsoft CRM Integration With Microsoft Office Documents ? Overview For System Integrator

Microsoft CRM is CRM application, maintained and supported by Microsoft... Read More

IT Strategy for Large Corporation: ERP/MRP/CRM, Unix/Linux/Windows, Microsoft/Java

Combining Microsoft Business Solutions Great Plains ERP with non-Microsoft Business... Read More

Microsoft Great Plains Integration Manager: Using Continuum ? Overview for Developer

Microsoft Business Solutions Great Plains has I'd say end user... Read More

Explore the Internet in a Whole New Way

For a long time now Microsoft's Internet Explorer has ruled... Read More

Great Plains Custom Development: Dexterity, VBA, SQL, Crystal, eConnect ? Overview For Programmer

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

Defining OLAP Solutions and Data Warehouse design

This tutorial covers OLAP solutions used by Data warehouses and... Read More

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

Microsoft Business Solutions CRM is present several years on the... Read More

Software Review: Xsitepro Total Site Management Software

It's not very often I get excited about a software... Read More

.Net Charts and Graphs Interact with Businesses and Customers

Bar charts, bar graphs, and any other chart or graph... Read More

Monitoring Software Can be Used for Spying as Well

We all already got used to computer monitoring both at... Read More

Why Stick With Email Clients Like Outlook?

Trying to figure out a stream in banning one email... 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

Microsoft Great Plains Distribution, Barcoding, Consignment ? overview for consultant

Microsoft Great Plains ? ERM from Microsoft Business Solutions and... Read More

A Guide To Purchasing Professional XP Icons Online And Enhancing Your Applications

Icons are used everywhere; right from software applications, to internet... Read More

eConnect: eCommerce Development for Microsoft Great Plains

Microsoft Business Solutions Great Plains has several options to enable... Read More

shuttle from O'Hare Lake Villa ..