Great Plains Integration Manager scripting and translation - overview for programmer, software developer, database administrator, IT specialist
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.
We assume that you are familiar with Microsoft scripting technologies: Visual Basic for Application (VBA). You should be aware, however, about poor performance of Integration Manager. We recommend it for low volume of complex transactions. It is good tool, if you are importing about 100 transactions per day from text file (when you need to come through empty lines or something not matching the template to filter integration lines). If you have thousand transactions and performance is critical - switch to SQL Stored procs or Dexterity custom process server.
1. VBA Scripting ? Launch Great Plains Integration Manager, open your integration, right click->Properties, switch tab to scripts, open Before Document script. Now place this code in it:
If Left(SourceFields("F4"), 4)="THRU" Then
SetVariable "DocDate", Right(SourceFields("F4"), 8)
End If
If IsNull(SourceFields("F1")) Or IsNull(SourceFields("F7")) Then
CancelDocument
End If
It should mean that you can create DocDate variable and assign it the value from your query field. The second statement cancels document integration if certain condition met (when line is empty or is not actually integration line - something like comment record, etc.)
Now open Before Document Commit script and place this code there:
Dim MyTime, MyHour
MyTime = Now
MyHour = Hour(MyTime)
If Not IsNull(GetVariable("DocDate")) then
SetVariable "DocNumber", GetVariable("DocNumber")+1
DestinationFields("Document Number")="THRU"+GetVariable("DocDate") &"A" & GetVariable("DocNumber")
DestinationFields("Posting Date")=GetVariable("DocDate")
DestinationFields("Description")="Client Check "+ SourceFields("F1")
If Not IsNull(SourceFields("F8")) Then
DestinationFields("Check.Check Number")=SourceFields("F8")
Else
SetVariable "DocNumber", GetVariable("DocNumber")+1
DestinationFields("Check.Check Number")=GetVariable("DocDate")& MyHour&GetVariable("DocNumber")
End If
DestinationFields("Check.Date")=GetVariable("DocDate")
If Month(DestinationFields("Check.Date")+1)= Month(DestinationFields("Check.Date")) Then
DestinationFields("Check.Date")=DestinationFields("Check.Date")+1
End If
End If
Here you do two things. First you construct check number, using Date and time plus Document number. And the second is even more interesting - you alter check date.
In After Integration script place this code:
ClearVariables
It is good idea to nullify your variables.
2. Translation. Imagine that your are something like collection agency and you need to translate your vendors (from whom you purchase AR) into real vendors (banks). In AP integration open Destination Mapping, select Vendor ID, in the Rule Properties, click on the button next to Translation field, answer No, click on Translation tab and paste from Excel your translation list, something like this:
JOHNB BANKOFAMERICA
PETERP BANKOFAMERICA
MARTHAM CITIBANK
Now your vendors from integration file will be replaced with real vendors from Great Plains.
Happy integrating! if you want us to do the job - give us a call 1-866-528-0577! help@albaspectrum.com
About The Author
Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ? USA nationwide Great Plains, Microsoft CRM customization company, based in Chicago, California, Colorado, Arizona, New York, Texas, Florida, Georgia, Canada, UK, Australia and having locations in multiple states and internationally (help@albaspectrum.com), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer; help@albaspectrum.com
Oswego taxi to Midway ..With so many Microsoft Windows related viruses, errors, and other... Read More
Microsoft Great Plains, designed back in 1990th as database transferable... Read More
Shareware is software that you can try before you buy;... Read More
Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More
For those who are unclear on the differences between the... Read More
So, why should you use any O/R mapping tool? I... Read More
What is Tripwire?Tripwire is a form intrusion detection system (IDS)... Read More
Customer Relationship Management or CRM is a combination of enterprise... Read More
Microsoft Business Solutions Great Plains has I'd say end user... Read More
Microsoft Business Solutions is emerging as very attractive vendor for... Read More
GroupwareEfforts are continually made to manage the unavoidable ad hoc... Read More
The cornerstone of successful automated office systems is the ability... Read More
Microsoft Great Plains has full-featured Manufacturing suite of modules: Capacity... Read More
In order to meet regulatory and corporate compliance requirements reporting... Read More
Do you remember that frustrating feeling when you find an... Read More
ERP (Enterprise Resource Planning) Overview covers What is ERP, Brief... Read More
What is Snort?Snort is an open source network intrusion detection... Read More
Since Version 8.0 Microsoft Business Solutions Great Plains & Great... Read More
Well, even if the combination might look very unusual, we... Read More
Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More
If you are in the market for new staffing software,... Read More
eStore Advantage allows front-office applications to communicate with back-office business... Read More
The intuitive algorithm.Roger Penrose considered it impossible. Thinking could never... Read More
Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More
Many Webmasters have never bothered to view their website's server... Read More
shuttle from Midway Morris ..The most important benefit of XML is its simplicity. Though... Read More
This is a short article, written in question/answer/FAQ style to... Read More
With thousands of web pages added to the Net every... Read More
Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great... Read More
Shareware is software that you can try before you buy;... Read More
Preventive Maintenance (PM) is defined as scheduled work done on... Read More
TCO (Total Cost Ownership) is the buzzword in... Read More
The purpose of Project Management Software is to provide an... Read More
If you feel intimidated when someone tries to teach you... Read More
Microsoft CRM is new player on the CRM software... Read More
Finally, you have some time to personalize your desktop with... Read More
Features Additionally, Vista will include many other new features.Aero Vista... Read More
It is really interesting that a bug can create problem... Read More
The Windows registry is a huge database that ensures normal... Read More
Customer Relationship Management, abbreviated "CRM," is the term for a... Read More
OEComplete is a utility for managing the personal information of... Read More
In order to meet regulatory and corporate compliance requirements reporting... Read More
We were recently faced with a decision: either to let... Read More
Introduction: The creating of a computer program involves a number... Read More
For a long time now Microsoft's Internet Explorer has ruled... Read More
So let's begin crunching down these 300 images using Adobe... Read More
I started using PIP (Picture It Publishing) Platinum 2002 right... Read More
If you have been using the Internet for any amount... Read More
If you're online using a dialup Internet connection, you'll probably... Read More
The Internet is reshaping every form of communications medium, and... Read More
Software |