Dynamics GP Reporting Series: PM Manual Payment

Finally we’ll need to print the vendor payment, we’ll use the Manual Payment form to enter the transaction and print out the payment using the report, below is the needed command:

SQL Command:

SELECT    
dbo.PM10400.PMNTNMBR, dbo.PM10400.DOCNUMBR,
dbo.PM10400.DOCDATE, dbo.PM10400.VENDORID,
dbo.PM00200.VENDNAME, dbo.PM10400.PYENTTYP,
dbo.PM10400.CARDNAME, dbo.PM10400.CHEKBKID,
dbo.PM10100.CRDTAMNT, dbo.PM10100.DEBITAMT,
dbo.PM10100.DSTINDX, dbo.PM10100.ORCRDAMT,
dbo.PM10100.ORDBTAMT, dbo.PM10400.DOCAMNT,
dbo.CM00100.DSCRIPTN, dbo.PM10400.CURNCYID,
dbo.PM10100.DISTTYPE, dbo.GL00100.ACTDESCR,
dbo.GL00105.ACTNUMST, dbo.PM00200.VNDCHKNM,
dbo.PM10400.TRXDSCRN
FROM         dbo.PM10400
INNER JOIN dbo.PM10100 ON dbo.PM10400.VCHRNMBR = dbo.PM10100.VCHRNMBR
INNER JOIN dbo.PM00200 ON dbo.PM10400.VENDORID = dbo.PM00200.VENDORID
INNER JOIN dbo.GL00100
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
ON dbo.PM10100.DSTINDX = dbo.GL00100.ACTINDX
LEFT OUTER JOIN dbo.CM00100 ON dbo.PM10400.CHEKBKID = dbo.CM00100.CHEKBKID
WHERE     (dbo.PM10400.CNTRLTYP = 1) and  (dbo.PM10100.CNTRLTYP = 1)

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 below:

Crystal Report Design:

 

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: PM Transaction Entry

When we need to perform any adjustment transactions on our vendors, we’ll need to print a document to be attached to the original document sent by the vendor, below is the needed command:

SQL Command:

SELECT    
dbo.PM10000.DOCNUMBR, dbo.PM10000.DOCTYPE,
dbo.PM10000.DOCAMNT, dbo.PM10000.DOCDATE,
dbo.PM10000.PYMTRMID, dbo.PM10000.SHIPMTHD,
dbo.PM00300.ADDRESS1, dbo.PM00300.ADDRESS2,
dbo.PM00300.ADDRESS3, dbo.PM00300.CITY,
dbo.PM10000.PORDNMBR, dbo.PM10000.VCHRNMBR,
dbo.PM10000.TRXDSCRN, dbo.PM10000.PRCHAMNT,
dbo.PM10000.TRDISAMT, dbo.PM10000.TAXAMNT,
dbo.PM10000.FRTAMNT, dbo.PM10000.MSCCHAMT,
dbo.PM10000.CASHAMNT, dbo.PM10000.CHEKAMNT,
dbo.PM10000.CRCRDAMT, dbo.PM00200.VENDNAME,
dbo.PM10000.DISTKNAM, dbo.PM10000.CURTRXAM,
dbo.PM10000.CURNCYID, dbo.PM10000.VCHNUMWK,
dbo.PM10000.VENDORID, dbo.MC020103.ORCTRXAM,
dbo.MC020103.ORFRTAMT, dbo.MC020103.ORTAXAMT,
dbo.MC020103.ORCASAMT, dbo.MC020103.ORCHKAMT,
dbo.MC020103.ORCCDAMT, dbo.MC020103.ORDISTKN,
dbo.MC020103.ORWROFAM, dbo.MC020103.OMISCAMT,
dbo.MC020103.OPURAMT, dbo.MC020103.ORTDISAM
FROM         dbo.PM00200
INNER JOIN dbo.PM10000 ON dbo.PM00200.VENDORID = dbo.PM10000.VENDORID
LEFT OUTER JOIN dbo.MC020103 ON dbo.PM10000.DOCTYPE = dbo.MC020103.DOCTYPE
AND dbo.PM10000.VCHNUMWK = dbo.MC020103.VCHRNMBR
LEFT OUTER JOIN dbo.PM00300 ON dbo.PM10000.VENDORID = dbo.PM00300.VENDORID
AND dbo.PM10000.VADDCDPR = dbo.PM00300.ADRSCODE
WHERE dbo.PM10000.VCHNUMWK = {?VCHNUMWK}

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 below:

Crystal Report Design:

 

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: POP Receiving Transaction Entry

After creating the purchase order, we’ll need to receive the goods to the inventory, we’ll generate the receiving document along with its journal, the receiving view below displays only the posted receiving transactions, below is the needed command:

SQL Command:

SELECT   
dbo.POP30300.POPRCTNM, dbo.POP30300.POPTYPE,
dbo.POP30300.VNDDOCNM, dbo.POP30300.receiptdate,
dbo.POP30300.VENDNAME, dbo.POP30300.VENDORID,
dbo.POP30300.VOIDSTTS, dbo.POP30300.CURNCYID,
dbo.POP30300.ORSUBTOT, dbo.POP30300.ORTDISAM,
dbo.POP30300.ORFRTAMT, dbo.POP30300.ORMISCAMT,
dbo.POP30300.ORTAXAMT, dbo.POP30310.ITEMNMBR,
dbo.POP30310.ITEMDESC, dbo.POP30310.UOFM,
dbo.POP30310.ORUNTCST, dbo.POP30310.OREXTCST,
dbo.POP30310.CURRNIDX, dbo.PM00200.ADDRESS1,
dbo.PM00200.CITY, dbo.PM00200.STATE,
dbo.PM00200.COUNTRY, dbo.PM00200.ZIPCODE,
dbo.PM00200.ADDRESS2, dbo.POP30310.PONUMBER,
dbo.POP10500.QTYINVCD, dbo.POP30300.VCHRNMBR,
(SELECT     Top 1 reqdate
FROM         POP10110
WHERE     ponumber = dbo.POP30310.ponumber) AS ReqDate
FROM         dbo.POP30300
INNER JOIN dbo.POP30310 ON dbo.POP30300.POPRCTNM = dbo.POP30310.POPRCTNM
INNER JOIN dbo.PM00200 ON dbo.POP30300.VENDORID = dbo.PM00200.VENDORID
INNER JOIN dbo.POP10500 ON dbo.POP30310.PONUMBER = dbo.POP10500.PONUMBER
AND dbo.POP30310.RCPTLNNM = dbo.POP10500.RCPTLNNM
AND dbo.POP30310.POPRCTNM = dbo.POP10500.POPRCTNM
AND dbo.POP30310.ITEMNMBR = dbo.POP10500.ITEMNMBR
WHERE     dbo.POP30300.POPRCTNM ={?POPRCTNM}

Command for the Receiving Journal:

SELECT    
dbo.POP30390.POPRCTNM, dbo.GL00105.ACTNUMST,
dbo.POP30390.CRDTAMNT, dbo.POP30390.ORCRDAMT,
dbo.POP30390.DEBITAMT, dbo.POP30390.ORDBTAMT,
dbo.POP30390.CURNCYID, dbo.GL00100.ACTDESCR
FROM         dbo.POP30390
INNER JOIN dbo.GL00105 ON dbo.POP30390.ACTINDX = dbo.GL00105.ACTINDX
INNER JOIN dbo.GL00100 ON dbo.POP30390.ACTINDX = dbo.GL00100.ACTINDX

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 below:

Crystal Report Design:

 

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: POP Purchase Order

Moving to the Purchasing module, our first start will be by printing the Purchase Order and sending it to the vendors, below is the command needed:

SQL Command:

SELECT    
dbo.POP10100.PONUMBER, dbo.POP10100.VENDNAME,
dbo.POP10100.PYMTRMID, dbo.POP10100.DOCDATE,
dbo.POP10100.BUYERID, dbo.POP00101.DSCRIPTN,
dbo.POP10100.SHIPMTHD, dbo.POP10100.STATGRP,
dbo.POP10100.PURCHADDRESS1, dbo.POP10100.PURCHCITY,
dbo.POP10110.ITEMNMBR, dbo.POP10110.ITEMDESC,
dbo.POP10110.REQDATE, dbo.POP10110.UOFM,
dbo.POP10110.QTYORDER, dbo.POP10110.LineNumber,
dbo.POP10100.PRSTADCD, dbo.POP10100.PRMSHPDTE,
dbo.POP10110.CMPNYNAM, dbo.POP10110.ADDRESS1,
dbo.POP10110.CITY, dbo.POP10110.STATE,
dbo.POP10110.ZIPCODE, dbo.POP10100.PURCHSTATE,
dbo.POP10100.PURCHZIPCODE, dbo.POP10100.CURNCYID,
dbo.POP10100.CURRNIDX, dbo.POP10110.ORUNTCST,
dbo.POP10110.OREXTCST, dbo.POP10100.ORSUBTOT,
dbo.POP10100.ORTDISAM, dbo.POP10100.ORFRTAMT,
dbo.POP10100.OMISCAMT, dbo.POP10100.ORTAXAMT
FROM         dbo.POP10100
LEFT OUTER JOIN dbo.POP10110 ON dbo.POP10100.PONUMBER = dbo.POP10110.PONUMBER
LEFT OUTER JOIN dbo.POP00101 ON dbo.POP10100.BUYERID = dbo.POP00101.BUYERID
WHERE dbo.POP10110.PONUMBER = {?PONumber}
ORDER BY dbo.POP10110.LineNumber

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 below:

Crystal Report Design:

 

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: Sales Cash Receipt

As a part of the Receivables Management Module, we’ll need to print a cash receipt for the payments we receive from our customers, and due to many requests from the clients, I have included the distribution journal for the transaction, below the SQL command needed:

SQL Command:

SELECT    
dbo.SY00500.NUMOFTRX, dbo.SY00500.BCHCOMNT,
dbo.SY00500.BCHTOTAL, dbo.SY00500.CNTRLTOT,
dbo.SY00500.CNTRLTRX, dbo.SY00500.BACHFREQ,
dbo.SY00500.BACHDATE, dbo.SY00500.APRVLUSERID,
dbo.SY00500.APPRVLDT, dbo.SY00500.APPROVL,
dbo.SY00500.SERIES, dbo.RM10201.CUSTNMBR,
dbo.RM00101.CUSTNAME, dbo.RM10201.DOCNUMBR,
dbo.RM10201.DOCDATE, dbo.RM10201.TRXDSCRN,
dbo.RM10201.GLPOSTDT, dbo.RM10201.ORTRXAMT,
dbo.RM10201.WROFAMNT, dbo.RM10201.DISTKNAM,
dbo.RM10201.CURTRXAM, dbo.RM10201.CHEKNMBR,
dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR,
dbo.GL00100.ACTINDX, dbo.RM10101.CRDTAMNT,
dbo.RM10101.DEBITAMT, dbo.RM10101.ORDBTAMT,
dbo.RM10101.ORCRDAMT, dbo.RM10201.BACHNUMB,
dbo.RM10101.DISTTYPE, dbo.RM10201.CURNCYID
FROM        
dbo.GL00100
INNER JOIN dbo.RM10101 ON dbo.GL00100.ACTINDX = dbo.RM10101.DSTINDX
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
RIGHT OUTER JOIN dbo.RM10201
INNER JOIN dbo.SY00500 ON dbo.RM10201.BCHSOURC = dbo.SY00500.BCHSOURC
AND dbo.RM10201.BACHNUMB = dbo.SY00500.BACHNUMB
INNER JOIN dbo.RM00101 ON dbo.RM10201.CUSTNMBR = dbo.RM00101.CUSTNMBR
ON dbo.RM10101.DOCNUMBR = dbo.RM10201.DOCNUMBR

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 below:

Crystal Report Design:

 

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

How to Handle Jordanian “5% Vendors Service Tax” in Microsoft Dynamics GP??

I got many requests from my customers on how to automatically manage the newly added governmental 5% Vendors Service Tax on all service vendors payments, with few hints from friends and researches, I been able to handle this tax using the built in Withholding Tax, below are the steps required to setup the tax:

1. Go to “Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> Company” and click on “Options”:

Setup the tax committee as a vendor in your AP and fill it in the above form.

Go to “Cards >> Purchasing >> Vendor” and click on the added “Withholding” button, mark this vendor to be subject to the withholding deduction and define the deduction percent:

Now go to AP Transaction Entry “Transactions >> Purchasing >> Transaction Entry” and select your vendor, where a message will popup checking if this invoice will be subject to withholding:

So far, nothing will be affected financially but the document will be marked for deduction on the next payment.

To pay this vendor, go to “Transactions >> Purchasing >> Manual Payments” and select your vendor, then fill the amount you need to pay, and apply this payment the to invoice, where the system will automatically calculate the withholding amount as shown below:

Open the distribution and make sure that the Withholding Account “WH” was added to your invoice.

Post the payment and enjoy having the tax automatically calculated.

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

Smart List Excel Export

Great article by Patrick from MS Developer Support, he explained a hidden DEX.ini switch that reduce the time needed to export data from Smart List to Excel, by his words:

I pressed the Export to Excel button and Smartlist exported the 10,000 rows by 7 columns in 1 minute, 55 seconds (115 seconds).

Since this switch is read on the fly, I didn't have to restart Dynamics or even refresh the Smartlist.  I just pushed the Export to Excel button again.  How long was the export this time?  Would you believe 9 seconds?

This magical feature will be enhanced by just adding the following switch to your DEX.ini file:

SmartlistEnhancedExcelExport=TRUE

Take a look to the complete explanation about the switch in the Developing for Dynamics GP Blog.

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: Sales Credit Memo

As a part of the Receivables Management Module, we still have the Customer Credit Memo’s, 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.DOCTYPE = 6)
AND (dbo.ReceivablesTransactions.[Document Type] = 'Credit Memos ')
AND 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 Credit Memo:

Crystal Report Design:

 

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

How to Fix Most of FRx Errors??

Good article by Gloria Braunschweig at Computeration Dynamics blog, explaining a checklist to follow when getting FRx issue, check the post here.

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: Sales Credit Memo

As a part of the Receivables Management Module, we still have the Customer Credit Memo’s, 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.DOCTYPE = 6)
AND (dbo.ReceivablesTransactions.[Document Type] = 'Credit Memos ')
AND 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 Credit Memo:

Crystal Report Design:

 

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: Sales Debit Memo

As a part of the Receivables Management Module, we still have the Customer Debit Memo’s, 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.DOCTYPE = 2)
AND (dbo.ReceivablesTransactions.[Document Type] = 'Debit Memos ')
AND 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 Credit Memo:

Crystal Report Design:

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

GP 2010 is now on PartnerSource!

Here you go, download it from here, unfortunately customers registration keys still not updated.

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

Smart List Tree View Space

Patrick from MS Dynamics GP Developer Support at the Developing for Dynamics GP Blog posted a great article about smart list features in GP 10.0, one of his interesting points was and explanation on the smart list tree view control that displays the favorites and categories, the space allocated for the tree view is greater than needed comparing to the space allocated to your columns, he explained in details how to correct this using modifier and attached the package needed to get this customized, below an explanation with images:

Smart List before applying the customizations:

Smart List after applying the customizations:

Now you have a big space for your columns now! Checkout his post here or directly download the customization file here.

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

Microsoft Dynamics GP 2010 Cookbook

Mark Polino the great Microsoft Dynamics GP MVP has finally published his book about Microsoft Dynamics GP 2010 tips!

“This is a tips style book organized into recipes. Each recipe covers a specific topic step by step and provides more information on the topic  beyond just the how to. Most of the recipes then point to related topics in the book to deepen the user’s experience. Technically, the focus is on GP 2010 but so many things still in GP 2010 have been in GP for a long time. Most of the recipes are applicable to versions 8, 9 and 10 as well. Don’t worry there is plenty of GP 2010 specific stuff in here too.  MVP’s Frank Hamelly and Leslie Vail were contacted by the publisher to act as technical reviewers so you can be sure that everything right in the book was a result of their influence. Any corny comments that made past editing are all mine.”

Personally, I am very much excited to get my copy, and would recommend every one in GP field to get it, something like 50 Bucks is nothing comparing to the information value collected inside the book, for more information about the book and ordering information, please click here.

Well done Mark and wish you all the best.

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