Mohammad R. Daoud

Using credit cards to pay vendors in Dynamics GP

Daoudm — Sun, 04 Jan 2009 14:02:00 GMT

As usual! Victoria just came up with a new workaround for Credit Card Tracking, personally I liked the way she used, check her post below:

http://victoriayudin.com:80/2009/01/04/using-credit-cards-to-pay-vendors-in-dynamics-gp/

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/
http://mohdaoud.blogspot.com/

Copying Reports in Report Writer

Daoudm — Wed, 31 Dec 2008 23:30:04 GMT

By Frank Hamelly

1. Export the SOP Invoice package file using Customization Maintenance.
2. Open the package file using Notepad.
3. Perform a Find/Replace, replacing all the references to 'WORK' tables with 'HIST' tables. (i.e., replace SOP_HDR_WORK with SOP_HDR_HIST)
4. Change the report name in the file.
5. Save the package file.
6. Import the modified package file back into GP using Customization Maintenance.
7. Voila! Your report is modified.

An Error Occurred while using the BCP utility-- data was not correctly copied to the server....

Daoudm — Sun, 28 Dec 2008 13:00:55 GMT

Vaidy Mohan has turn our attention to a very known error these days on Vista operating system related to BCP Utility error:

Normally we used to go to users in control panel, remove "UAC feature" checkbox and wait to restart PC..................

Beside that this cannot be agreed by some of the users, I personally also hate to wait! Vaidy suggested another solution, which is only right clicking on GP shortcut and run it as Administrator :)

Thank you Vaidy for this solution.

Victoria Yudin on Reporting on Extender Data

Daoudm — Mon, 22 Dec 2008 15:48:24 GMT

By Mariano Gomez, MVP, MCP

Fellow MVP Victoria Yudin, goes to great lengths to explain how to report on Extender data. Extender can be a valuable time saving customization tool, but data storage can be confusing even to the pros. Remember that Extender stores data in separate tables according to data types and the only way compile the data is by creating Extender Views.
Also don't miss out on her previous article about Extender and SmartList.
Victoria provides great detail on how to accomplish this with screenshots that will help you understand the process from beginning to end. More importantly, let her know what you think about her work by dropping her a comment.

Support Debugging Tool Build 10 released

Daoudm — Mon, 22 Dec 2008 15:43:08 GMT

By David Musgrave

Some of you might know that I have been busy working on the next build of the Support Debugging Tool for Microsoft Dynamics GP. If you did not know... well... you do now.

I have a wish list of features based on feedback from colleagues and the partner community as well as my own ideas for enhancements. I have been slowly working my way through this list and even though there is still more work to be done, I wanted to give you the benefit of the work completed already.

So, we have released Build 10 of the tool on PartnerSource. The download links for the pages are below.

Here is a summary of the features and enhancements added:

ScreenShot
ScreenShot is a tool that will capture screen shots of all open windows in the Microsoft Dynamics GP system and email them along with a detailed System Summary Report and optional copies of the Dynamics.set and Dex.ini files.
Security Profiler
The Security Profiler window has been enhanced to provide more information about security issues. It can now be set to automatically open when an error occurs so that you can immediately see the cause. You can print the contents of the window or even better, export to file or email a log of the information. This log can be emailed to your administrator, so they can see the issues and the cause on their system.
Resource Information
The Resource Information window has been enhanced to provide better search capabilities with options for Exact Match, Begins With and Contains searching as well as turning off case sensitivity. A Search Again button has been added so that you can keep searching for resources that match your criteria in all available dictionaries.
Security Information
The new Security Information window can be opened using a right mouse click context sensitive menu or the Security button from the Security Profiler and Resource Information windows. This window will display a full break down of the security status for a selected resource for a specified user and company combination. It can then be used as a launch pad to open the security windows to make changes.
For v8.0 & v9.0: It will show the security settings for the user and the user's class.
For v10.0: It will show the Security Tasks and Roles that the user is assigned to that grant access to the resource and customisation selected from the Alternate/Modified Forms and Reports ID. It will also show the Security Tasks and Roles available on the system that are linked to the selected resource.

Administrator Settings
This new "Advanced Mode" window is used to set default settings for the tool, including the administrator's email address, whether ScreenShot adds the Dynamics.set and Dex.ini files as attachments and whether the Security Profiler window should open automatically when there are errors or warnings.

There have also been a number of minor tweaks to the code to improve functionality and performance. Please download this latest version and get it installed on all your customer sites.

Downloads

Support Debugging Tool for Microsoft Great Plains 8.0

Support Debugging Tool for Microsoft Dynamics GP 9.0

Support Debugging Tool for Microsoft Dynamics GP 10.0

Have a look at Mariano Gomez's great post with his take on the latest features added to Build 10.

Please post your feedback as comments, I would love to know how the new features work for you.

How to determine if a report or a form has been modified??

Daoudm — Mon, 22 Dec 2008 15:37:40 GMT

By Mariano Gomez, MVP, MCP

How many times have you walked into a GP implementation done by a previous VAR and cannot establish what changes have been done on a report, if any? Or how many times have you come across forms and reports dictionaries with tons of objects and cannot tell by simply looking at these if they have been changed or not? Or have you made some changes to a report a few years aback and now cannot remember what these changes were? I get this question every once in a while and finally someone was keen enough to post it on the Dynamics GP public newsgroup. Solution Let me start by saying that ALL modified forms and reports should ALWAYS be backed up in the form of package files, and that ALL these package files should be stored in a source code control repository -- for example, Visual Source Safe -- and versioned if all possible, with notes on all changes done from version to version. However, this is not always possible, especially if the company happens to be a small company with limited technical and software development resources -- unless of course, the business happens to do software development :-) ). In order to establish what changes have been done to a form or a report, without having a source control repository, you can use old fashioned Microsoft Word... well, I will be using Microsoft Word 2007 for this example. In addition, we will use a slightly modified version of the SOP Blank Invoice report.

*Click on image to enlarge

1) Export the modified report to a package file. Go to Microsoft Dynamics GP > Tools > Customize > Customization Maintenance. Highlight the SOP Blank Invoice report and click on Export. Save the file as SOPBlankInvoice_Modified.package

*Click on image to enlarge

2) From a workstation not pointing to the REPORTS.DIC dictionary file containing the modified report or from a standalone copy of GP, say for example the one you carry on your laptop, print the SOP Blank Invoice report to screen. Go to Transactions > Sales > Sales Transactions and choose an invoice. Print to screen. Click on Modify to open the report with Report Writer. Once the report is shown in the Report Layout window, return to Dynamics GP.

*Report with no customizations. Click on image to enlarge

3) Repeat step 1 on your standalone environment, this time saving the file as SOPBlankInvoice_Original.package. Move the SOPBlankInvoice_Modified.package file to the same directory with the SOPBlankInvoice_Original.package file. This will both files easily accessible.

*Click on image to enlarge

4) Open Microsoft Word 2007.

Click on the Review menu item.

*Click on image to enlarge

5) Click on the Compare option. Open your SOPBlankInvoice_Original.package in the Original document column, open SOPBlankInvoice_Modified.Package in the Revised document. Changes can be labeled with markers to allow for easy identification. Word will run the comparison and highlight any changes between the two documents

*Click on image to enlarge

Remember: it's not about the tools, it's about how you use these tools to meet your needs. I hope this article provides a mechanism to quickly and accurately identify changes between original reports and customized reports and help you get a headstart when working at customers and customizations you have not created -- or may have and don't recall :-) .

Until next post!

Microsoft Dynamics GP SMS Services!

Daoudm — Fri, 12 Dec 2008 11:40:00 GMT

Dear All,

I finally released the SMS services version of Microsoft Dynamics GP! You can get notified about all new orders, invoices, inquiries, security breaks and any kind of business alerts in your system directly to your phone!

Tool was developed based on .Net technology with extremely dynamic process and configuration, basically it’s using predefined triggers using any method available in GP, you can use Business Alerts, Reminders, Smart-List and any newly added records for triggering.

The tool is available with competitive price that’s nothing comparing to the benefits gained.

Please don’t hesitate to contact me for any further information about this tool or its rates.

Keywords: Microsoft Dynamics GP SMS; SMS Services; SMS Engine; SMS And Dynamics.

MSN: Dawood_2@hotmail.com

All about SOP distributions

Daoudm — Thu, 11 Dec 2008 00:06:00 GMT

By Mariano Gomez

Microsoft MVP Victoria Yudin seems to be everywhere in these days. If you have ever wondered how GP calculates all these pesky SOP invoice accounting distributions, wonder no more! Victoria decodes in a very friendly and illustrative manner how GP decides the fate of the accounting distributions to be posted to General Ledger when an invoice is posted in the Sales Order Processing module.

If I may add one more thing about the SOP distribution types, this field is used to categorize the distributions for the purpose of verifying the distributions on a document and can be framed in one of these 23 types:

1 = SALES

2 = RECV

3 = CASH

4 = TAKEN

5 = AVAIL

6 = TRADE

7 = FREIGHT

8 = MISC

9 = TAXES

10 = MARK

11 = COMMEXP

12 = COMMPAY

13 = OTHER

14 = COGS

15 = INV

16 = RETURN

17 = IN USE

18 = IN SERVICE

19 = DAMAGED

20 = UNIT

21 = DEPOSITS

22 = ROUND

23 = REBATE

The corresponding numeric value is stored in the SOP_Distribution_WORK_HIST table (SOP10102).

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

http://mohdaoud.blogspot.com

Identifying Duplicate Transactions

Daoudm — Wed, 10 Dec 2008 23:53:00 GMT

By David Musgrave

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx

Because Microsoft Dynamics GP is designed to have separate tables for WORK, OPEN and HISTORY transactions, there are times where it is possible for a transaction record to exist in more than one table. This is usually the result of an error or interruption.

These duplicate records become an issue when it comes time to move a transaction from one table to another. For example: when it is posted, fully applied, or paid transaction removal is used. At this time, the duplicate will generate a duplicate key error and the process will be aborted.

Another time when duplicate records can cause problems is when using an Inquiry window which can show data from more than one of the WORK, OPEN and HISTORY tables at the same time. To achieve this, the Inquiry windows transfer data into a single temporary table and use this table for their display. If duplicate records exist, you will get an error when the data is being copied into the temporary table.

An error message that may be generated by several inquiry windows, when there are duplicates, refers to a createSQLTmpTable stored procedure. This stored procedure does not actually exist, but is in fact referring to pass through SQL script called from the Dexterity code. For example: The error message from the Payables Transaction Inquiry window is The stored procedure createSQLTmpTable returned the following results, DMBS: 2627, Microsoft Dynamics GP: 0. Error 2627 is a SQL Cannot insert duplicate key error.

To make it easier to find duplicates, I am providing the following SQL queries to look for duplicate headers in the core Distribution and Financial modules. While these will not find every possible duplicate in every table, they are a great basis for checking for duplicate transactions.

SQL Script to look for duplicate headers in SOP, IVC, POP, RM, PM, IV, & GL modules

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Useful SQL Scripts Series

Daoudm — Wed, 10 Dec 2008 23:50:00 GMT

Guys,

David started his new piece, he’s working on SQL Scripts package that's needed to complement GP environment, checkout his blog below:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/useful-sql-scripts-series.aspx?CommentPosted=true#commentmessage

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Search all columns of all tables for a given search string and replace it with another string

Daoudm — Wed, 10 Dec 2008 10:03:00 GMT

Guys,

By: Narayana Vyas Kondreddi

My colleague and best friend Areej Neshewat just sent me the following stored procedure for knowledge sharing.
This procedure replaces any kind of strings in the database with other.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SearchAndReplace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SearchAndReplace]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC SearchAndReplace
(
                @SearchStr nvarchar(100),
                @ReplaceStr nvarchar(100)
)
AS
BEGIN

                -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
                -- Purpose: To search all columns of all tables for a given search string and replace it with another string
                -- Written by: Narayana Vyas Kondreddi
                -- Site: http://vyaskn.tripod.com
                -- Tested on: SQL Server 7.0 and SQL Server 2000
                -- Date modified: 2nd November 2002 13:50 GMT

                SET NOCOUNT ON

                DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
                SET @TableName = ''
                SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
                SET @RCTR = 0

                WHILE @TableName IS NOT NULL
                BEGIN
                                SET @ColumnName = ''
                                SET @TableName =
                                (
                                                SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                                                FROM INFORMATION_SCHEMA.TABLES
                                                WHERE                                 TABLE_TYPE = 'BASE TABLE'
                                                                AND       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                                                                AND       OBJECTPROPERTY(
                                                                                                OBJECT_ID(
                                                                                                                QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                                                                                                ), 'IsMSShipped'
                                                                                                       ) = 0
                                )

                                WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
                                BEGIN
                                                SET @ColumnName =
                                                (
                                                                SELECT MIN(QUOTENAME(COLUMN_NAME))
                                                                FROM INFORMATION_SCHEMA.COLUMNS
                                                                WHERE                                 TABLE_SCHEMA               = PARSENAME(@TableName, 2)
                                                                                AND       TABLE_NAME    = PARSENAME(@TableName, 1)
                                                                                AND       DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                                                                                AND       QUOTENAME(COLUMN_NAME) > @ColumnName
                                                )

                                                IF @ColumnName IS NOT NULL
                                                BEGIN
                                                                SET @SQL=         'UPDATE ' + @TableName +
                                                                                                ' SET ' + @ColumnName
                                                                                                + ' = REPLACE(' + @ColumnName + ', '
                                                                                                + QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
                                                                                                ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                                                                EXEC (@SQL)
                                                                SET @RCTR = @RCTR + @@ROWCOUNT
                                                END
                                END
                END

                SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Silent Installation for Dynamics GP

Daoudm — Mon, 08 Dec 2008 21:37:00 GMT

Guys,

If you are looking to install GP in silent mode without using packages, you may need to check command below, I found it as an answer to question in Microsoft Dynamics Forum:

msiexec /i "C:\path\bin\GreatPlains.msi" SQL_SERVER_NAME="server that is running Microsoft SQL Server" INSTALLDIR="installdirpath" SELECTED_COUNTRY="country" TRANSFORMS=:Inst0instance;c:\skipdotnetcheck.mst INSTANCE_NAME="instancename" MSINEWINSTANCE=1 /qb+

Along with that command from above, you can use the "ADDLOCAL" command to install certain modules from GP. I found a list of codes for each module and they seem to be valid (at least the several I have tried).

A4 - A4
AA - Analytical Accounting
BM - Bank Management
CPR - Canadian Payroll
CM - Collections Management
DDR - Direct Debit Refunds
PMEFT - EFT for Payables Management
RMEFT - EFT for Receivables Management
EREC - Electronic Bank Reconcile
EM - Encumbrance Management
EI - Enhanced Intrastat
EXTENDER - Extender
FAM - Fixed Asset management
GP - GP
GM - Grant Management
HRM - Human Resources
MFG - Manufacturing
MLCHECKS - Multilingual Checks
PDM - Payment Document Management
PA - Project Accounting
RED - Revenue/Expense Deferrals
SP - Safe Pay
SI - Scheduled Instalments
SLB - SmartList Builder

For instance, if you wanted to install Fixed Assets, Safe Pay, and Smartlist Builder you would put "ADDLOCAL=FAM,SP,SLB"

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Programmatically Closing for Dynamics GP

Daoudm — Sun, 07 Dec 2008 23:00:00 GMT

If you wish to automatically close Dynamics GP from .Net Application or VBA, just follow steps below:

In your Project, go to references and add a reference to “Dynamics Continuum Integration Library” then use the code below:

Dim CompilerApp As New Dynamics.Application
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String

Commands = ""
Commands = Commands & "local integer l_file_id; " & vbCrLf
Commands = Commands & "local string pathname; " & vbCrLf
Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP.MAC""; " &
vbCrLf
Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " &
vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, ""CommandExec form

BuiLtin command cmdQuitApplication; " & vbCrLf
Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
Commands = Commands & " run macro pathname; " & vbCrLf
Commands = Commands & "end if; " & vbCrLf

' Execute SanScript

CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

An available update may be required for your computer, but the update process couldn't verify information.

Daoudm — Sun, 07 Dec 2008 22:46:00 GMT

An available update may be required for your computer, but the update process couldn't verify information.

Sometimes when you setup client updates functionality in GP 10.0 and do not specify the correct information, the message above will appear upon login and the system will shutdown.

I found this question on the Dynamics Newsgroup and realized that it should be solved somewhere, the solution for this is only by going to Dynamics database, locate “SYUPDATE” table and delete the records saved there.

Hope this helps.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

How To Delete GP Company

Daoudm — Fri, 05 Dec 2008 23:37:00 GMT

I got allot of questions on how to delete company from GP, script below is the answer:

CAUTION: Do not delete the company from Enterprise Manager first (step 2). If this
is done, you will need to run SQL scripts to delete database tables individually. You
will need to delete any references to the company in several DYNAMICS tables. The
example below shows a list of some of the tables that might need to be removed.
Example:
Use DYNAMICS
Select * from DYNAMICS…SY01500
The result set shows you the CMPANYID and INTERID fields for your company. Run
the following script, replacing "-1" with CMPANYID and "TWO" with INTERID with
your company’s information:
Use DYNAMICS
go
delete MC60100 where CMPANYID = -1
delete MC60200 where CMPANYID = -1
delete SY00801 where CMPANYID = -1
delete SY01500 where CMPANYID = -1
delete SY01900 where CMPANYID = -1
delete SY02000 where CMPANYID = -1
delete SY02100 where CMPANYID = -1
delete SY05200 where CMPANYID = -1
delete SY40500 where CMPANYID = -1
delete SY60100 where CMPANYID = -1
delete UPR10300 where CMPANYID = -1
delete UPR10304 where CMPANYID = -1
delete W200002 where CMPANYID = -1
delete DU000020 where companyID = -1
delete DU000030 where companyID = -1
delete DB_Upgrade where db_name = 'TWO'

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/