Monday, June 30, 2008

New Article on MSDynamicsWorld: In-Transit Transfer Transactions

Dear readers,

Check my new article at MSDynamicsWorld.com: "Here's a Creative Exercise you Probably Won't Miss: Allocating Inventory in Transit Using Microsoft Dynamics GP". The article recollects the old tricks used to manage in-transit inventory and explores the new In-Transit Transfer Transactions feature in Microsoft Dynamics GP 10.0.

Once again, thanks to David Gumpert, Managing Partner and Editorial Director at MSDynamicsWorld.com for allowing me the chance to exercise my second passion -- writing.

Until next post!

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

Wednesday, June 25, 2008

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/

Friday, June 20, 2008

More Deployment Options for Microsoft Dynamics GP: P2P Networking

News has surfaced on the US Dynamics GP Field Team blog that Peer-to-Peer (P2P) networking is now supported as part of the growing number of deployment options. Needless to say that P2P is only suitable for very small operations and may require one of the participating workstations to have suitable capacity to run SQL Server Express 2005 and Microsoft Dynamics GP all at once, while accepting incoming requests for posting and all the various operations involved with the system.

The article also clarifies that P2P has only been tested on machines running Windows XP, but expect to have results soon for environments running Windows Vista.

Until next post!

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

Thursday, June 19, 2008

Your Must-Have CRM Checklist: 10 Factors to CRM Success

This free live webinar will be conducted on June 26, 2008 at 10:00 AM PDT / 1:00 PM EDT with the sponsorship of Microsoft Dynamics CRM Online and InsideCRM.com. If you are interested, register here.

Until next post!

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

Wednesday, June 18, 2008

Enabling Address Third Line in Microsoft Dynamics GP Report Writer

Ok, I felt the need to write about this because I still cannot understand why, after so many years, the Microsoft Dynamics GP development team cannot extend the courtesy of adding the extra calculated field required to display the address third line for customers, vendors, etc., and anyone who happens to have a third line in their address.

Case in point, if your address happens to be:

1972 Western Boulevard
Tower C
Suite 400
COLUMBUS, OH 43203

Dynamics GP will display on all its reports:

1972 Western Boulevard
Tower C
COLUMBUS, OH 43203


Now, that's just a bummer! Anyways, to correct this issue you will need to modify all reports where you need to display a third line by incorporating a fourth calculated field -- follow whatever convention used by Microsoft to create the calc field as it will make your life easier. Your address calculated fields should look something like this:

Address Line 1 = Function_Script( rw_SelectAddLine 1, "", Address1, Address2, Address3, "", City, State, Zip, "")

Address Line 2 = Function_Script( rw_SelectAddLine 2, "", Address1, Address2, Address3, "", City, State, Zip, "")

Address Line 3 = Function_Script( rw_SelectAddLine 3, "", Address1, Address2, Address3, "", City, State, Zip, "")

Address Line 4 = Function_Script( rw_SelectAddLine 4, "", Address1, Address2, Address3, "", City, State, Zip, "")

NOTE: I use commas for readability sake in this post, but in Report Writer these will not exist between each parameter of the function script. Normally, you would find two blank spaces ("" "") after the Address2 position in the function script. Highlight the first blank space, click remove, and chose Address3 from the appropriate table (RM Customer Address Master, PM Vendor Address Master, Company Address Master, etc.) and insert in its place.

You will also have to pay attention to certain reports, since you may encounter address fields on the Report Header and Page Header sections. In this case, you will have to drag your new AddresLine4 calculated field to both sections. In addition, special attention is required for the field properties on both sections as usually they are different. To have access to the field properties, you can double click on say for example AddressLine3 on each section and replicate the same properties to AddressLine 4 to ensure you obtain the right data.

Until next post!

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

Retrieving Microsoft eConnect 10.0 Version


There are two methods that will allow you to retrieve the exact version and build number in eConnect 10.0.


Method 1

In Windows, go to Start > Programs > Microsoft Dynamics > eConnect 10 > Release Information > Release Info. You can verify the Connection String by clicking on Connection. Once this is done, click on Retrieve and the window will be populated with the exact version of eConnect.
Method 2
You can run the following SQL script in SQL Server Management Studio Query window against your DYNAMICS database:
exec DYNAMICS..taeConnectVersionInfoDYNAMICS
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Thursday, June 12, 2008

More Microsoft SQL Server 2008 News!


Fresh from Tech-Ed: Microsoft unveils SQL Server 2008 release candidate (RC) which has been declared the last public release before release to manufacturing in Q3, 2008. For now, SQL Server 2008 Release Candidate (dubbed RC0) has been made available to the general public. If you are interested in testing the product (if you haven't already done so with TCP version), you can click here to obtain a copy.

Until next post!

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

The Little Known Secrets of T-SQL SERVERPROPERTY Function

Ok, you walk into this new customer site that you just picked up as a result of a change of VAR. You know nothing about their SQL Server installation and/or whether it is properly configured with all the required service packs. How would you like to be able to pull a bunch of SQL Server information without having to search the entire Internet for it, or search the entire SQL Server Books Online help file, bother the DBA, or better yet, call me :-).

T-SQL has a very little known function called SERVERPROPERTY which provides a vast number of arguments that allow you to obtain tremendous amount of information about your SQL Server. The following is a list of my favorite propterties and the most requested by consultants, DBAs, and users alike:

Syntax

SERVERPROPERTY (propertyname)

Properties

  • Collation: returns the name of the default collation being used on your SQL Server
  • Edition: Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, such as maximum number of CPUs, that are supported by the installed product.
    Returns:
    'Desktop Engine' (Not available for SQL Server 2005.)
    'Developer Edition'
    'Enterprise Edition'
    'Enterprise Evaluation Edition'
    'Personal Edition'(Not available for SQL Server 2005.)
    'Standard Edition'
    'Express Edition'
    'Express Edition with Advanced Services'
    'Workgroup Edition'
    'Windows Embedded SQL'
  • EngineEdition: Database Engine edition of the instance of SQL Server installed on the server.
    1 = Personal or Desktop Engine (Not available for SQL Server 2005.)
    2 = Standard (This is returned for Standard and Workgroup.)
    3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and Developer.)
    4 = Express (This is returned for Express, Express Edition with Advanced Services, and Windows Embedded SQL.)
  • InstanceName: Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input is not valid, or error. In this case you may want to try using the ServerName property.
  • IsIntegratedSecurityOnly: Server is in Integrated Security mode. I found this one particularly useful to troubleshoot SQL Server installations for GP, especially when the server has been 'accidentally' configured for Integrated Security instead of Mixed Mode.1 = Integrated security. 0 = Not Integrated security. NULL = Input is not valid, or an error.
  • LicenseType: Mode of this instance of SQL Server.
    PER_SEAT = Per Seat mode
    PER_PROCESSOR = Per-processor mode
    DISABLED = Licensing is disabled.
  • ProductVersion: Version of the instance of SQL Server, in the form of 'major.minor.build'. Can also be obtain with SELECT @@VERSION
  • SQLSortOrderName: The SQL sort order name from the collation.
Here are couple examples of the results I obtained while running SERVERPROPERTY against my SQL Server:

select serverproperty('Collation')
---------------------------------------------------------------------------
SQL_Latin1_General_CP1_CI_AS


select serverproperty('Edition')
---------------------------------------------------------------------------

Standard Edition

select serverproperty('EngineEdition')
---------------------------------------------------------------------------
2


select serverproperty('ServerName')
---------------------------------------------------------------------------

MGB001

select serverproperty('IsIntegratedSecurityOnly')
---------------------------------------------------------------------------

0

select serverproperty('LicenseType')
---------------------------------------------------------------------------

DISABLED

select serverproperty('ProductVersion')
---------------------------------------------------------------------------

9.00.3054.00


select serverproperty('SQLSortOrderName')
---------------------------------------------------------------------------
nocase_iso

I hope you find SERVERPROPERTY a very usefull function and the one stop shop for a bunch of information on SQL Server properties. If you would like the complete list of properties dont visit http://msdn.microsoft.com/en-us/library/ms174396.aspx or open SQL Server Books Online and type in SERVERPROPERTY in the Look For line.

Until next post!

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

Use Microsoft Dynamics GP to Take the Pain and Expense Out of CPA and Bank Audits

Check my new article as MS Dynamics World! The article exposes the use of window and record level OLE container objects (clip notes) in Microsoft Dynamics GP and how they can be leveraged to provide basic document management capabilities, directly supporiting accounting departments with CPA and bank audits.

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.GPConnUserDate
MsgBox GPConnection.GPConnInterCompanyID
MsgBox GPConnection.GPConnUserID
MsgBox GPConnection.GPConnUserName
MsgBox 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/

Wednesday, June 11, 2008

Reseting Inventory Quantity On hand and Quantity Available in Inventory

During a normal implementation, customers using the Inventory module are faced with testing numerous transaction scenarious prior to going live with their Dynamics GP system. However, the conversion to the live company requires transfering the Inventory Master tables, including the Item Quantity Master (IV00102) table. This table hosts the Quantity On Hand and Quantity Available fields per site which can already be affected by the transactions previously tested.

In order to reset the values in these fields without any SQL query involved (ok, not completely), you can try this simple solution: create a physical count for all items!

1) Create a stock count schedule for all items. Go to Transactions > Inventory > Stock Count Schedule. Enter a Stock Count ID, i.e., CLRQTYS.
2) Mass Add all items in your inventory by any criteria
3) Start the count by clicking on the Start button. Close the Stock Count Schedule window.

NOTE: you can choose to uncheck the Reuse Stock Count checkmark.

4) Open the Stock Count Entry window. Go to Transactions > Inventory > Stock Count Entry. Mark all items as counted without entering any values (they are automatically defaulted to zero).

NOTE: If the company has a great number of products, the items can be marked as counted with the following SQL script

declare @stockcountID char(20)
set @stockcountID = 'CLRQTYS' -- replace for the actual stock count you created
update IV10300 set ATPSTVRNC = 1 where stckcntid = @stockcountID
update IV10301 set VERIFIED = 1 where stckcntid = @stockcountID


5) Post the count. Click on the Process button to process and post the count.

Now you are good to transfer your Inventory Master tables!

Until next post!

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

Tuesday, June 10, 2008

Microsoft Dynamics Communities New House

I knew all those "Service Unavailable" messages could not be just another glitch in some Microsoft server. News has surfaced that the Microsoft Dynamics Community will be moving soon, featuring an improved interface and for ease of access to experts in various areas. The following is a transcript as released in the Dynamics GP community earlier today.

Within the next few weeks you will begin to see the Microsoft Dynamics Community pages located at http://www.microsoft.com/dynamics/community, moving to a new location at https://community.dynamics.com. We are making this move in an effort to streamline the accessibility of all Microsoft Dynamics communities and make the user experience of interacting with community experts and your peers much easier. The newsgroups as you see them today will still be intact, but the web pages which you use today to access them will be different. Plus you’ll see more streamlined content and future functionality additions, such as networking tools!To get a jump start on the transition, I would encourage you to register on the new site TODAY – once you do, you can begin to interact with our non-technical role based communities which reside there today for Finance, Sales & Marketing, and Customer Service professionals.

--
Liz Hallen
Online Community Product Manager

For now, update your favorites with the new address and enjoy the new navigation pane as it will allow you to hop from community to community without remembering every single URL.

Until next post!

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


How to Transfer Microsoft Dynamics GP Installation to a New Server Running Microsoft SQL Server

Often customers upgrade their hardware infrastructure, requiring Microsoft Dynamics GP databases to be migrated to the new environment. The steps outlined by KB article 878449 (requires access to Microsoft CustomerSource or PartnerSource sites) allow you to achieve just this without much headaches. If followed in detail, the results will speak for themselves. As always, before attempting any of these procedures, have a complete backup of ALL your SQL Server data (company databases, DYNAMICS database, and system databases).

Until next post!

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

Monday, June 9, 2008

More DEX.INI Settings!

Thanks to Leslie Vail, MVP for taking the time to compiling what I consider a very extensive list of DEX.INI settings used in both the Dexterity development environment and Microsoft Dynamics GP. Some of these settings can be very damaging to your data and will require you to have precise knowledge (and backups) before you play around with them.

You can click here to download a Microsoft Word document.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Sunday, June 8, 2008

Error message when trying to install Microsoft Dynamics GP 10: ".NET Framework 2.0 is not installed. The installation cannot continue"

For those of you attempting to install GP 10, you could come across any of 2 error messages indicating that the .NET Framework 2.0 is not installed, when in fact it is, and furthermore, you have applied SP1 and/or installed any new versions of the Framework (i.e., 3.0 or 3.5):

Error message 1
.NET Framework 2.0 is not installed. The installation cannot continue.

Error message 2
Product: Dexterity Shared Components -- .NET Framework 2.0 is not installed. The installation cannot continue. Then, the installation is ended.

To overcome any of these error messages, follow KB article 947539 to resolve. In summary, you will have to copy the contents of the Dynamics GP CD1 to your hard drive, follow 2 steps outlined by the KB article -- includes downloading 2 funky .MST programs (DexSkipDotNetCheck.mst and SkipDotNetCheck.mst) to bypass the .NET Framework 2.0 check, and run the installation MSI with a cryptic set of parameters that will allow GP to be installed.

Until next post!

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

Thursday, June 5, 2008

Resolving SQL Duplicate Key Error Message When Executing RM Paid Transaction Removal

This is one of those issues that I cannot fathom why the Microsoft Dynamics GP development team has not address in its RM Paid Transaction Removal procedure. It seems when the RM module gets "out of sync" -- usually after a crash of some sort -- it will cause transaction records to post between the RM Open table (RM20101) and the RM History table (RM30101). Unfortunately, for the end-user the problem is only evident when executing the Paid Transaction Removal operation, since they are likely to experience the following error message:

(Microsoft)(SQL Native Client)(SQL Server)Cannot insert duplicate key row in object 'dbo.RM30101' with unique index 'AK3RM30101'.

In addition, rebuilding the RM Key table (RM00401) does not solve the problem as the system will not know what to do with the same record found on both open and history tables. Given this situation, I have written the following query to identify and help in resolving the issue -- the query must be executed against the company database:


SELECT a.rmdtypal, a.docnumbr, b.rmdtypal, b.docnumbr
FROM RM20101 a
INNER JOIN RM30101 b ON (a.rmdtypal = b.rmdtypal) AND (a.docnumbr = b.docnumbr)

Once it is determined which document(s) is causing the problem, additional research will need to be conducted to establish which of the two records is the valid one and remove from either table (RM20101 or RM30101) accordingly. Establish if the document is fully applied and also check the Document Status (DCSTATUS) field in the RM Key table (RM00401) -- 0: Reserved, 1: Work, 2: Open, 3: History.

Until next post!

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

Feature Pack 1 for Microsoft Dynamics GP 10.0 US English and International English Now Available

Microsoft has released the long awaited Feature Pack 1 for Dynamics GP for its US and International English speaking customers and partners. Feature Pack 1 includes a number of new components, such as Business Portal Migration Utility, Business Portal Security Synchronization Utility, Developer Toolkit for Microsoft Dynamics GP 10, eConnect SDK, eExpense, Field Service Anywhere, Office SmartTag Manager, and PSTL, while including and extensive number of fixes to existing products (Service Pack 2).

PartnerSource
The product can be downloaded here

CustomerSource
The product can be downloaded here

Until next post!

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

UPDATES TO THIS ARTICLE:
12/23/2008 - Added CustomerSource link for FP1

Wednesday, June 4, 2008

Changing Microsoft Dynamics GP 10.0 Home Page Background Color


Microsoft Dynamics GP 10.0 Home Page is one of those application elements users and developers tend to be extremely intrigued about. It provides the ability to view To Do's, pending Outlook email messages, incorporate Metrics, add shortcuts to Reports and ability to incorporate custom links, but how come the user is not able to define certain preferences like colors and other features?
Some of these "secrets" are programmed in the HomePage.xsl stylesheet file, which can be located in the Background directory under the Dynamics GP installation folder (Program Files\Microsoft Dynamics\GP\Background).

The file can be edited with Visual Studio or Notepad for that matter. Once opened, search for:

background-color:#F9F9F9

This tag can be found in the body tag of the html section of the document. If you happen to be editing with Visual Studio, you can open a dummy Win32 project and play with the background colors of the window. When you find the right color, take note of the HEX code and replace the above (#F9F9F9) with the code in VS.
Keep in mind that many of GP's homepage elements are also white by default and will need to be changed accordingly to match whatever background color you choose.
Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Tuesday, June 3, 2008

Release of SQL Server 2005 Service Pack 3 Raises Concerns Over SQL Server 2008 Shipping Date

According to Redmond Developer News, Microsoft will be releasing SQL Server 2005 Service Pack 3 this summer, raising concerns on the release date of SQL Server 2008. While Microsoft has assured the general public that the RTM version still on track for Q3, it may not come as a surprise if the product slips into Q4. Read the full story here, but in the mean time, if you are performing extensive testing on the CTP version, submit all your bugs to Microsoft as SQL Server 2008 may not be released on time if the features cannot be fully tested.

Until next post!

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

Mark Polino's Integration Manager Rant

Visit Mark's blog to get an insight into his love/hate relationship with Integration Manager and how Microsoft should once and for all unleash the power of the product. Mark makes compelling arguments that are thought provocative, but at the same time cannot be ignored if you happen to be an Integration Manager user or developer.

Until next post!

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