October 14, 2010 
The view below will return all Checkbook transactions for Dynamics GP. It does not show details for the deposits because I wanted to keep this to one line per transaction, as it appears on the Checkbook Register in GP. To add deposit detail to you report, you can join table CM20300 on CM20200.CMTrxNum = CM20300.depositnumber.
You can find other Dynamics GP views on my GP Reports page.
~~~~~
CREATE VIEW view_Checkbook_Register as /******************************************************************* view_Checkbook_Register Created on Oct 14, 2010 by Victoria Yudin For updates see https://victoriayudin.com/gp-reports/ All bank rec transactions - one line per transaction Includes voided transactions Tables: CM20200 - bank transactions CM20600 - bank transfers CM40101 - transaction type setup Updated Nov 10, 2010 to link to CM40101 table instead of hard coding transaction types (thanks to Siva Venkataraman for this suggestion) Updated Feb 18, 2011 to include code for bank transfers from and to the same checkbook (thanks to Max Toledo for this suggestion) *******************************************************************/ SELECT T.CHEKBKID Checkbook_ID, T.CMTrxNum Trx_Number, D.DOCABREV Trx_Type, T.TRXDATE Trx_Date, T.GLPOSTDT GL_Posting_Date, T.paidtorcvdfrom Paid_To_Received_From, CASE WHEN T.CMTrxType in (1,2,5,101,102) THEN T.Checkbook_Amount WHEN T.CMTrxType = 7 AND X.CMCHKBKID = X.CMFRMCHKBKID THEN T.Checkbook_Amount*0 WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMCHKBKID THEN T.Checkbook_Amount WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMFRMCHKBKID THEN T.Checkbook_Amount*-1 ELSE T.Checkbook_Amount*-1 END Checkbook_Amount, T.CURNCYID Currency_ID, T.DSCRIPTN 'Description', T.ClrdAmt Cleared_Amount, CASE T.Recond WHEN 1 THEN 'Yes' ELSE 'No' END Reconciled, T.AUDITTRAIL Audit_Trail, CASE T.VOIDED WHEN 1 THEN 'Yes' ELSE 'No' END Voided FROM CM20200 T LEFT OUTER JOIN CM20600 X ON T.Xfr_Record_Number = X.Xfr_Record_Number INNER JOIN CM40101 D ON D.CMTrxType = T.CMTrxType /** the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions **/ GO GRANT SELECT ON view_Checkbook_Register TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Hello this is i hope a simple question, our company has some checks that are not reconciled in gp and the accounting staff need to get the vendor info added to the check book register report, or a report like it.
i have the sql that is used by the default report abnd have been looking at the great stuff here but i am new with gp tables and how to look for the data…
help !
i know sql, is know ssrs but i am new with gp and how it does things.
LikeLike
Figuerres,
What tables is your report already using? It would be easier to start with that.
-Victoria
LikeLike
Hi Victoria, I am trying to get the “Check/card number” that is in the Cash Receipts Entry window. The reason I want this is to prepare a statement so if any invoices are paid with checks then the check number can be displayed beside the document number. i am getting the data for the Document number and the received amount from the RM20201 table. but I do not see any details of the check number.
LikeLike
Hi Sandeep,
The check number will be in the RM20101 (or RM30101) table – it’s in the CHEKNMBR column.
-Victoria
LikeLike
Ma’am,
This view is great TYVM.
I also have a question: If my AP got their checks out of sequence (they know to fix with next check num) and the physical check is cashed and done and GP knows as different number. Can I simply update the CM20200 CMTrxNum or are there other tables that check number is hitting? I would also VOID out the paper check for the one that GP thinks it is as I update it in SQL to the correct one matching the physical paper check.
Thanks in advance,
jason
LikeLike
Hi Jason,
The check numbers are in A LOT of tables because the check number becomes the Document Number (DOCNUMBR) in Payables. So they will be in numerous Payables tables (transaction tables, apply tables, distribution tables, keys table) and also in GL tables and Bank Rec tables.
If at all possible, I would recommend making a list of the ‘mapping’ – real check numbers to the GP check numbers and keeping it with the accounting records, to be used for auditing/reconciling, and then leaving the data alone. The reason is that if you don’t do this exactly right you are going to break a lot of the drillback and linking in GP and could cause data issues afterwards for aging reports, check links and reconcile functions, and who knows what else.
If this is not possible to leave the data alone and it absolutely has to be fixed, I would recommend working with either your GP Partner or Microsoft Dynamics GP support to come up with the appropriate scripts to fix all the tables needed. And make lots and lots of backups!
-Victoria
LikeLike
Good morning – I used this view to create a smartlist with Smartlist Builder. The Checkbook Amount displays as a negative value in Smartlist. I tried to use the feature of Builder to “Display as negative based on field” for Checkbook Amount but it does not change the value of that field. Is there a way to modify the above code to have Checkbook Amont display as an absolute value?
LikeLike
We only use this report currently for Checks issued – CMTtxType = 3
LikeLike
Hi Jeff,
There are two lines in the code, about halfway down, that end in the following:
If you delete the *-1 from the end of both, you should be good to go, I think.
-Victoria
LikeLike
Hi Victoria,
My company is still behind the times due to budgetary constraints and am using GPS Version 8.0.
I am looking to automate, as best as possible, a daily cash projection spreadsheet in Excel by creating an SQL view in Crystal Reports. My first instinct is to have this View/Report pull data from the Checkbook and Payables modules. We don’t use the Receivables module. It would have a column for each day of the month (I would set it up as a cross tab report) and for future dates it would show what we project to have deposited and what payables are due and for the past dates, what was actually deposited and paid.
Am I on the right track here or I am so far off that I should have my head examined? If I am on the right track, which tables/fields would I use to link the Checkbook and Payables modules?
THanks in advance.
Gordon
LikeLike
Hi Gordon,
I am not sure this would work because there is nowhere in GP for you to store your projections for future deposits. And if you create one, then someone has to keep populating it and updating it. The best you could do from GP tables is get historical data for deposits and only what is currently entered in payables. Another issue that may come up is what data is actually in payables. Many companies do not enter all their payables in a timely manner and/or do not use due dates properly in GP, so it may mean a big change to the payables procedures for the accounting staff to allow this type of reporting to work. Even for the data already in GP, this report would be specific to how (and where) your company enters transactions so I am not sure I could give you a generic answer. This sounds like a report you really need to plan out well first, to understand what kind of data it needs to show and where your company is entering/storing this data. Perhaps there is a resource at your GP Partner company that can help you with this?
-Victoria
LikeLike
Hi Victoria,
Thanks for your input and yes, you are correct, there is no place to store that data for future deposits in GP. I was thinking that it would have to be done in the report itself as a formula of Given data.
My thought was to have “given” information in the report which would be our future projected deposits. I would then use IF THEN ELSE statements to the effect of “If TRXDATE is less than current date, then use actual amounts and if TRXDATE is greater than current date, then use the projected amounts. Or something like that that. I have to play around with it and see if that will get me on my way. Could you suggest which Tables/Fields I could use to link the Checkbook to the Payables?
LikeLike
Gordon,
Why do you need to link the Checkbook to Payables? Anything already paid will be in both, but you can use either. Since you’re looking for both paid and unpaid payables, I would probably just use PM30200 for paid and PM20000 for unpaid. And only use the Checkbook tables for the deposits.
-Victoria
LikeLike
I think I need to link both because of future payables that I would need to include in a projection of cash flow.
Thanks again for your help. I will let you know how I make out.
LikeLike
I think I am having an issue with the word “link”. There is really no link, you just need to add 2 different sets of data, right?
-Victoria
LikeLike
Hi Victoria
A client, by mistake, when making bank transfers, select the same checkbook (CMFRMCHKBKID = CMCHKBKID). Your query does not support it, and doubles Checkbook_Amount when it make zero effect. I suggest to change your CASE function:
CASE
WHEN T.CMTrxType in (1,2,5,101,102)
THEN T.Checkbook_Amount
WHEN T.CMTrxType = 7 AND X.CMCHKBKID = X.CMFRMCHKBKID
THEN T.Checkbook_Amount*0 — zero effect
WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMCHKBKID
THEN T.Checkbook_Amount
WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMFRMCHKBKID
THEN T.Checkbook_Amount*-1
ELSE T.Checkbook_Amount*-1
END Checkbook_Amount,
I know that is an error, but I think you good include on your query.
Sincerely,
Max
LikeLike
Thanks Max!
I have updated the code to include your suggestion. I agree that even though it’s a mistake, it could easily happen, so it is better to allow for it in the code.
-Victoria
LikeLike
How can I add
CM20300 on CM20200.CMTrxNum = CM20300.depositnumber ?
Thanks
LikeLike
Ilya, you could add the following code at the end of my code (before the GO):
LEFT OUTER JOIN CM20300 DEP
ON T.CMTrxNum = DEP.depositnumber
And add the fields from CM20300 that you need to the SELECT list.
Or you could write separate view for the deposit details and link the two views in your report, depending on how/where you are writing your report.
-Victoria
LikeLike
Hi Victoria
Instead of hardcoding the abbreviations for the transaction type, you can have it pulled from the setup. Like the one shown below. 🙂
CREATE VIEW view_Checkbook_Register
AS
/*******************************************************************
view_Checkbook_Register
Created on Oct 14, 2010 by Victoria Yudin
For updates see https://victoriayudin.com/gp-reports/
All bank rec transactions - one line per transaction
Includes voided transactions
Tables:
CM20200 - bank transactions
CM20600 - bank transfers
*******************************************************************/
SELECT T.CHEKBKID Checkbook_ID,
T.CMTrxNum Trx_Number,
S.DOCABREV Trx_Type,
T.TRXDATE Trx_Date,
T.GLPOSTDT GL_Posting_Date,
T.paidtorcvdfrom Paid_To_Received_From,
CASE WHEN T.CMTrxType IN ( 1, 2, 5, 101, 102 ) THEN T.Checkbook_Amount
WHEN T.CMTrxType = 7
AND T.CHEKBKID = X.CMCHKBKID THEN T.Checkbook_Amount
WHEN T.CMTrxType = 7
AND T.CHEKBKID = X.CMFRMCHKBKID THEN T.Checkbook_Amount * -1
ELSE T.Checkbook_Amount * -1
END Checkbook_Amount,
T.CURNCYID Currency_ID,
T.DSCRIPTN 'Description',
T.ClrdAmt Cleared_Amount,
CASE T.Recond
WHEN 1 THEN 'Yes'
ELSE 'No'
END Reconciled,
T.AUDITTRAIL Audit_Trail,
CASE T.VOIDED
WHEN 1 THEN 'Yes'
ELSE 'No'
END Voided
FROM CM20200 T
LEFT OUTER JOIN CM20600 X ON T.Xfr_Record_Number = X.Xfr_Record_Number
INNER JOIN CM40101 S ON S.CMTrxType = T.CMTrxType
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Checkbook_Register TO DYNGRP
Hopefully this helps… 🙂
Cheers
Siva
LikeLike
Thanks Siva!
-Victoria
LikeLike
No problem Victoria. 🙂
LikeLike