MVP Mark Polino on Transaction Posting vs Batch Posting

If you thought Post to GL and Post through GL were confusing, wait until you have to explain batch posting versus transaction posting and the implications of either to your client. Fortunately, MVP Mark Polino breaks it down in easy terms in his new Weekly Dynamic article on the subject.

Remember that every implementation is different and that the options you select during configuration will impact the way the business conducts its operations, so go on and read Mark's article to get some insight on how these options work.

Until next post!

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

Google it with Bing!

You know that I don't often deviate from everything Dynamics GP, but this is got to be the most hilarious stuff I have seen in the last ... who knows how long.



Sorry Microsofties, but even you have to admit this is too dagarn funny.

Until next post!

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

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company - The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.



In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my 'sa' user and open the Clear Data window and try to do something there.



Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.



If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID's SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVP
DELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those 'old' Dynamics maintenance utilities.

Until next post!

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

How are Payables transactions work error messages stored?

This question came up in the Dynamics GP Partner forum a few days ago, the specifics inquiried by the consultant were as follow:

"I was just looking at the PM10000 table in SQL Server Management Studio. I found a column named PMWRKMSG, with a data type of binary(4), which I cannot understand. In addition, I found entries like, 0x00019000, 0x00009000, 0x00000000 as column values. I know that they represent error messages, generated on the Batch Edit List and Posting Journals, but how can I check which message is being generated by just looking at this column? Do I need to convert this entry to some other integer and refer to some other table, which in turn would have a list of all the error messages?"

Answer

The 'PM WORK Messages' field (PM10000.PMWRKMSG column) is a Dexterity multi-select listbox control (so are the 'PM WORK Messages 2' and the 'PM Distribution Messages' fields) which contains static texts. The only way of storing such controls (with the listbox values checked or unchecked) on SQL Server is by using a binary data type. During the posting process, a number of failed validation rules will trigger any of the 32 static text values -- added by the development team -- to be checked in the multi-select listbox control.

The static texts (and possible errors you may receive during posting) for the 'PM WORK Messages' multi-select listbox control are:

No vendor record exists for this vendor ID.
This vendor is inactive.
This transaction already has been posted.
Duplicate check numbers are not allowed.
Duplicate invoice numbers are not allowed.
No record exists for this credit card.
No unique voucher numbers are available.
This transaction has been posted and fully paid.
Distributions for this transaction contain errors.
Vendor summary records cannot be updated.
This transaction is recurring; it cannot include a payment amount.
This document should not be applied to other documents.
The total applied amount is incorrect.
Tax detail information is incorrect.
Vendor ID is on hold
Withholding Vendor ID is invalid
Applied record is on hold
Batch information is invalid
GL posting date is invalid
Fiscal period for the posting date does not exist
Fiscal period for the posting date is closed
Taxes Incorrectly Distributed
This transaction contains multicurrency error(s).
This transaction contains errors. It won't be posted.
Transaction Analysis information for this transaction is incorrect or missing.
Transaction contains intercompany distributions; mark as an IC transaction.
Intercompany Processing is not registered;cannot post intercompany transactions
The currency must be either the functional currency,
The currency must be the same as the currency assigned to the checkbook
the Euro currency,
an enabled denomination currency,
or the same as the currency assigned to the checkbook

Assuming the values follow a binary storage pattern, 0x00009000, can be converted to its decimal equivalent of 36,864. In turn, this number can be represented as 2^15 + 2^12. If is the case, the error messages displayed on the batch edit list are: "Withholding Vendor ID is invalid" and "The total applied amount is incorrect". In the case of 0x00019000, this is 2^16 + 2^15 + 2^12, this would result in the messages "Applied Record is on hold", "Withholding Vendor ID is invalid" and "The total applied amount is incorrect".

Of course, these errors can only be cleared via the interface, since resetting the value in SQL Server would have no direct effect on the validation rules applied by the posting process. A transaction will not move to the PM Transaction Open table (PM20000) if all the validation rules are not cleared, which in turn would render a value of 0x00000000 in the column.

The 'PM WORK Messages 2' multi-select listbox field stores the following static text values:

You cannot post to a tax date within a closed tax period.
You cannot post to a tax period that has not been set up.
The vendor's remit to address bank format is missing or inactive.
The bank format for the vendor and the checkbook must be the same country.
The bank format for the vendor and the checkbook must be different countries.
The bank format assigned to the checkbook is missing.
This check amount exceeds the maximum check amount for the checkbook.
Remit-To address doesn't exist; please enter a different address.

The above errors are mostly used during the validation of tax computations and vendors setup as EFT vendors.

The 'PM Distribution Messages' multi-select listbox field stores the following static text values:

The accounts payable distribution(s) does not equal the actual amount.
The purchases distribution(s) does not equal the actual amount.
The discount available distribution(s) does not equal the actual amount.
The trade discount distribution(s) does not equal the actual amount.
The discount taken distribution(s) does not equal the actual amount.
The misc distribution(s) does not equal the actual amount.
The freight distribution(s) does not equal the actual amount.
The tax distribution(s) does not equal the actual amount.
The cash distribution(s) does not equal the actual amount.
The write off distribution(s) does not equal the actual amount.
The other distribution(s) does not equal the actual amount.
The GST distribution(s) does not equal the actual amount.
The withholding distribution(s) does not equal the actual amount.
The debit distributions do not equal the credit distributions.
No account has been specified for one or more distributions.
The Realized Gain distribution(s) does not equal the actual amount.
The Realized Loss distribution(s) does not equal the actual amount.
The Round distribution(s) does not equal the actual amount.

Hope this helps in troubleshooting and understanding how Dynamics GP processes and manages errors when executing a Payables transaction posting validation.

Until next post!

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

Fixing Microsoft Dynamics GP and Illegal Characters error messages

By now you probably read David Musgrave's series on Microsoft Dynamics GP and Illegal Characters and have come to realize that the single quote character is perhaps one of the biggest culprits in the errors you are receiving when running reports such as your age trial balances in both receivables and payables. It's enough for the single quote character to be in your customer ID, vendor ID, or even document numbers to cause some error while running a report or inquiring a transaction.

The following is an actual case reported by a user on the Dynamics GP newsgroup when running the Payables Historical Aged Trial Balance report:

The stored procedure pmHistoricalAgedTrialBalance returned the following
result: DMBS: -127, Microsoft Dynamics GP: 0.

After some research the user realized they had a few document numbers that contained the illegal single quote character, for example INV'23002. Note that this case is specific to the document number.

So, what to do if you are a victim of the single quotes?

1) You will need to first identify the tables possibly affected. In particular, you will need to search all tables in the database that may contain the column storing the data -- in the case of the example, DOCNUMBR. For this you can use a script I published before here. You may also use the script provided by David Musgrave on Part 2 of his series, which would allow you to search through all your company databases if experiencing the issue in more than one company.

2) You will then need to identify the specific records affected by a single quote. Once the tables have been identified, you can do the following:

SELECT * FROM PM00400 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM10201 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM10300 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM10400 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM20000 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM30200 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM50100 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM80200 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM GL20000 WHERE ORDOCNUM LIKE '%''%'

NOTE: The GL20000 and GL30000 tables will store this information in the ORDOCNUM column.

3) Once you identify the document numbers you will need to have a plan to correct them. Your plan should include making sure the new resulting document number is not going to create a duplicate situation with an already existing record.

4) Update the affected records.

UPDATE PM00400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM10201 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM10300 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM10400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM20000 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM30200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM50100 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM80200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE GL20000 SET ORDOCNUM = 'NEW_DOC_NUM' WHERE ORDOCNUM = 'XY''Z'

Note the use of a double single quote to format the document number string properly and avoid an early string termination.

Since this situation can present itself in other modules, be sure to identify the specific module affected and the tables that may be impacted based on the data column storing the informatin. Hope this helps and complements the articles and information on Developing for Dynamics GP.

Until next post!

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

How does Check Links work?

This is a rather complex question. To understand how Check Links works one needs to step back a bit into the history of Microsoft Dynamics GP.

In its origins, Dynamics GP was conceived with the idea of running on multiple operating systems (Windows, Mac OS, Novell Netware), and "database" platforms, namely Ctree and Btrieve. The term database is a misnomer because in reality Ctree and Btrieve at the time were nothing more than Index Sequential Access Method (ISAM)-based file systems, with Btrieve being at best a record manager environment that could run on Netware and Windows. However, ISAM-based systems lacked referential integrity capabilities.

This brings me to Microsoft Dexterity -- the Microsoft Dynamics GP development environment. Within a Dexterity application, such as Dynamics GP, developers can define table structures and relationships that the Dexterity Runtime Engine then replicates into physical files depending on the file system platform. This table replication could be controlled via the DEX.INI file, with the FileHandler key.

FileHandler = Ctree

Since ISAM file systems had no ability to support referential integrity, the Dynamics development team had to create their own routines to analyze and maintain data integrity. The routines that were put into place were Shrink, Rebuild, Check Links, and Reconcile. In fact, back in the 80's and 90's if you called Great Plains Technical Support, the first thing you were always asked to do was to run a Shrink-Rebuild-Check Links-Reconcile.

The Shrink operation would remove any blank spaces between records, which was very common in ISAM-based platforms. Since ISAM files lacked the ability to compress files, records removed from GP's physical files would create logical gaps in the file itself, hence space was never released.

NOTE: Btrieve was modularized starting with version 6.15 and became one of two database backends that plugged into a standard software interface called the Micro-Kernel Database Engine. The other product is Scalable SQL, a relational database product that uses Structured Query Language, otherwise known as SQL. After several new versions were released the company was renamed to Pervasive Software and they now sell the product as Pervasive P-SQL. All three platforms were supported by Dynamics at some point in time.

The Rebuild operation would "attempt" to recreate table structures and place default records in them if needed. It was not uncommon, for example, for palette files (the predecessor of today's menus) to become corrupt and for system administrators to have to run a Rebuild operation to restore the default records in them.

Check Links however, is a different animal, so to speak. Check Links are entire procedures designed to analyze and remove any suspect records based on how tables are related in the application's dictionary. Hence, at the time it was highly recommended to take full backups of the physical files before running this maintenance utility. Check Links performs validations to make sure every record that is part of an atomic document(for example, a customer record, a purchase order, a sales order, an RM transaction, etc.) is valid within the context of that document. The check links algorithms sweep through the records in every table that is not the main table of a table group, then compare to the records in the main table. If there is any missing corresponding record in the main table, for the records currently being processed, these records in the table being processed are removed. For example, invoice line items cannot subsist without an invoice header record; invoice distributions, cannot subsist without a header record. But Check Links only verifies integrity. Accuracy is carried out by the Reconciliation processes. Hence, after running a Check Links, it is always recommended to run a Reconcile.

With the release of SQL Server, the development team decided that it was more cost efficient (time, effort, and money) to live with the table relationships and the routines to maintain data integrity within the Dynamics dictionary all the while creating the extensions needed to validate certain operations required in the Dynamics code only for SQL. For example, it was not unusual to find source code that was designed to work in one way if running on Ctree/Btrieve versus calling a stored procedure if working on SQL Server -- this is also known as selective code. After all, Ctree and Btrieve were still supported up until v7.5 and the code needed to work across all platforms supported. It was a lot easier to enhace parts of the application to support SQL Server rather than rewriting the code from scratch for the SQL platform. Introducing SQL Server also needed to be transparent to the 1000's of ISVs who had integrating applications and add-ons to GP that wanted to make their code available for SQL Server without major rewrites.

Back to Check Links... SQL Server also allowed the development team to create more efficient routines to analyze data integrity. Portions of Check Links, especially those related to table groups, have been migrated to SQL stored procedures. In fact, the following list of stored procedures execute Check Links operations and are called from the Dexterity Sanscript code based on the selected table group.

glCLAccountMSTR
pmCLApplyToOPENOPEN
pmCLApplyToWORKOPEN
pmCLDistributionWORKOPEN
pmCLHistoryLogicalTableGroup
pmCLKeyMSTR
pmCLManualPaymentWORK
pmCLMCRevaluation
pmCLMCTransactions
pmCLMoveFullyApplied
pmCLPaymentWORK
pmCLSchHdr
pmCLSchLine
pmCLTaxInvoices
pmCLTaxWORK
pmCLTransactionLogicalTableGrp
pmCLTransactionOPEN
pmCLTransactionWORK
rmCLAppliedOPEN
rmCLCashWORK
rmCLCommissionWORK
rmCLDistributionWORK
rmCLHistoryLogicalTableGroup
rmCLKeysMSTR
rmCLMCRevaluation
rmCLMCTransaction
rmCLRMOPEN
rmCLSalesWORK
rmCLSchHdr
rmCLSchLine
rmCLTaxWORK
rmCLTransactionLogicalTableGrp

So what happens to non-table groups? Certain tables are managed from the Dynamics interface with Sanscript code, certain other tables are not even checked as they may be self contained.

[Edit] David Musgrave also adds that you must "Be aware that Check Links can delete records it feels are damaged or orphaned. I have heard of valid data being removed. This has been known to occur with some ISV products which store data in GP tables, but Check Links does not recognise them.". The moral of the story: backup, backup, backup all your data!

Related Articles:

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server - David Musgrave @ Developing for Dynamics GP.

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued - David Musgrave @ Developing for Dynamics GP.

More Dex.ini Settings! - This Site

Until next post!

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

Retrieving Windows Registry key values with Microsoft Dexterity

Every so often you get these development requests that seem to push Dexterity to its limits. One of such requests is being able to read a Windows Registry key value using nothing more than SanScript.

The following example will show how to use the Microsoft Windows Management Instrumentation (WMI) Scripting library, ADVAPI32.DLL to retrieve a Windows Registry key value by examining how to retrieve the default Internet browser software being used.

The code will use the RegOpenKeyA and RegQueryValueExA DLL functions to a) return a handle for the registry path where we can found the key, b) then retrieve the actual key value. In order to access external DLL functions, it is necessary to create Dexterity prototype global procedures for the external DLL functions.

RegOpenKeyA@ADVAPI.DLL

{ prototype procedure RegOpenKeyA@ADVAPI.DLL                                    }
out   long   return_value;       {function returns ERROR_SUCCESS                }
in    long   hKey;               {Handle of parent key to open the new key under}
in    string lpcstr;             {Name of the key under hkey to open            }
inout long   phkey;              {Destination for the resulting Handle          }

RegQueryValueExA@ADVAPI32.DLL

{ prototype procedure RegQueryValueExA@ADVAPI32.DLL                             }
out   long   return_value;       {function returns ERROR_SUCCESS                }
in    long   hKey;               {handle of the key to query                    }
in    string sName;              {Name of value under hkey to query             }
in    long   lReserved;          {Reserved, must be null                        }
inout long   lType;              {Destination for the value type, or NULL if not}
                                 {required.                                     }
inout string sKeyValue;          {Destination for the values contents, or NULL  }
                                 {if not required.                              }
inout long   lResultLen;         {Size of sKeyValue, updated with the number of }
                                 {bytes returned.                               }

Now that we have the prototype functions, we can proceed to use the Constant Definition window in Dexterity to define values for each Registry hive.

Registry Hive Constants

Constant Name             Constant Value
HKEY_CLASSES_ROOT         2147483648
HKEY_CURRENT_USER         2147483649
HKEY_LOCAL_MACHINE        2147483650
HKEY_USERS                2147483651
HKEY_PERFORMANCE_DATA     2147483652
HKEY_CURRENT_CONFIG       2147483653

NOTE: These constants are usually known by their hexadecimal values, but Dexterity does treats hexadecimal constants as strings, hence the decimal notation used.

Once the prototype functions have been defined, we can wrap these in an API that isolates the developer from dealing with the innerworks of the calls. We will define to global functions as follow:

RegKeyExists

{ global function RegKeyExists }
function returns long phkey;

in long   hkey;
in string lpcstr;

local long return_value;

try
 extern 'RegOpenKeyA@ADVAPI32.dll'
  , return_value
  , hkey
  , lpcstr
  , phkey;
catch[EXCEPTION_CLASS_OBJECT_EXCEPTION]
 error "Error calling RegOpenKeyA@ADVAPI32.DLL. Could not locate DLL pointer.";
else
 throw;
end try;

RegKeyExists accepts the registry hive parameter (hkey) and the registry path and returns a handler if the path is valid (phkey).

RegGetKeyValue

{ global function RegGetKeyValue }
function returns string key_value;
in long hKey;

local long   return_value, lValueType, lValueLength;
local string sKeyValue;

set lValueLength to 255;

try
  extern 'RegQueryValueExA@ADVAPI32.DLL'
    , return_value
 , hKey
 , ""
 , 0
 , lValueType
 , sKeyValue
 , lValueLength;
catch[EXCEPTION_CLASS_OBJECT_EXCEPTION]
 error "Error calling RegQueryValueExA@ADVAPI32.DLL. Could not locate DLL pointer.";
else
 throw;
end try;

if return_value = OKAY then
 key_value = sKeyValue;
else
 key_value = "KEY_ERROR";
end if;

RegGetKeyValue in turn will take the handler (returned by RegKeyExists) and attempt to retrieve a value for the Default entry of the path previously given. If the function succeeds, it will return a string with the actual value, else the user will get a KEY_ERROR message.

Making it all work together..

For this project, I have created the following form:



The form contains 4 local variables:

'(L) RegistryHive': is a drop-down list with the following string values corresponding to each registry hive, as follows: HKEY_CLASSES_ROOT, HKEY_CURRENT_USER, HKEY_LOCAL_MACHINE, HKEY_USERS, HKEY_PERFORMANCE_DATA, and HKEY_CURRENT_CONFIG.

'(L) RegistryPath': is a string of 255 characters in length.

'(L) RegistryKey': is a string of 50 characters in length, not used in this project. I will leave this for a future post.

'(L) RegKeyValue': is a string of 255 characters in length. Set the property to Editable to False.

Now that you have the strings and drop-down list, drag the 'OK Button' push button control to finish -- I am working in the Dynamics dictionary, DYNAMICS.DIC.

The actual production form will look like this:



We can now add the following Sanscript code to the OK Button change script:

syGetRegistryKey OK Button K_CHG

local long  hKey;      { [In] Handle to an open key. }
local long   lHive;

local long   return_value, nSubkeys, nSubkeyMaxSize,nMaxChars, nValues, lValueName, lValueData, lClassLen;
local reference ft;
local string  lClass;

{ check for the last backslash character on the path string }
if substring('(L) RegistryPath', length('(L) RegistryPath'), 1) <> CH_BACKSLASH then
  '(L) RegistryPath' = '(L) RegistryPath' + CH_BACKSLASH;
end if;

{ check the hive selected from the DDL and assign the proper constant }
case '(L) RegistryHive'
  in [1] lHive = HKEY_CLASSES_ROOT;
  in [2] lHive = HKEY_CURRENT_USER;
  in [3] lHive = HKEY_LOCAL_MACHINE;
  in [4] lHive = HKEY_USERS;
  in [5] lHive = HKEY_PERFORMANCE_DATA;
  in [6] lHive = HKEY_CURRENT_CONFIG;
end case;

{ establish if the key entered is valid within the hive }
hKey = RegKeyExists(lHive, '(L) RegistryPath');

{ RegKeyExists will return 0 if the path does not exist within the hive,
  else it will return the handle value for the key
}

lClass = "";
lClassLen = 0;

if  hKey <> 0 then
 { Get the key value; will append the key to the path to make it whole }
 '(L) RegKeyValue' = trim(RegGetKeyValue(hKey));
else
 warning "Invalid registry key entered";
end if;

You can compile and run the code in test mode and use the Developer Assistant form to open the newly created form. For example, let's check the registry for the default Internet browser running:



I will be taking a shot a two other functions in the Microsoft WMI Scripting library in a futute installment. Hope you enjoy this article and can't wait to hear your comments.

Acknowledgements

A big thank you to Jon Eastman for his insight on the ADVAPI32.DLL. He gave me the idea for this post... that's what it's all about!

Until next post!

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

How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

One of the tasks that is often necessary in any Dynamics GP implementation is to get data across from the test company to the production "Go Live" company once we are ready to make the cut over. Those records are usually configuration and master records, but can also be beginning balances for subsidiary modules and General Ledger. This article will show you how to use Microsoft SQL Server 2008 Import and Export Wizard to transfer data across company databases.

The article assumes you are familiar with SQL Server Management Studio and that you know your way around databases and objects.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, and then type the sa user name and sa password. Click Connect.

3. In Object Explorer, double-click Databases. Right-click the database from which you want to export the data, point to Tasks, and then click Export Data.

4. In the Welcome window, click Next.



5. In the Data Source window, you can choose between Windows Authentication and SQL Server Authentication. It's always been my preference to use Windows Authentication, especially if your Windows user belongs to the SQL Server sysadmin role. If you are not sure, click Use SQL Server Authentication in the Authentication area, and then type the sa user name and sa password. Click Next.



6. In the Destination window, type the server name in the Server Name area. In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password. In the Database list, click to select the destination database. Then, click Next.



7. In the Specify Table Copy or Query window, click Next to accept the default entries.



NOTE: The table(s) to which you are exporting data must exist in the destination database. If the table does not exist, you must follow the installation routines for the add-on or third party to which the tables are related. You can also use the SQL Maintenance window to identify and create the missing table(s). Start Microsoft Dynamics GP and go to File > Maintenance > SQL. While the SQL Server Import and Export Wizard has the ability to create the missing destination tables, this feature will not replicate certain table constraints and indexes that may exist in the source table.

8. Select the check boxes next to the tables that you want to copy to the destination database.



9. Highlight all tables to be exported, then click on the Edit Mappings button. Select Delete rows in existing destination tables and Enable identity insert. If you use Analytical Accounting, and you transfer Analytical Accounting tables, you cannot ignore DEX_ROW_ID for the following tables: Customer Master - RM00101, Vendor Master - PM00200, Item Master - IV00101, and Site Setup - IV40700. Hence, as a standard practice I enable this option to transfer data. Click on OK to continue.




In the Select Source Tables and Views window, click Next.

10. In the Save and Run Package window, click Next to accept the default entries.




11. Now, sit back and watch your data go across!

General Notes

These steps were performed using Microsoft SQL Server 2008. The Import and Export Wizard is fairly standard between this version of SQL Server and Microsoft SQL Server 2005 and you should be able to obtain very similar results. Consult your Microsoft SQL Server documentation for further information on the Import and Export Wizard feature.

Related Articles

KB article 874208 - How to transfer setup information between company databases by using SQL Server 2005 or SQL Server 2000.

Moving data between Microsoft Dynamics GP companies - This site, click here.

The often overlooked, yet powerful Table Import - This site, click here.

Until next post!

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

New article on MSDynamicsWorld: "The Coming Shift in IT Department Priorities"

Strategies to gear up for the future

My new article is out on MSDynamicsWorld.com! This article focuses on the coming shifts in priorities inside IT departments running Microsoft Dynamics GP. The article is based on some of the strategic initiatives coming out of Redmond and carried around to customers and partners over the last few months. You can read the full article here.

Until next post!

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

Named in DynamicsWorld 2009 "100 most influential"

Ok! For those of you who are familiar with me, you may know that I don't blush too often (or maybe is a bit difficult to tell... ehem!), but this morning I woke up to the news that I am number 82 among DynamicsWorlds's 100 most influential people within the Microsoft Dynamics community for 2009.

I have the honor to share this spot with some truly awesome people: MVP Mark Polino, MVP Leslie Vail, Richard Whaley, and MVP Monzer Osama.

Now back to work!

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

Understanding the "Posting Accounts from Customer or Item" option in SOP

I have been a Microsoft Dynamics GP consultant for more than a decade now and have always found that a big point of contention (and confusion) during the implementation of Sales Order Processing in any environment has always been selecting whether posting accounts will default from the customer or from the item for the effects of creating an accounting distribution for transactions entered and processed.

From a business perspective...

Typically, organizations use one of these two criteria -- there may be many more -- for reporting profitability: by customer or by product, these two terms used in the broader sense.

Posting Accounts From can be found the Sales Order Processing Setup
window by going to MSDGP > Tools > Setup > Sales > Sales Order Processing

Accounting wise, Customer may refer to a segment in the chart of accounts that represent geographical market location (for example, sales territories, regions; or domestic customers vs international customers), or specific customer activity (for example, retail customers, wholesale customers, not-for-profits, etc). The end goal with this type of revenue and COGS segregation is to report profitability in the different markets where the organization does business. This will in turn allow the organization's executives to implement certain market strategies that are geared towards improving its bottom line in these markets, or in extreme cases, determine whether having a presence in a specific market makes business sense. It's also true that organizations that choose this revenue and COGS breakdown typically carry a small amount of product lines, with product costs and prices that do not vary that often.

Accounting wise, Item, may refer to a segment in the chart of account that represent different product lines (for example, analog, digital, embedded processing; or frozen, perishables, dry goods, meats, etc.), or a specific type of fabrication (for example, pinbrazing, vacuum brazing, induction brazing, etc). The end goal with this type of revenue and COGS segregation is to report profitability by the different products carried by the company. This will in turn allow the organization's executives to implement certain business strategies geared towards minimizing production costs, or find better suppliers of raw material or finished goods, or in extreme cases, determine whether having a certain product line makes business sense. It's also true that organizations that choose this revenue and COGS breakdown typically carry a wide array of products, where, contrary to the customer scenario, costs and prices may vary often due to market conditions.

Making the decision as to what option is best for the organization should always take into account the financial reporting structure for revenue and cost of sales.

From a technical perspective...

From a technical standpoint, Microsoft Dynamics GP's chart of account is static, meaning, the chart of account is configured and stored ("hardcoded") as a pre-requisite to any transaction activity in the system. This in turn imposes certain limitations in the way the system selects accounts at transaction time, but the most notorious limitation is the inability to match chart of account segments based on rules as opposed to selecting defaults. These limitations have given way to third party solutions that implement a rules based system to determine the account that must be used, for example eOne Integrated Business Solutions' Flexicoder. Flexicoder allows organizations with complex revenue and COGS reporting requirements to build rules for GL accounts used in Sales Order Processing transactions.

I hope you've found this article useful and that you understand how each posting account configuration option is related to an organization's financial reporting requirements.

Until next post!

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

New article on MSDynamicsWorld: MVP Frank Hamelly on "Getting your ERP house in order"

5 suggestions to prepare for the economic recovery

In his new article on MSDynamicsWorld, MVP Frank Hamelly provides insight on what your company should be doing NOW to take full advantage of the economic rebound. Frank asks "In an economic downturn, layoffs inevitably occur, leaving many companies more lean than they've been in years. While that's great when business volume is down, what happens when activity increases?". The question is, is your organization prepared?

Until next post!

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

"The Dynamics GP Blogster" now available on your Kindle with Amazon.com

You outta love technology! Kindles have certainly improved the way people interact with the written word. If you can't be away from that important troubleshooting tip, need a script that is only found here, or simple have a good read while you are waiting at the airport for that flight that will take you to your client's site, now you can catch my blog on Amazon Kindle.

It's risk free—all Kindle Blog subscriptions start with a 14-day free trial. You can cancel at any time during the free trial period. If you enjoy your subscription, do nothing and it will automatically continue at the regular monthly price.


Until next post!

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

Support Debugging Tool Build 11 released: a look at the company colour coding feature

As you may know, I have been working with Support Debugging Tool since the pre-release of build 9 and have been a contributor to the development and beta testing of the product since that time. Today marks the release of Build 11, the third installment of the product, in a marathonic 130-hour development effort by my friend David Musgrave. Read what David has to say and the myriad of enhancements introduced in this build in his release notes article.

The focus of this post is the new Company Colour Coding feature.

I am very excited about this feature for many reasons, but I will highlight two scenarios where I find it highly valuable:

1) With the introduction of Microsoft Dynamics GP 10.0 came the release of the Single Document Interface (SDI). SDI is a method of organizing graphical user interface applications into individual windows that the operating system's window manager handles separately. A window does not have a "background" or "parent" window containing its menu or toolbar; instead, each window contains its own menu or toolbar. Release 10 marked the departure from the Multiple Document Interface (MDI) that was known to hundreds of thousands of users around the world for more than 20 years.

However SDI introduced another challenge to the user community and the question did not wait: "I used to work with multiple instances of GP at the same time and had no problems differentiating my sessions. With this new interface how can I tell my companies apart?" While the usual response to this question was the company name on each window, this did not stop users from expressing their frustrations due to costly data entry errors, especially for users working in Terminal Server and Citrix environments.

2) Systems administrators always faced the challenge of clearly differentiating Microsoft Dynamics GP test and historical companies from their production counterparts, even with the introduction of the 'TEST' and 'HISTORICAL' tags in the company name to display a warning at login time, it was still not enough and could not save some users from entering transactions into the wrong company. For more information on setting up test and historical companies, click here.

Let's see how colour coding can help!

Support Debugging Tool now allows system administrators and support personnel to clearly distinguish Microsoft Dynamics GP companies by implementing colour codes for each company. In the case of scenario 1 and 2 above, conceivably, each production company could have varying colours, but all test and historical companies could be assigned a red colour, in which case all windows would inherit such colours.

To enable company colour coding follow these instructions:

1) Open Support Debugging Tool. Go to MSDGP > Tools > Support Debugging Tool or press CTRL+D on your keyboard.

2) Click on the Options button and chose Administrator Settings.



NOTE: The Administrator Settings option is only available when using the Advance Debugging Mode. For more information on how to enable the Advance Debugging Mode click here.

3) In the Administrator Settings window, click on the Company tab. This will display the options available to configure colour coding.



You can choose to display a dialog on login for test and historical companies along with displaying the company, user, or both on each window title. In any case, changes will not take effect until you log off and log back into Dynamics GP by either switching companies or by exiting and re-entering the application.

4) Activate company colour coding by clicking on the Activate Company based Colour Schemes checkmark.


You can then select colour schemes for the application background, windows background (recommended), and scrolling windows toolbars. Consult with your users to establish the best schemas for their needs and be sure to circulate an email with the chosen schemas.

Click Ok to continue.

5) Click on the Apply button in the Administrator Settings window to acknowledge the changes. Support Debugging Tool will then change all windows background, in the case of this example, including its own. Below are a few samples.



NOTE: the colour chosen for the windows is only for illustration purposes. Be sure to select colour schemes that will render the texts and prompts in the application readable and suite user requirements.

If you are not satisfield with the results, you can always reset the colours back to standard Microsoft Dynamics GP schemes. In order to assign colours to each company, be sure to log into that company and repeat steps 1 through 5.Happy colour coding!

As a final note, I truly enjoyed working with David and Robert Cavill on this build and I am very proud of the final results. Support Debugging Tool is loaded with features that partners and customers should be taking advantage of. In the next few days I will be releasing some more articles with the capabilities built into the tool, please check in regularly for the updates.

Related Articles

Support Debugging Tool Build 11 Released - David Musgrave @ Developing for Dynamics GP
Support Debugging Tool Redux - This site.
First Look at Support Debugging Tool for Microsoft Dynamics GP - This site.
All other Support Debugging Tool articles - This site.

Until next post!

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

Why you should upgrade to Microsoft SQL Server 2008 if you are using Microsoft Dynamics GP

The Microsoft Dynamics GP Product Management and Marketing team has published some performance stats that are good enough reasons to upgrade to Microsoft SQL Server 2008 if you are a Microsoft Dynamics GP customer running SQL Server 2005 or even SQL Server 2000. Their stats revolve around the use of SQL Server 2008's row compression and page compression features and their ability to save a good amount of storage space.

Don't forget that the vardecimal storage format, the predecesor of row compression and page compression, was made available in Microsoft SQL Server 2005 SP2 and can be enabled to save data storage once you understand all its capabilities. For more information on vardecimal storage format check the following article:

• Microsoft Technet: Reducing Database Size by Using Vardecimal Storage Format

For more information on Microsoft SQL Server 2008 row compression and page compression features visit the following articles:

• Microsoft Technet: Creating Compressed Tables and Indexes

• Microsoft SQL Server Storage Engine team: Types of Data Compression in SQL Server 2008

Please remember that Microsoft SQL Server 2008 is only supported if you are running Microsoft Dynamics GP v10 with Service Pack 2 (Build Number: 10.00.1061) or later. For information on Microsoft SQL Server versions and service packs, check my previous article here.

Until next post!

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

New Article on MSDynamicsWorld: MVP Mark Polino on the Dynamics GP Navigation Bar

MVP Mark Polino, in his new MSDynamicsWorld article narrates his adventures with Microsoft Dynamics GP v10 navigation bar. Mark says of the feature "Because it's combined with easy drill back, it's changing the way I use Dynamics GP and it's changing the way I show clients how to use it".

If you are still romanced (and glued) to the menu toolbar, I can understand the sentiment, but let me use another phrase that I have been hearing lately: "that's so 90's!". If you want to follow Mark's adventures with the navigation pane and lists, take a look at his articles from his Weekly Dynamic series:

• Module Homepages in Dynamics GP 10
• My grand UI experiment for GP
• Navigation Pane, Lists and Financial Data
• Sales and Lists
• Navigation, Lists, and Project Accounting

Feel free to drop Mark a comment on any of these articles and be sure to start your journey away from the Dynamics GP toolbar.

Until next post!

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

Retrieving dictionary build numbers outside of Dynamics GP

I am amazed at the useful little things you can find out without even trying too hard. Many times I ask users to provide me with the build number of their Dynamics GP dictionary or SmartList dictionary and, whether because of lack of familiarity with the product or simply the amount of information found around the Dynamics GP application set, this task can become a bit confusing.

Typically, you would click on Help > About Microsoft Dynamics GP to access dictionary build numbers under the About window, as shown in this picture.


Most dictionary information can be found below the Version Information section of the window. However, if you are an overall systems administrator and need to provide this same information to a support engineer you can do the same with the following steps:

1) Open Windows Explorer and navigate to the Dynamics GP installation folder, typically located under Program Files\Microsoft Dynamics\GP

2) Method 1: mouse hover the Dynamics dictionary (or any other dictionary you are interested in). Windows will display a summary of the file as shown below:


Note the dictionary information as shown by the file summary.

3) Method 2: Right-click on the dictionary file and select Properties from the menu. Next, click on the Dictionary tab. Windows will display the following information for the dictionary file.


As you can see, the Dictionary tab now displays the forms and reports dictionary files corresponding to this dictionary, in the example, FORMS.DIC and REPORTS.DIC respectively.

If the Advanced button is clicked, more information will be displayed including the dictionary launch ID (position within the DYNAMICS.SET file) and the dictionary's compatibility information.


As you can see, Microsoft Windows can offer most of the information you would obtain from within the application about a dictionary. Word of caution: this may only work if you have followed all installation procedures using the application installer.

Until next post!

MG.-
Mariano Gomez, MVP