Color coding Integration Manager scripts

Ok, so this is not currently possible, but won’t it be great if it were? )

This question came up in the Dynamics GP Developers’ newsgroup just a couple days aback and while the developer was sure of seing this at one of his clients, I assured him that Integration Manager did not have the capability of color coding the script, which is very standard stuff in other script editors and development environments, take for example Dynamics GP VBA code editor.

The above image is simulated and not an actual capability of Integration Manager

Nonetheless, I explained to the developer that I use other tools like VbsEdit to provide script color coding and some debugging capabilities. Except for native Integration Manager objects, VbsEdit is capable of color coding keywords, numbers, strings, etc., and provide line numbering which facilitates code readability and troubleshooting. Did I mention the ability to insert code breakpoints and the more than 200 sample scripts provided?

So the next time you are struggling with VBScripting within Integration Manager, consider giving VbsEdit a shot. You may also want to look into Notepad++ which offers similar features.

Finally as I may be wrong and since nothing is set in stone, if you have been able to enable script color coding in Integration Manager’s Script Editor please post back with how you did it.

Until next post,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.

Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box

-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULLDROP PROCEDURE uspUpdateVendor1099Box;GO


CREATE PROCEDURE dbo.uspUpdateVendor1099Box@VENDORID CHAR(21) = NULL,@TEN99BOXNUMBER SMALLINT = NULL,@RET_VAL INT OUTPUTAS


SET @RET_VAL = 0


IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)BEGIN  SELECT @RET_VAL = 0;  RETURNEND


BEGIN TRY  UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =  @VENDORIDEND TRYBEGIN CATCH  SELECT @RET_VAL = ERROR_NUMBER()END CATCH;GO


GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRPGO

Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document

' Created by Mariano Gomez, MVPConst adUseClient = 3Const adCmdStoredProc = 4Const adSmallInt = 2Const adInteger = 3Const adChar = 129Const adParamInput = 1Const adExecuteNoRecords = 128


Dim oCnDim oCmdDim pVendorID, pTen99BoxNumber, pRetVal


set oCn = CreateObject("ADODB.Connection")oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyIDoCn.CursorLocation = adUseClientGPConnection.Open(oCn)


With oCmd  .ActiveConnection = oCn  .CommandType = adCmdStoredProc or adExecuteNoRecords  .CommandText = "dbo.uspUpdateVendor1099Box"


  'Set parameters  set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _     GetVariable("gVendorID"))  .Parameter.Append pVendorID


  set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _     adParamInput, 8, GetVariable("gBoxNumber"))  .Parameter.Append pTen99BoxNumber


  set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)  .Parameter.Append pRetVal


  .ExecuteEnd With


If pRetVal.Value  0 Then' Error handling here, you can choose to write to IM's logEnd If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script — we will no longer map the vendor ID since it’s necessary to use a field script.

Vendor ID field script

' Created by Mariano Gomez, MVPSetVariable "gVendorID", SourceFields("someSource.Vendor")SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don’t get discourage if you don’t see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.

Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box

-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULLDROP PROCEDURE uspUpdateVendor1099Box;GO


CREATE PROCEDURE dbo.uspUpdateVendor1099Box@VENDORID CHAR(21) = NULL,@TEN99BOXNUMBER SMALLINT = NULL,@RET_VAL INT OUTPUTAS


SET @RET_VAL = 0


IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)BEGIN  SELECT @RET_VAL = 0;  RETURNEND


BEGIN TRY  UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =  @VENDORIDEND TRYBEGIN CATCH  SELECT @RET_VAL = ERROR_NUMBER()END CATCH;GO


GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRPGO

Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document

' Created by Mariano Gomez, MVPConst adUseClient = 3Const adCmdStoredProc = 4Const adSmallInt = 2Const adInteger = 3Const adChar = 129Const adParamInput = 1Const adExecuteNoRecords = 128


Dim oCnDim oCmdDim pVendorID, pTen99BoxNumber, pRetVal


set oCn = CreateObject("ADODB.Connection")oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyIDoCn.CursorLocation = adUseClientGPConnection.Open(oCn)


With oCmd  .ActiveConnection = oCn  .CommandType = adCmdStoredProc or adExecuteNoRecords  .CommandText = "dbo.uspUpdateVendor1099Box"


  'Set parameters  set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _     GetVariable("gVendorID"))  .Parameter.Append pVendorID


  set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _     adParamInput, 8, GetVariable("gBoxNumber"))  .Parameter.Append pTen99BoxNumber


  set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)  .Parameter.Append pRetVal


  .ExecuteEnd With


If pRetVal.Value  0 Then' Error handling here, you can choose to write to IM's logEnd If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script — we will no longer map the vendor ID since it’s necessary to use a field script.

Vendor ID field script

' Created by Mariano Gomez, MVPSetVariable "gVendorID", SourceFields("someSource.Vendor")SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don’t get discourage if you don’t see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.

Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box

-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULLDROP PROCEDURE uspUpdateVendor1099Box;GO


CREATE PROCEDURE dbo.uspUpdateVendor1099Box@VENDORID CHAR(21) = NULL,@TEN99BOXNUMBER SMALLINT = NULL,@RET_VAL INT OUTPUTAS


SET @RET_VAL = 0


IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)BEGIN  SELECT @RET_VAL = 0;  RETURNEND


BEGIN TRY  UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =  @VENDORIDEND TRYBEGIN CATCH  SELECT @RET_VAL = ERROR_NUMBER()END CATCH;GO


GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRPGO

Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document

' Created by Mariano Gomez, MVPConst adUseClient = 3Const adCmdStoredProc = 4Const adSmallInt = 2Const adInteger = 3Const adChar = 129Const adParamInput = 1Const adExecuteNoRecords = 128


Dim oCnDim oCmdDim pVendorID, pTen99BoxNumber, pRetVal


set oCn = CreateObject("ADODB.Connection")oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyIDoCn.CursorLocation = adUseClientGPConnection.Open(oCn)


With oCmd  .ActiveConnection = oCn  .CommandType = adCmdStoredProc or adExecuteNoRecords  .CommandText = "dbo.uspUpdateVendor1099Box"


  'Set parameters  set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _     GetVariable("gVendorID"))  .Parameter.Append pVendorID


  set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _     adParamInput, 8, GetVariable("gBoxNumber"))  .Parameter.Append pTen99BoxNumber


  set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)  .Parameter.Append pRetVal


  .ExecuteEnd With


If pRetVal.Value  0 Then' Error handling here, you can choose to write to IM's logEnd If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script — we will no longer map the vendor ID since it’s necessary to use a field script.

Vendor ID field script

' Created by Mariano Gomez, MVPSetVariable "gVendorID", SourceFields("someSource.Vendor")SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don’t get discourage if you don’t see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

VBScript "Type Mismatch" Error with Integration Manager 10.0

For those of you battling it out with a “Type Mismatch” error in your event or field scripts upgraded to IM 10.0 from previous releases of IM, sweat it no more! This issue has been recognized as a bug by Microsoft and was unfortunately not resolved in Feature Pack 1 (or Service Pack 2). The problem seems to occur with all IM VBScript functions that accept parameters.

“The issue is that these functions (and many other) accepted OPTIONAL parameters in prior versions of IM. The reason these are now failing is because Integration Manager has been switched over from VB6 to C# .Net code. The problem with that is that C# does not support optional parameters. That means that those existing function’s “Optional” parameters are now actually Required parameters.” said Greg Willson with Microsoft in the Microsoft Dynamics GP Community group.

However, as a workaround, IM developers must supply all parameters for the affected functions until a Hot Fix is made available in July. Willson proceed to explain the issue with an example.

“So with that said, the LogDocDetail function has five parameters (message, source, status_code, field_name and field_value). All parameters are string values except “status_code” which is an Integer value. So, now that we know that, if you supply a value for all parameters, it will work as expected.“.

The list of affected functions include (but may not be limited to):

• CancelDocument [message, source, status_code, field_name, field_value]
• CancelIntegration [message, source, status_code]
• Execute path [, timeout] [, window_style]
• LogDetail [message, source, status_code]
• LogDocDetail [message, source, status_code, field_name, field_value]
• LogDocWarning [message, source, status_code, field_name, field_value]
• LogWarning [message, source, status_code]
• PlaySound path

Functions not affected:

• ClearVariables
• DocumentIsNew
• DocumentNo
• GetVariable(variable) — the parameter is mandatory
• SetVariable variable, value — the parameters are mandatory

To access and contribute to the discussion thread on the Microsoft Dynamics GP community click here.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Integration Manager 10 and the GPConnection Object and Properties

The GPConnection object replaces the instantiation previously made with the RetreiveGlobals DLL, used with previous versions of Microsoft Dynamics GP.
Open method

The Open method allows you to open an ADO connection using the current GP user login information. This method uses the data source that is in use when Microsoft Dynamics GP is open. If you want to use a default company database (TWO or GPDAT) for this method, then you must set the Open value in the connection string before using the Open method.

You will not be able to update the connection string after the Open method is called. There is no close method for this object. Once the connection is returned to the same way the connection object was initially created in the script, that connection object can be closed normally.

Syntax

object.Open({suppress})

Comments
All properties for the Open method will return string values.

Examples
The following example is the Open script. It opens the data connection.

set MyCon = CreateObject("ADODB.Connection")MyCon.Connectionstring = "database=GPDAT"GPConnection.Open(MyCon)

The following is an example of creating the ADO record set.

set recset = CreateObject("ADODB.Recordset")

The following is an example of creating the ADO connection.

set MyCon = CreateObject("ADODB.Connection")

The following is an example of executing the update command

recset = MyCon.Execute(updatecommand )

The following is an example of closing the ADO Connection.

MyCon.Close

The following are examples of retrieving the properties exposed by the new GPConnection object.

MsgBox GPConnection.GPConnUserDateMsgBox GPConnection.GPConnInterCompanyIDMsgBox GPConnection.GPConnUserIDMsgBox GPConnection.GPConnUserNameMsgBox GPConnection.GPConnDataSource

UserDate property

The UserDate property contains the current user date.

Syntax

object.UserDate

CompanyID property

The CompanyID property contains the intercompany ID (company database ID).

Syntax

object.CompanyID

Example

The following is an example of initializing the connection string to specify a default
database. In this case it is set to the current company. This could be set to a constant
database, such as GPDAT.

MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID

UserID property

The UserID property contains the current User ID.

Syntax

object.UserID

UserName property

The UserName property contains the name of the current user.

Syntax

object.UserName

Example

The following is an example of creating a string to update the customer name in the customer master table.

updatecommand = "update RM00101 set [CUSTNAME]='IM Customer' where [CUSTNMBR]='AARONFIT0022'"

DataSource property

The DataSource property contains the name of the current data source that is being used in Microsoft Dynamics GP.

Syntax

object.DataSource

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Dropping Timestamps from SQL Server and Text File Data Sources in Integration Manager

As promissed, the following post deals with managing those rogued timestamps in SQL Server and text file data sources. This problem, contrary to what anyone would think, is very common among Integration Manager developers and has haunted newsgroups and message boards with questions on how to effect such conversions. I have devised two methods that are effective in removing timestamps from data sources. The first method deals with the problem at its source — the source query. The second method utilizes VBScript to remove the timestamp.

SQL Server Query Method (Source Query)

To drop a timestamp from a SQL Server data source, you will need to setup a simple ODBC or advance ODBC data source. The query will have to make use of the SQL Server convert function to remove the timestamp from the datetime field as follows:

select field1, field2,…, convert(datetime, convert(char(15), thedatefield, 101)) as newdatefield from your_table where your_condition

Note the dual conversion, from date to char and back to a datetime expression.

VBScipt Method (Date Field Script)

In order to apply this method, you will need to open the script editor for the date field and enter the following script:

tDate = SourceFields(“SourceQuery.thedatefield”)
newDateField = right(“00″ & Month(tDate), 2) & “/” & right(“00″ &_
Day(tDate), 2) & “/” & year(tDate)
CurrentField.Value = newDateField

The neatness of this script rely on the ability to set the dows (leading zeroes) in front of the month and day values without extending the script beyond 3 lines.

Hope you liked these two simple, yet useful methods of dropping timestamps from data sources.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Supported Date Formats in Integration Manager

The topic of date formats supported by Integration Manager comes up very often in discussion boards and online community forums around the web, especially overseas where date formats tend to be distinct from the typical US format. Even here in the US, it is necessary to make certain adjustments to the date format if characters other than the forward slash (“/”) are introduced as separators.

Currently, Integration Manager will accept date formats that are in the order of month-day-year. Using the date 05/01/08, the following formats will be acceptable within your source file or table:

MM/DD/YY ————— 05/01/08
MM/DD/YYYY ———— 05/01/2008
M/D/YY ——————- 5/1/08
M/D/YYYY —————- 5/1/2008
MMDDYY —————– 050108
MMDDYYYY ————– 05012008

When converting other date formats, it will be necessary to introduce VBScript into the mapping of the date field to standardize to any of the aforementioned formats.

Let’s assume a transaction date of ‘20080501’ (YYYYMMDD) is given in a file, to be converted to the traditional US format of MM/DD/YYYY. The following VBScript would be added to the transaction date field, as follow:

‘Assign the source date to a local script variable
sDate = SourceFields(“Query.Transaction_Date”)
fDate = Mid(sDate, 5, 4) & Mid(sDate, 1, 4) ‘ Move the year at the end of the date string
CurrentField.Value = fDate ‘ Map the transposed date field to the transaction field

This post has dealt with the topic of date formatting. My next topic will talk about dropping timestamps from source date fields using SQL and VBScript, depending on the source.

As always, drop me a note letting me know what you think about this post or commenting on your personal experiences with the subject.

Until then,

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Supported Date Formats in Integration Manager

The topic of date formats supported by Integration Manager comes up very often in discussion boards and online community forums around the web, especially overseas where date formats tend to be distinct from the typical US format. Even here in the US, it is necessary to make certain adjustments to the date format if characters other than the forward slash (“/”) are introduced as separators.

Currently, Integration Manager will accept date formats that are in the order of month-day-year. Using the date 05/01/08, the following formats will be acceptable within your source file or table:

MM/DD/YY ————— 05/01/08
MM/DD/YYYY ———— 05/01/2008
M/D/YY ——————- 5/1/08
M/D/YYYY —————- 5/1/2008
MMDDYY —————– 050108
MMDDYYYY ————– 05012008

When converting other date formats, it will be necessary to introduce VBScript into the mapping of the date field to standardize to any of the aforementioned formats.

Let’s assume a transaction date of ‘20080501′ (YYYYMMDD) is given in a file, to be converted to the traditional US format of MM/DD/YYYY. The following VBScript would be added to the transaction date field, as follow:

' Created by Mariano Gomez, MVP' No warranties conferred, express or implied

' Assign the source date to a local script variablesDate = SourceFields("Query.Transaction_Date")fDate = Mid(sDate, 5, 4) & Mid(sDate, 1, 4) ' Move the year at the end of the date stringCurrentField.Value = fDate ' Map the transposed date field to the transaction field

Now let’s assume your date format is DD/MM/YYYY. The following script will properly format the date for Integration Manager to MM/DD/YYYY:

' Created by Mariano Gomez, MVP' No warranties conferred, express or implied

' Assign the source date to a local script variablesDate = SourceFields("Query.Transaction_Date")fDate = Mid(sDate, 4, 2) & Mid(sDate, 1, 2) & Mid(sDate, 7, 4)CurrentField.Value = fDate ' Map the transposed date field to the transaction field

This post has dealt with the topic of date formatting. My next topic will talk about dropping timestamps from source date fields using SQL and VBScript, depending on the source.

As always, drop me a note letting me know what you think about this post or commenting on your personal experiences with the subject.

Until then,

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com