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
Every day millions of new web documents emerge on the... Read More
Microsoft CRM is CRM answer from Microsoft Business Solutions. If... Read More
This article is the fourth of a series of articles... Read More
An integral part of any quality CRM system is lead... Read More
Here is some free software tools to help you build... Read More
ERP Consulting industry is on the way to serve clients... Read More
Just stress testing one of the latest Linux distributions. Been... Read More
Microsoft SQL Server is the leader for inexpensive and middle... Read More
As we could imagine, if you are reading this article... Read More
The most important benefit of XML is its simplicity. Though... Read More
There are plenty of articles out there about how to... Read More
This is a short article, written in question/answer/FAQ style to... Read More
We don't think about mainframe software pricing anymore, we just... Read More
Microsoft Great Plains is main accounting / ERP application... Read More
In linux, one of great commands for finding out information... Read More
Are you one of those people that keeps track of... Read More
Heard about the Quark "killer"?Adobe InDesign CS2. Will it really... Read More
Our opinion is based on our Microsoft Business Solutions Great... Read More
Preventive Maintenance (PM) is defined as scheduled work done on... Read More
Today's business world is fast-paced. No matter what it is... Read More
GroupwareThe internet is full of 1.5 million to 7 million... Read More
In the case when you represent mid-size or mid-size-to-large business,... Read More
Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More
In the new era of internet marketing the problem of... Read More
Small can be beautiful! Working with Knoppix for the past... Read More
While Ukraine is becoming a new popular IT outsourcing destination,... Read More
Handling character strings in Java is supported through two final... Read More
Microsoft has never released a service pack for Windows98 SE,... Read More
Scrapbooks are very popular these days. I think that almost... Read More
Each Industry and market niche has business specific and unique... Read More
Microsoft Business Solutions offers several ERP applications: Great Plains, Navision,... Read More
Microsoft Business Solutions Great Plains is very good fit for... Read More
In linux, one of great commands for finding out information... Read More
What is Spyware?Spyware monitors your surfing habits and sends the... Read More
Homeland security, airport security, Internet security ??" these days we???re... Read More
Microsoft Business Solutions Great Plains has substantial market share among... Read More
As seeing large number of implementations ? in our case... Read More
Executive SummaryAn effective plan for entering, cleaning and updating the... Read More
There are so many different programs that clutter up your... Read More
For a long time now Microsoft's Internet Explorer has ruled... Read More
How do you run a program on a remote server... Read More
Need software to record your voice, streaming audio or musical... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
Microsoft Business Solutions is emerging as very attractive vendor for... Read More
You have gotten those E_Mails buy software at deep discounts.... Read More
Most computer users use spreadsheets software such as Microsoft Excel... Read More
What is installation in the language of technology? Installation... Read More
Traditionally we were considering functionally rich systems, such as SAP,... Read More
A few months back I really got sick of my... Read More
The Windows Indexing Service provides you with the ability to... Read More
Software |