Friday, April 30, 2010

Dynamics GP Reporting Series: Sales Invoice (Receivables Management)

For Sales invoices that does not contains items, you might need to print invoice using Transactions>> Sales>> Transaction Entry, the good thing is, MS guys already created view for the Receivables Transactions, below the SQL command needed:

SQL Command:

SELECT    
dbo.RM10301.DOCTYPE, dbo.RM10301.DOCDATE,
dbo.RM00102.ADDRESS1, dbo.RM00102.ADDRESS2,
dbo.RM00102.ADDRESS3, dbo.RM00102.CITY,
dbo.RM10301.DOCDESCR, dbo.RM10301.CUSTNMBR,
dbo.RM10301.CUSTNAME, dbo.RM10301.SLPRSNID,
dbo.RM10301.SHIPMTHD, dbo.RM10301.PYMTRMID,
dbo.RM10301.DOCAMNT, dbo.RM10301.SLSAMNT,
dbo.RM10301.MISCAMNT, dbo.RM10301.TAXAMNT,
dbo.RM10301.FRTAMNT, dbo.RM10301.TRDISAMT,
dbo.RM10301.CASHAMNT, dbo.RM10301.CHEKAMNT,
dbo.RM10301.CRCRDAMT, dbo.RM10301.CURNCYID,
dbo.RM10301.DOCNUMBR, dbo.ReceivablesTransactions.[Originating Cash Amount],
dbo.ReceivablesTransactions.[Originating Check Amount],
dbo.ReceivablesTransactions.[Originating Credit Card Amount],
dbo.ReceivablesTransactions.[Originating Current Trx Amount],
dbo.ReceivablesTransactions.[Originating Discount Taken Amount],
dbo.ReceivablesTransactions.[Originating Freight Amount],
dbo.ReceivablesTransactions.[Originating Misc Amount],
dbo.ReceivablesTransactions.[Originating Sales Amount],
dbo.ReceivablesTransactions.[Originating Tax Amount],
dbo.ReceivablesTransactions.[Originating Trade Discount Amount],
dbo.ReceivablesTransactions.[Originating Write Off Amount],
dbo.ReceivablesTransactions.[Currency ID]
FROM         dbo.RM10301
LEFT OUTER JOIN dbo.ReceivablesTransactions
ON dbo.RM10301.DOCNUMBR = dbo.ReceivablesTransactions.[Document Number]
LEFT OUTER JOIN dbo.RM00102 ON dbo.RM10301.CUSTNMBR = dbo.RM00102.CUSTNMBR
AND dbo.RM10301.ADRSCODE = dbo.RM00102.ADRSCODE
WHERE dbo.RM10301.DOCNUMBR = {?DOCNUMBER}

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Sales Invoice:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Tuesday, April 27, 2010

Database Monitoring (Transactions Operations Log)

Have you ever wanted to monitor one of your tables transaction for non GP tables that couldn’t be monitored by Activity Tracking? I been in a situation where the customer requested to log ALL transactions performed on a third party software, to achieve this you will have two options, one to use SQL Server Profiler to trace your data based on predefined filters and direct the results to an SQL table, which will be stopped if the profiler closed, or your other option is to use SQL triggers to perform insert operation on a monitoring table I called “TransactionsLog”:

image

Now the tough part is to create triggers on your tables, each table will need to have 3 triggers, one to monitor “Insert”, and one to monitor “Update” and the last one to monitor “Delete”.

Having a big number of tables will make such task a nightmare! Where I had to write the script generator below that generates those triggers for you! It writes you the needed triggers including the primary key field –if it was single – along with the timestamp of the transaction, below the complete script:

/*Transactions Log Table Creation:

Created By: Mohammad R. Daoud

http://mohdaoud.blogspot.com

April 26, 2010

Make sure to have a complete backup on the databases you are running this script on, in addition, running those scripts is your own risk.

*/

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionsLog]') AND type in (N'U'))
DROP TABLE [dbo].[TransactionsLog]
GO

CREATE TABLE [dbo].[TransactionsLog](
    [TransactionID] [int] NOT NULL,
    [Type] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_Type]  DEFAULT (''),
    [TrxDateTime] [datetime] NOT NULL CONSTRAINT [DF_LogTable_TrxDateTime]  DEFAULT (''),
    [TrxDescription] [varchar](5000) NOT NULL CONSTRAINT [DF_LogTable_TrxDescription]  DEFAULT (''),
    [Notes] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_Notes]  DEFAULT (''),
    [TrxNumber] [int] NOT NULL,
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [RowUser] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_RowUser]  DEFAULT (suser_sname()),
    [RowDate] [datetime] NOT NULL CONSTRAINT [DF_LogTable_RowDate]  DEFAULT (getdate()),
CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED
(
    [TransactionID] ASC
))
GO

 

/* Starting Script */

DECLARE @Operation varchar(500)
DECLARE    @TableName varchar(128)
DECLARE @PrimaryKey varchar(500)

DECLARE TriggersCur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME = ‘GL00100’ OR TABLE_NAME = ‘GL00105’

 

/*Remove Above Filter To Monitor the complete Database, note that this will create triggers on all your database tables, please make sure to have a FULL DATABASE Backup before adding the triggers */


OPEN TriggersCur
FETCH NEXT FROM TriggersCur INTO @TableName

WHILE @@FETCH_Status = 0
BEGIN
SELECT    @PrimaryKey = c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE pk.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND c.TABLE_NAME = pk.TABLE_NAME AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
SET @Operation = 'INSERT Operation'
PRINT ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + 'INSERT]''))'
PRINT ' DROP TRIGGER [dbo].[' + @TableName + 'INSERT]'
PRINT ' GO '

PRINT ' CREATE TRIGGER ' + @TableName + 'INSERT ON ' + @TableName + ' FOR INSERT AS'
PRINT ' DECLARE @LOGID INT'
PRINT ' DECLARE @TransactionID INT'
PRINT ' SELECT @TransactionID = ' + @PrimaryKey + ' FROM INSERTED'
PRINT ' SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog'
PRINT ' INSERT INTO [TransactionsLog]'
PRINT '            ([TransactionID]'
PRINT '            ,[Type]'
PRINT '            ,[TrxDateTime]'
PRINT '            ,[TrxDescription]'
PRINT '            ,[Notes]'
PRINT '            ,[TrxNumber])'
PRINT '      VALUES'
PRINT '            (@LogID '
PRINT '            ,''' + @Operation + ''''
PRINT '            ,''' + CONVERT(VARCHAR(500), GETDATE()) + ''''
PRINT '            ,'''+ @TableName + ''''
PRINT '            ,'''+ ''''
PRINT '            , CONVERT(VARCHAR(500), @TransactionID) )'
PRINT ' GO '

SET @Operation = 'UPDATE Operation'

PRINT ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + 'UPDATE]''))'
PRINT ' DROP TRIGGER [dbo].[' + @TableName + 'UPDATE]'
PRINT ' GO '

PRINT ' CREATE TRIGGER ' + @TableName + 'UPDATE ON ' + @TableName + ' FOR UPDATE AS'
PRINT ' DECLARE @LOGID INT'
PRINT ' DECLARE @TransactionID INT'
PRINT ' SELECT @TransactionID = ' + @PrimaryKey + ' FROM INSERTED'
PRINT ' SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog'
PRINT ' INSERT INTO [TransactionsLog]'
PRINT '            ([TransactionID]'
PRINT '            ,[Type]'
PRINT '            ,[TrxDateTime]'
PRINT '            ,[TrxDescription]'
PRINT '            ,[Notes]'
PRINT '            ,[TrxNumber])'
PRINT '      VALUES'
PRINT '            (@LogID '
PRINT '            ,''' + @Operation + ''''
PRINT '            ,''' + CONVERT(VARCHAR(500), GETDATE()) + ''''
PRINT '            ,'''+ @TableName + ''''
PRINT '            ,'''+ ''''
PRINT '            , CONVERT(VARCHAR(500), @TransactionID) )'
PRINT ' GO '

SET @Operation = 'DELETE Operation'

PRINT ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + 'DELETE]''))'
PRINT ' DROP TRIGGER [dbo].[' + @TableName + 'DELETE]'
PRINT ' GO '

PRINT ' CREATE TRIGGER ' + @TableName + 'DELETE ON ' + @TableName + ' FOR DELETE AS'
PRINT ' DECLARE @LOGID INT'
PRINT ' DECLARE @TransactionID INT'
PRINT ' SELECT @TransactionID = ' + @PrimaryKey + ' FROM DELETED'
PRINT ' SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog'
PRINT ' INSERT INTO [TransactionsLog]'
PRINT '            ([TransactionID]'
PRINT '            ,[Type]'
PRINT '            ,[TrxDateTime]'
PRINT '            ,[TrxDescription]'
PRINT '            ,[Notes]'
PRINT '            ,[TrxNumber])'
PRINT '      VALUES'
PRINT '            (@LogID '
PRINT '            ,''' + @Operation + ''''
PRINT '            ,''' + CONVERT(VARCHAR(500), GETDATE()) + ''''
PRINT '            ,'''+ @TableName + ''''
PRINT '            ,'''+ ''''
PRINT '            , CONVERT(VARCHAR(500), @TransactionID) )'
PRINT ' GO '

FETCH NEXT FROM TriggersCur INTO @TableName
END
CLOSE TriggersCur
DEALLOCATE TriggersCur

Hope that this helps!

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Sunday, April 25, 2010

Dynamics GP Reporting Series: SOP Sales Invoice

Moving from the financial series into the Sales, sales invoice will need to be sent to the customer, where it has to be designed to represent company image, I have used design similar to the one come with GP, below the SQL command needed:

SQL Command:

SELECT    
dbo.SOP10100.SOPNUMBE, dbo.SOP10100.DOCDATE,
dbo.SOP10100.PYMTRMID, dbo.SOP10100.CUSTNMBR,
dbo.SOP10100.CUSTNAME, dbo.SOP10100.CSTPONBR,
dbo.SOP10100.SHIPMTHD, dbo.SOP10100.SLPRSNID,
dbo.SOP10200.ITEMNMBR, dbo.SOP10200.ITEMDESC,
dbo.SOP10200.QTYORDER, dbo.SOP10200.QTYTBAOR,
dbo.SOP10200.QTYTOINV, dbo.SOP10100.CURNCYID,
dbo.SOP10100.CURRNIDX, dbo.SOP10200.UOFM,
dbo.SOP10100.MSTRNUMB, dbo.SOP10106.USRTAB01,
dbo.SOP10106.USERDEF2, dbo.SOP10106.USRDEF03,
RM00102_1.ADDRESS1, RM00102_1.ADDRESS2,
RM00102_1.ADDRESS3, dbo.SOP10100.ORTDISAM,
dbo.SOP10100.ORSUBTOT, dbo.SOP10100.ORFRTAMT,
dbo.SOP10100.ORMISCAMT, dbo.SOP10100.ORTAXAMT,
dbo.SOP10100.ORDOCAMT, dbo.SOP10200.ORUNTPRC,
dbo.SOP10200.OXTNDPRC, dbo.SOP10200.ORMRKDAM,
dbo.SOP10200.CMPNTSEQ
FROM         dbo.SOP10100
INNER JOIN dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE
AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE
AND dbo.SOP10100.TRXSORCE = dbo.SOP10200.TRXSORCE
LEFT OUTER JOIN dbo.SOP10106 ON dbo.SOP10100.SOPTYPE = dbo.SOP10106.SOPTYPE
AND dbo.SOP10100.SOPNUMBE = dbo.SOP10106.SOPNUMBE
LEFT OUTER JOIN dbo.RM00102 RM00102_1 ON dbo.SOP10100.PRSTADCD = RM00102_1.ADRSCODE
AND dbo.SOP10100.CUSTNMBR = RM00102_1.CUSTNMBR
WHERE dbo.SOP10100.SOPNUMBE = {?SOPNumber}

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original SOP Sales Invoice:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: Bank Transfers

As well as the bank transactions, bank transfers does not have “Save” operation as well, it directly posts the transaction to your checkbook, where we’ll need to get our report printed after the post operation, below the SQL command needed:

SQL Command:

SELECT    
dbo.CM20600.CMXFRNUM AS CMTrxNum, dbo.CM20600.CMCHKBKID,
dbo.CM20600.CMFRMCHKBKID, dbo.CM20600.CMXFTDATE,
dbo.CM20100.AUDITTRAIL, dbo.CM20200.DSCRIPTN,
dbo.CM20200.POSTEDDT, dbo.CM20200.Xfr_Record_Number,
dbo.CM20400.DistRef, dbo.GL00105.ACTNUMST,
dbo.GL00100.ACTDESCR, dbo.CM20200.ORIGAMT,
dbo.CM20400.ORCRDAMT, dbo.CM20400.ORDBTAMT,
dbo.CM20200.CURNCYID
FROM         dbo.CM20400
INNER JOIN dbo.CM20200 ON dbo.CM20400.CMDNUMWK = dbo.CM20200.CMRECNUM
INNER JOIN dbo.GL00100 ON dbo.CM20400.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
INNER JOIN dbo.CM20600
INNER JOIN dbo.CM20100 ON dbo.CM20600.CMXFRNUM = dbo.CM20100.CMTrxNum
ON dbo.CM20200.CMRECNUM = dbo.CM20100.CMDNUMWK
WHERE     (dbo.CM20200.CMTrxType = 7)
AND dbo.CM20100.CMTrxNum = {?CMTRXNUM}

To avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Bank Transfer Posting Journal:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Tuesday, April 20, 2010

Monday, April 19, 2010

Dynamics GP Reporting Series: Bank Transactions

As you know, bank transactions does not have “Save” operation, it directly posts the transaction to your checkbook, where we’ll need to get our report printed after the post operation, below the SQL command needed:

SQL Command: 

SELECT

dbo.CM20200.TRXDATE, dbo.CM20200.GLPOSTDT,

dbo.CM20200.paidtorcvdfrom, dbo.CM20200.DSCRIPTN,

dbo.CM20100.AUDITTRAIL, dbo.CM20400.ACTINDX,

dbo.GL00100.ACTDESCR, dbo.GL00105.ACTNUMST,

dbo.CM40101.DOCTYNAM, dbo.CM20100.CMTrxNum,

dbo.CM20100.CMTrxType, dbo.CM20100.CHEKBKID,

dbo.CM20200.CURNCYID, dbo.CM20400.ORCRDAMT,

dbo.CM20400.ORDBTAMT, dbo.CM20200.ORIGAMT

FROM

dbo.CM20100 INNER JOIN dbo.CM20400

ON dbo.CM20100.CMDNUMWK = dbo.CM20400.CMDNUMWK

INNER JOIN dbo.GL00100 ON dbo.CM20400.ACTINDX = dbo.GL00100.ACTINDX

INNER JOIN dbo.GL00105 ON dbo.CM20400.ACTINDX = dbo.GL00105.ACTINDX

INNER JOIN dbo.CM20200 ON dbo.CM20100.CMTrxNum = dbo.CM20200.CMTrxNum

AND dbo.CM20100.CHEKBKID = dbo.CM20200.CHEKBKID

AND dbo.CM20100.CMTrxType = dbo.CM20200.CMTrxType

INNER JOIN dbo.CM40101 ON dbo.CM20100.CMTrxType = dbo.CM40101.CMTrxType

Where (dbo.CM20100.VOIDED <> 1) AND (dbo.CM20400.VOIDED <> 1) AND dbo.CM20100.CMTrxNum = {?CMTRXNUM}

To avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Bank Transaction Posting Journal:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Friday, April 16, 2010

Integration Manager Scripts without hard coding the Database Name

Good method by Touchstone blog, to write SQL Scripts within your Microsoft Dynamics GP Integration and perform some database operations without having to hard code the database name.

Checkout the article here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

GL Account and its Currencies

I found a question in MicrosoftDynamicsForums, the user was searching the GL tables for account currencies, while the account currencies does not sorted under the GL series, instead it stored in MC0020, below view to return the needed result:

SELECT     dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR, dbo.MC00200.CURNCYID
FROM         dbo.GL00100 INNER JOIN
                      dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.MC00200 ON dbo.GL00100.ACTINDX = dbo.MC00200.ACTINDX
ORDER BY dbo.GL00105.ACTNUMST

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Calling Dexterity Report Using VBA

David in Developing for Dynamics GP posted an article about how to run a Dexterity report from VBA, the code is very much useful if you are looking to create custom reports that need to be opened within your form, checkout David’s post here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Thursday, April 15, 2010

Dynamics GP Reporting Series: GL Edit List

As we will need to print a report that displays all open GL Transactions and filter the result on a specific batch, we’ll need to start be getting the needed tables and create our view:

SQL Command:

SELECT     DISTINCT
dbo.GL10000.BACHNUMB,    dbo.SY00500.BCHTOTAL,
dbo.SY00500.SERIES,        dbo.SY00500.BCHCOMNT,
dbo.SY00500.NUMOFTRX,    dbo.SY00500.BACHDATE,
dbo.SY00500.CNTRLTRX,    dbo.SY00500.CNTRLTOT,
dbo.SY00500.APPROVL,    dbo.SY00500.APPRVLDT,
dbo.SY00500.APRVLUSERID, dbo.SY00500.BACHFREQ,
dbo.GL10000.JRNENTRY,    dbo.GL10000.TRXDATE,
dbo.GL10000.RVRSNGDT,    dbo.GL10000.SOURCDOC,
dbo.GL10000.TRXTYPE,    dbo.GL10000.REFRENCE,
dbo.GL00100.ACTDESCR,    dbo.GL10001.DSCRIPTN,
dbo.GL10001.DEBITAMT,    dbo.GL10001.CRDTAMNT,
dbo.GL00105.ACTNUMST,    dbo.GL10001.SQNCLINE
FROM        
dbo.SY00500 INNER JOIN
dbo.GL10000 ON dbo.SY00500.BACHNUMB = dbo.GL10000.BACHNUMB
LEFT OUTER JOIN    dbo.GL00100
INNER JOIN dbo.GL10001 ON dbo.GL00100.ACTINDX = dbo.GL10001.ACTINDX
INNER JOIN dbo.GL00105 ON dbo.GL10001.ACTINDX = dbo.GL00105.ACTINDX
ON dbo.GL10000.DTAControlNum = dbo.GL10001.ORCTRNUM
AND dbo.GL10000.BACHNUMB = dbo.GL10001.BACHNUMB
AND dbo.GL10000.JRNENTRY = dbo.GL10001.JRNENTRY

To avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original GL edit list:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Tuesday, April 13, 2010

Dynamics GP Reporting Series: Introduction

I been in a situation where the client wanted to get his company logo loaded in his reports whenever he adds new companies and without calling the IT guys to modify the reports.

Due to Dexterity limitations, logo cannot be loaded from the database into the report writer, instead we were getting around multi-companies reports by adding a section in report writer for each logo and managing the appearance of each section using VBA.

My client has allot of companies and those companies are increased day after day! Where he asked to have the company logo as a setup and loaded to his reports dynamically, I had to customize this through the VS Toolkit, I added a new form linked to the company setup form:

image 

image

Where you can setup the logo for each company, the logo will be saved directly to the database, and then I developed 15 Crystal Reports linked to the forms to display the data along with predefined logo’s instead of the original GP reports:

image

Each report of our reports will have two main commands, first one will hold the SQL Statement and the other one will hold the company related information, we’ll be discussing each command in its related report, but the company command will be one for all reports, to avoid any issues when creating our crystal report, we’ll need to add the statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original GL edit list:

SELECT * FROM Dynamics.dbo.SY01500
INNER JOIN Dynamics.dbo.CompanySetup ON Dynamics.dbo.SY01500.InterID = Dynamics.dbo.CompanySetup.IntercompanyID

I am willing to share the complete steps as a series of posts based on the below schedule (Links will be active on the specified dates):

Introduction Tuesday April 13, 2010
GL Edit List Friday April 16, 2010
Bank Transactions Tuesday April 20, 2010
Bank Transfers Friday April 23, 2010
SOP Sales Invoice Tuesday April 27, 2010
RM Sales Invoice Friday April 30, 2010
RM Credit Memo Tuesday May 04, 2010
RM Debit Memo Friday May 07, 2010
RM Cash Receipt Tuesday May 11, 2010
POP Purchase Order Friday May 14, 2010
Receiving Transaction Entry Tuesday May 18, 2010
PM Transaction Entry Friday May 21, 2010
PM Manual Payment Tuesday May 25, 2010
Customer Statement Friday May 28, 2010
Vendor Statement Tuesday June 01, 2010

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Sunday, April 11, 2010

2010 Nominations

As written in the pool votes:

”Its nearly time for the 2010 list for the Microsoft Dynamics Top 100 Most Influential People!”

Vote for me guys! Click on this link and keep refreshing until finding my name and vote!

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Thursday, April 8, 2010

Dynamics GP Fixed Assets With No Depreciation

Frank in his daily tip posted an article on how to add a fixed asset without depreciation, check his article here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Wednesday, April 7, 2010

Management Reporter Main Features

Great article by Dynamics Corporate Performance Management blog on the main features added to Management Reporter, worth checking their article, below some of the interesting features:

64-bit support

• Many companies are upgrading their systems to 64-bit. Management Reporter was built using in 64-bit environments. The IT department won’t need to maintain a separate machine just for Management Reporter as they may need to do for FRx.

Improved stability

• The FRx code base was close to 20 years old and the design experience was closely tied to report generation. Management Reporter is more stable. Each area of the application (designing, processing, and viewing) is isolated, making it easier to identify exactly where issues are.

Improved performance

• Processing takes place on the server, not the client. This frees up the client to continue designing reports instead of locking it up until the report has finished generating.

SQL Server support

• No more Microsoft Access databases! This means no more corrupted spec files, no need to rebuild indexes to ensure new accounts are picked up. This results in fewer calls to IT or your partner to “fix” the issue. As a result, end users are much more productive.

Improved multi-user environment

• With the switch to IIS and SQL Server, there is no need to create shares for the sysdata and io_data. All of the building block, company information and generated reports are in a SQL database. Administrators can see all reports that are being generated at any given time since all processing is done on the server.

Active directory support

• Once users are added to Management Reporter, any changes made to passwords, etc. by users are automatically rolled forward. There is no need to “manage” users in Management Reporter unless a user needs to be disabled or given a new security role. This also means fewer login screens for the user.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Tuesday, April 6, 2010

Use Dynamics GP Lookups Using VS Toolkit

While searching for performing such operation, I have created a small sample that worked perfectly!!

This method uses Form.Open method to load lookup form and AddHandlers to handle the selection events, below steps I used to fulfill this request, in the example below, I am developing a customer and vendor statements for one of my clients:

1. Create the form that will load the lookup, the form will look like the below:

image

2. Add the needed form to the Customer Maintenance form as a menu:

image

This could be done by adding the following lines of code in the AddIns file under “Sub Initialize()”:

'Customer Statement
Dim CustomerStatementMenu As New System.EventHandler(AddressOf CustomerStatement_Menu)
Dynamics.Forms.RmCustomerMaintenance.AddMenuHandler(CustomerStatementMenu, "Customer Statement")

3. We need add handler to the “Select Button” on the customer lookup form just below the above lines:

'Customer Lookup
AddHandler Dynamics.Forms.CustomerLookup.CustomerLookup.SelectButton.ClickBeforeOriginal, AddressOf CustomerLookup_Handler

4. Handling the menu item for customer will call Customer Statement Menu sub as the below, make sure to define the customer statement form public to get the access later on:

Dim CustomerStatementForm As New CustomerStatement

Private Sub CustomerStatement_Menu(ByVal sender As Object, ByVal e As System.EventArgs)
    CustomerStatementForm.Show()
End Sub

5. Now we will need to handle the Select button event to get the selected customer, we’ll need to add two global variables, one as a Boolean to make sure that the lookup called by our application called “CalledByApplication” and the other to specify from where the lookup called and where to return the result –From or To-:

Private Sub CustomerLookup_Handler(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
     If CalledByApplication Then
         If CalledBy = "From" Then
             CustomerStatementForm.CustomerFrom.Text = Dynamics.Forms.CustomerLookup.CustomerLookup.CustomerNumber.Value
         ElseIf CalledBy = "To" Then
             CustomerStatementForm.CustomerTo.Text = Dynamics.Forms.CustomerLookup.CustomerLookup.CustomerNumber.Value
         End If

         CalledByApplication = False
         CalledBy = ""
     End If
End Sub

6. Now in our form at the lookup button event, add the following lines of code:

CalledByApplication = True
CalledBy = "From"
Dynamics.Forms.CustomerLookup.CustomerLookup.Open()

Viola, your application will call the GP lookup! Hope that helps.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Sunday, April 4, 2010

Cashbook Management Module

For companies that has allot of checkbooks inside the company premises and would like to have additional features in Banks Management Module, they might need to take a look to the following features in Cash Book Management Module:

1. Checkbook Level Permissions:

clip_image002

2. Setup per checkbook:

clip_image004

All posted payments and deposits could be posted automatically from “AP/AR/GL” or placed in batches for review.

3. Payment Form:

clip_image006

Note that you can pay for customers, vendors and miscellaneous (GL Direct), in addition, you could print checks directly from here.

4. Deposits Form:

clip_image008

You can also receive payments from Customers, vendors or miscellaneous, note that payments and deposits are integrated with the Analytical Accounting Module.

5. All Transactions are saved in the same batch:

clip_image010

6. Cash Receipts could be printed directly from the system:

clip_image012

7. All deposits could be posted once:

clip_image014

8. Automatic and manual reconciliation are available:

clip_image016

9. Reminders on Manual Payment and Cash Receipt:

clip_image018

Very nice module, worth testing if you are interested.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP 10.0 US Payroll

Last few days, I been working to activate the US payroll based on our local laws, I used to hear allot about the payroll module and how it couldn’t be used in Middle East due to US taxes and regulations, and specially after getting a major changes in the taxation roles in Jordan, the good thing is I been able to process salaries on the US payroll module by following steps below:

1. Disable all the US taxes where located.

2. Setup the “Basic Salary” as a pay code in “Microsoft Dynamics GP menu >> Tools >> Setup >> Payroll >> Pay Code”.

image

3. Link the pay code to the employee in “Cards >> Payroll >> Pay Code”:

image

4. Setup the Social Security and all other deductions as deductions in “Microsoft Dynamics GP menu >> Tools >> Setup >> Payroll >> Deduction” and then link it to the employee:

image 

5. Setup any Allowances needed in “Microsoft Dynamics GP menu >> Tools >> Setup >> Payroll >> Benefit” and link it with the employee:

image

6. Now go to “Transactions >> Payroll >> Transaction Entry” and select your employee to generate the payroll:

image

This was a simple workflow to cover the payroll process in the Middle East, hope that this helps.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Disable Cash Receipts Deposits

Have you ever wanted you cash receipts deposits to be posted automatically? If yes, then you might need to install and activate Analytical Accounting module (Not sure why the option is there!?) and go to “Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> Analytical Accounting >> Options” and check “Post Cash Receipt deposits automatically in BR” where cash receipt deposits will be posted directly in Bank Reconciliation.

image

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

MVP Certificate

While preparing my luggage for a personal trip to Lebanon, I been checking my mails just before leaving where I shocked receiving a mail from MVP Support informing me that I got awarded the MVP certificate for Dynamics GP 2010.

Holding such certificate is a pleasure and a great motive to keep up with the product technologies and community activities, I would like to take this opportunity to express my keen thanks to Microsoft, my MVP lead Baransel Dogan and all my blog readers who where the reason behind getting such recognition.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Related Posts:

Related Posts with Thumbnails