Commonly Used Tables:
- CM00100 – Checkbook Master – setup information like GL account, next check and deposit numbers and checkbook balance
- CM10100 – Deposit Work – saved unposted deposit data
- CM20100 – Bank Rec Journal – contains a record for each posted transaction, allows for reprinting of some bank rec posting journals
- CM20200 – Transactions – all transaction data except receipts
- CM20201 – Transaction Totals – information for posted deposits
- CM20300 – Receipts – all receipts (deposited and undeposited)
- CM20400 – Distributions – GL distributions for all bank rec transactions
- CM20500 – Reconciliation Headers – header information entered during bank reconciliation
- CM20501 – Reconcile Adjustments – adjustments on current reconciliations
- CM20502 – History of Outstanding Transactions – added in GP 2013 R2
- CM20600 – Transfers
- CM30200 – Transaction History – added in GP 2016 R2
- CM30300 – Receipt History – added in GP 2016 R2
INACTIVE in CM00100:
0 – Active
1 – Inactive
CNTRLTYP (Control Type):
1 – Transaction
2 – Receipt
CMTrxType (Transaction Type) and abbreviation:
1 – DEP – Deposit
2 – RCT – Receipt [not used in CM20200]
3 – CHK – Check
4 – WDL – Withdrawal
5 – IAJ – Increase Adjustment
6 – DAJ – Decrease Adjustment
7 – XFR – Transfer
101 – INT – Interest Income
102 – OIN – Other Income
103 – OEX – Other Expense
104 – SVC – Service Charge
DEPTYPE (Deposit Type):
1 – Deposit with Receipts
2 – Deposit without Receipts
3 – Clearing Deposit
DEPOSITED:
0 – False
1 – True
RcpType (Receipt Type):
1 – Check
2 – Cash
3 – Credit Card
~~~~~
Thank you to Michael Lupro of BestTechsNW and Paul Maynell of Maynell Associates for their help with providing additional information on this page!
Great information on EFT tables here from Shipli Rao at Encore Business Solutions: EFT Tables Used in Dynamics GP
—
Last Updated 07.19.2023
Victoria Yudin 
Hi Victoria
Just setting a smartconnect for Bank Transactions Enter Receipts. All working well however I do need to run a task that I need to access the unposted data for the transaction before it is “posted”. Cannot for the life of me find a table that holds the unposted data for a Bank Transactions Enter Receipts
Thank-you very much if you can assist
LikeLike
Hi David,
The Bank Rec module is different from others in that it does not have a way to save transactions prior to posting, or a ‘batch’ process. Bank Transactions are entered one at a time and posted right away. When you import bank transactions they are also posted right away. So you’re not missing anything – there are no tables for unposted Bank Rec data.
-Victoria
LikeLike
Hi Victoria. Thank-you very much for the reply and confirmation
LikeLike
Victoria,
We are having issues with EFT batches getting stuck in the Generate EFT Files window. We are able to clear them out by running the following scripts:
delete from CM20202 where BACHNUMB = ‘BATCHNUMBER’
delete from CM20203 where BACHNUMB = ‘BATCHNUMBER’
Was wondering if you have any idea on why they get stuck here. It was happening periodically but now its happening to most of the batches.
LikeLike
Hi Tony,
Are you getting errors? Are the EFT files generating, but the batches are never coming off the list?
-Victoria
LikeLike
No errors, Yes you are correct, the files generate but the batches stay on the list.
LikeLike
Tony,
Next time you do this, can you try as ‘sa’ or a user that is a POWERUSER and see if there is any difference?
-Victoria
LikeLike
We can, I will say that not all batches get stuck, its random.
LikeLike
Yep, that makes me think it’s either user or permissions related in some way. Deleting data out of tables is never my first inclination, I prefer to find what is causing the issue. So if you can find a pattern, hopefully that will help you do that.
-Victoria
LikeLike
We made the user Poweruser and they are still failing.
LikeLike
Does it also fail for ‘sa’?
LikeLike
I see the check book in the CM00100 table, but when I go to CARDS/FINANCIAL/CHECKBOOK and select Checkbook ID is does not show up in the lookup window. Are there permissions hiding the checkbook, I am logged into GP 2016 as the sa.
LikeLike
Sam,
What happens if you type in the Checkbook ID on the Checkbook Maintenance window instead of trying to look it up?
-Victoria
LikeLike
I get a popup window ‘This Checkbook is not accessible’
LikeLike
Victoria, A lookup on that error led me to a link concerning MEM (Binary Stream) – issue resolved by adding Checkbook to entity in MEM setup – Thanks for the push back to simple troubleshooting step 1 (can’t see the trees because that damn forest is in the way)
LikeLike
Hi Sam,
Glad you found the issue. I was just about to respond that this did not sound like an out-of-the-box GP error. 🙂
-Victoria
LikeLike
If you ever have problems trying to figure out bank reconciliation issues between the GL and the bank tables check out this. http://www.dynamicsgp.cloud/bank-rec-to-gl.html
LikeLike
Hi Victoria,
How can I fix (complete) records that does not exists in CM20300 for posted cash receips (RM20101 table)?
I don´t know why we got this behavior.
Thanks.
LikeLike
Hi Victoria,
I am importing Bank Transactions using IM 2013 and found that the Bank Transaction Journal does not print. I changed the IM.ini settings to show the GP windows and I can see the report print but it closes. Have you ever heard of anyone getting past this? Thank you,
Jim
LikeLike
Hi Victoria,
Is it a requirement to use the Bank Module in GP?
Reason being is that we are using the 3rd Party application to handle the bank reconciliation – this 3rd Party application pulls out the transactions from the General Ledger directly.
So that is why we are not using the Bank Deposit window. Is there any impact of deleting from CM20300 with status Deposited = 0?
Appreciate your response.
Thanks,
Sergio
LikeLike
Hi Sergio,
There is no requirement to use the Bank Rec module. However, I personally would not recommend deleting any data unless you have a really good reason to. You never know if you will want/need it later. 🙂
-Victoria
LikeLike
Hi Victoria, I am wondering if you have posted information of how to undo Bank Rec, please let me know
LikeLike
Hi Patty,
I’ve not done this myself, but here is some info on this from a reliable source:
http://gp.rosebizincblogs.com/2011/10/undoing-a-bank-reconciliation-after-its-already-been-posted-for-dynamics-gp.html
-Victoria
LikeLike
Victoria,
THe link referenced above has been suspended. I too am looking for a way to undo a cleared transaction, do you have anything else you can recommend?
My name is Calvin
thank you
LikeLike
Hi Calvin,
Try this: https://community.dynamics.com/gp/b/gprosesolutions/archive/2011/10/31/undoing-a-bank-reconciliation-after-it-s-already-been-posted-for-dynamics-gp.aspx
-Victoria
LikeLike
Hi Victoria, Do you know if there’s a way to run a daily cash balance report? I know you can see the daily cash balance in GP, but my client wants to see the detail for the daily activity and multiple days in a row (cash trends).
LikeLike
Hi Ann,
I don’t think there is anything like this in GP. You might have to create a custom report for it.
-Victoria
LikeLike
Victoria,
Thank you for your quick response. You confirmed my suspicions.
By the way, your posts are so valuable – my first go-to when I need GP answer.
-Ann
LikeLike
Ann,
Thank you so much – that really means a lot to me!
-Victoria
LikeLike
Hi Victoria,
Not sure if much can be done to help our present situation. But I thought I would ask.
We have a need to have 2 checkbooks in the same company linked to the same Bank Account and GL Account.
Is there any way to do a combined reconciliation?
In order to be able to print both Pre-Printed and Laser Style Checks from AP and PR we had to set them up. I have a SQL Query that allows me to print combined checkbook entries but reconciling them has proved to be a major challenge. I have looked for third party solutions that can do this and asked our partner if they have any solution.
Just wondering if you have come across this and if you have any ideas how to make it easier for our accounting folks.
Thanks,
Gary
LikeLike
Gary,
Nothing easy (or even moderately difficult) will do this in GP. Just paying devil’s advocate here, why not have a separate bank account for payroll? I suspect it would cost you far less in account fees to the bank than it would to do whatever customizations may be needed in GP to allow you to use Bank Rec this way. If it has to be the same bank account, I would maybe talk to Mekorma to see if their product allows you to have a different check layout for the same checkbook depending on where the check is printed from.
-Victoria
LikeLike
Thanks Victoria, this query works perfectly for what I need. Just have to filter the document type for ‘Payments’ and add a date range. Thanks again.
LikeLike
Hi Victoria, trying to do a check register report that includes the vendor class in it for a report my accountant does. Right now its being done manually. Any suggestions on how to pull in the class id from the PM00200 table to go with this report?
LikeLike
Hi Debbie,
Do you have checks entered directly in the Bank Rec module, not going through Payables? That’s not very common… If you only enter checks in payables transactions, you can use something like my view for all payables transactions as a start. The PM00200 table is already linked there, so you can add the vendor class. If you want to differentiate between checks and other types of payments, you may need to add a little more code.
-Victoria
LikeLike
Thanks for the reply! Yes, the void was done in error.
LikeLike
Hi Jana,
If that is the case, you will need to re-enter the payment in AP, right? When you do that, you will have a positive and a negative in Bank Rec, selecting both on the next reconciliation will clear them both out. Hope that helps.
-Victoria
LikeLike
Hi Victoria,
We managed to void a check through payables transaction history (Dynamics 2010) that was previously reconciled. It now show as a negative on our bank reconciliation. What is the best way to fix this? (We have done nothing yet to make sure we do it right the first time).
Thank you
LikeLike
Hi Jana,
To help answer this I need some more details. Was the void done in error? Was it actually a valid payment and should not have been voided?
-Victoria
LikeLike
Hi Victoria…need help-a voided check was accidentally mailed and cleared the bank. To reconcile to the bank a DAJ was done to Debit A/P and Credit Cash. In the next month, a Manual payment was done as cash to relieve the invoices from Payables for the voided check that cleared the bank. Now, the bank rec has a WDL waiting to clear. And A/P aging does not balance with GL. But can’t clear the WDL bcz the DAJ already handled the voided check clearing the bank. So, a General JE was done to reverse the DAJ so the WDL could be cleared. Along the way, another JE was done Dr to Cash Cr to A/P. WDL has also been cleared at this point. A/P aging is still off balance with G/L, if I void a trx – Cash will be off. Did reversing the DAJ as a General JE create an issue with reconciling the bank account? Big mess.
LikeLike
Hi Dana,
As you say, ‘big mess’. While we could try to go through each of the ‘fixes’ above one at a time and try to unravel what has happened, I think I would be doing you a disservice offering advice on this kind of a situation in blog messages. Not to mention it would probably take a couple of months of messages back and forth, because I personally would have multiple questions on every single transaction you’ve mentioned.
Once you’re more than a few ‘fixes’ past the original issue, you would be much better served to have someone actually look at your system and see all of these transactions in detail. I would recommend talking to your GP Partner to get them to look at this with you and help fix it for good.
-Victoria
LikeLike
Hi Victoria! I hope you are able to help me. I found this post about the CM tables very helpful, but what I thought I fixed a couple weeks ago with a Bank Rec issue has come back to me!
The problem I am having is that two cash payments to vendors were voided. They show as voided in the Checkbook Register and in the Vendor Inquiry. However, they are showing up in the bank rec as un-reconciled. After trying a finance reconcile and running checklinks with no success, I updated the CM20200 table field RECOND = 1 for the two records. Later, I did update other fields in this table to show zero dollars and mark it void. This did take it out of reconciliation and after running a checklinks and reconcile, it updated the Adj Book Balance = Adj Bank Balance. Since then, they have moved on to the next month’s reconcile. Now the problem has returned. Now when I run CL, it puts back in the two transactions with zero dollar. However the Adjusted balances are off by the original amounts…
Log story short, what tables to I need to look at in order to get this straightened out? I’ve looked at all of the CM tables and cant see what I am missing. I hope you can point me in the right direction. Thanks so much in advance!
Jim
LikeLike
Hi Jim,
For the record, I do not recommend doing anything like this directly in the tables. As you have seen, it typically creates a bigger issue in the long run as there are many other related tables. At this point, I would suggest working with either GP Support or your GP Partner so they can look at your data and help you fix this. Since there have been multiple changes at this point, without actually seeing it, I am not sure what to even suggest for a fix.
-Victoria
LikeLike
Hi Victoria, thanks for responding. I agree with your statement. Normally I would not touch anything in SQL, but nothing I tried within the GP utilities worked. I think you right in that I need to get Microsoft involved at this point.
Thanks again and thanks for always helping out the community!
Jim
LikeLike
Hi Victoria,
Do you know which table contains the Next IAJ and DAJ numbers?
LikeLike
Hi Jeff,
GP only has one ‘next’ number for all Bank Rec transactions, it is stored in the CM40100 table in the NXTNUM field.
-Victoria
LikeLike
Hi Victoria
Firstly apologies that this probably isnt the right place to post this but I would be very grateful for any advice. When attempting to do an Advance Bank Reconciliation, upon selecting the required Bank Account ID, I am given the following errror:
An ABR Reconciliation For Bank ID ___ ____ Is In Progress By User ______ – Please Try Again When This Has Completed
This has been the case now for a couple of days. I have removed the stated user (and all other users) from GP through Tools–>Utilities–>System–>User Activity… Please can you propose any solutions? (I have a very basic knowledge of SQL so an idiots guide may be needed)
Thanks in advance!
Chris
LikeLike
Hi Chris,
Is ‘Advance Bank Reconciliation’ a 3rd party product? I see a similarly named product from Nolan, is that what you’re using? If so, I would recommend asking Nolan the best way to clear this up. Most likely there is a stuck record somewhere, but they should be able to easily tell you how to fix this.
-Victoria
LikeLike
Was this ever figured out? We are having the same error. We tried clearing them out of system user activity with no fix.
LikeLike
Yes, ABR is Nolan’s 3rd party product. It has its own separate ABR Clear User Activity feature, which should help you in your situation.
LikeLike
Hi Victoria,
We already have referred this issue to our GP parter, they just said that GP should work fine with multiple users of a company accessing select bank transactions window at the same time provided they are reconciling different checkbooks. That’s what I also thought before we came upon this issue. But after making series of testing on different server and on different companies including Fabrikam on a standard GP installation this notion proved to be inaccurate. Anyways, I guess we just have to make some workaround so we can maximize the time of our people while they wait their turn to do bank recon in GP. Thanks Victoria for always replying to our GP inquiries.
Herson
LikeLike
Hey Victoria,
I always appreciate your blog posts and wisdom. We are experiencing extreme performance problems with our EFT process because of the number of records we have in CM20300 (over 1M in one company alone). The Microsoft KB article 2531207 says this is a known bug corrected in the 2011 Payroll Year End Update. The KB also says it’s a viable solution to simply delete records out of this table if you’re not using bank reconciliation.
I’m torn between going with the service pack, which may make a lot of changes we don’t necessarily want, versus deleting records, which isn’t ideal either but in a way seems less risky. Do you see any drawbacks from either approach? Would you recommend applying the patch or deleting records?
LikeLike
Hi Jay,
I am not familiar with this particular issue, however my gut feeling would be that deleting records should be a last resort. Usually service packs do not make a lot of changes, they may fix existing issues or add some functionality, but they rarely break anything. To me, that would be the preferable option. That said, again, I am not familiar with your particular setup or situation, so it may be best to discuss this with your GP Partner to get their feedback on it.
-Victoria
LikeLike
Hi Victoria,
Thank you so much for your reply. Yes, I could recreate the same scenario even for a standard GP installation (without customization) on Fabrikam company with LESSONUSER1 and LESSONUSER2 users reconciling checkbooks PAYROLL and UPTOWN TRUST. We do have 3rd party product installed but I believe it should not in anyway get in the way of bank recon module as the customization deals only with the computation of tax. And as I’ve mentioned, the scenario can be recreated
on a different server with GP’s sample company Fabrikam so the server and customization could not have cause the issue. Hope we can clear this or maybe you could suggest a workaround so that multiple users can do bank reconciliation simulataneously because we have many checkbooks to reconcile. Thank you Victoria for being so accommodating to our inquiries.
herson
LikeLike
Hi Herson,
I do not have access to a test GP 9.0 environment right now, but I just tested this with GP 2010 and am not able to duplicate this behavior. Since you’re on a version of GP that is no longer supported, you cannot go to Microsoft to ask about this, however, you could try posting this question on the GP community forum to see if anyone else has run into this or has any other ideas.
For what it’s worth, my gut feeling on this is that it is not a bug or known issue, but something specific to your environment/data. Having multiple users perform bank reconciliations simultaneously is not uncommon and based on my understanding of how GP stores the bank rec transactions, there should never be any cross over like what you’re describing. The fact that you can see it in the sample company with the default checkbooks seems to indicate that it is not your data, but rather something in application and how it is displaying the data. Unfortunately, without being able to see this in action, I am not sure how much more can be done to troubleshoot it. Have you talked to your GP Partner about this?
-Victoria
LikeLike
Hi Victoria,
We’re still on GP9. The problem we encounter right now is that when 2 users do bank reconciliation at the same time for 2 different checkbooks, the select bank transactions windows for both of them shows mix of the two checkbooks transactions. Is this the normal behaviour of GP? Does it limit only one user at a time to do the reconcile bank statement? Any workaround or fixes on this? Hope to hear from you. Thank you very much Victoria.
herson
LikeLike
Hi Herson,
I have not heard of this being a problem before in any version of GP. Can you recreate this at will? After restarting the SQL Server and the computers the 2 users are on? Do you have any customizations or 3rd party products installed?
-Victoria
LikeLike
Hi Victoria,
We were posting a payable check through Purchasing>Post Checks when the machine was stuck and hanged, which made us terminate the GP application. When we reopened the batch, it no longer existed in the Payables Batch IDs. We located the Batch number in Batch recovery and we continued it and it disappeard in Batch Recovery. We checked if the Check Balance was updated by going to Financial>Inquiry>Checkbook Balance and Financial>Inquiry>Checkbook Register but unfortunately, the checkbook ID did not dispaly the payables documents that have been applied. We checked tables CM20100, CM20200 and CM20300 respectively and no record were found for the said Checkbook ID.We also Checked GL10001 but there was no entry in there either. The Payables Documents were posted and applied,we did verify it in PM30200 and PM30300 tables respectively.
Can we fix this by running Reconcile or Check Links? Any suggestions will be highly appreciated. Thanks. 😀
John
LikeLike
John,
I would certainly try Check Links and Reconcile – and I would run it on the entire Purchasing and Financial series. However, from what you are describing, it’s possible that will not fix this, in which case I would recommend getting help from your GP Partner or GP Support for this so they can take a look at your data and determine what the best approach is.
-Victoria
LikeLike
Hi Victoria – we had to remove the user’s access to the modified version of the bank reports because we think the issue is related to “field level” security that we were not able to easily detect with any SQL scripts (checking activity tables for denied access once activity tracking was turned out – great feature if anyone cares – I use it often). We think it’s field level because when we gave the user “poweruser” access and left their access to the modified version of the bank reports, they were able to execute them without receiving the calculation error. It’s only when we reverted their security back to the custom tasks/roles and left them with access to the modified version of the bank reports that they would receive the error. So, rather than keeping the user with poweruser access, we opted to give them access to the non-modified (Microsoft Dynamics GP) version of the reports and that has allowed the user to run their reports. The layout is not ideal in the reports they receive, but it’ll do in a pinch. If/when we discover the offending field/security, we’ll let you know.
LikeLike
Hi Kristie,
Thanks for the update. I am not a big fan of field level security precisely because of stuff like this – I have seen it cause similar issues ni the past. 😦 Good to know that you at least have a work around.
-Victoria
LikeLike
thanks much for the reply. 😀
LikeLike
We feel like we have provided a user with all of the correct security tasks which should allow them the ability to perform bank reconciliation tasks (happy to list if needed) but when the user attempts to do so they receive this error message “Error in equation ‘trxvoided'” and I am not able to find anything on this. Has anyone had the same issue and if so, please, how were you able to resolve it?
LikeLike
Kristie,
What version of GP? That sounds like an error in a report, are any of your Bank Reconciliation reports modified? If so, can you point this user to the unmodified reports and see if you still have the error? Does the Bank Rec actually get completed after this error comes up?
-Victoria
LikeLike
Hi Victoria
I have a user who was applying a deposit in cash recipts, gp froze up then booted her out. She went back in to try and retrive the deposit to finish it , it was not there. When she tried to start over the invoices were not available to select GP is telling her that the invoices are applied to a payment. When we pull up the payment number and click on it we get a message that it does not exist.
Any ideas?
thanks
Vic
LikeLike
Hi Vic,
Have you tried running reconcile? Or check links?
-Victoria
LikeLike
Victoria
No I have not. Great idea! What modules/Tables should I run them on
Thanks
Vic
LikeLike
Vic,
This was an issue entering a receivables cash receipt? If so, then Sales. I typically run check links on all tables.
-Victoria
LikeLike
Hi Victoria,
I’m a newbie to GP so please bare with me. I’m reconciling my bank rec and my Adjusted Bank balance does not equal the adjusted book balance. My Book balance is correct though. What could be wrong? Thank you.
LikeLike
Hi Rev1976,
This is not something easy to help with in blog comments, as I do not know the details of your GP setup and there are many things that can contribute to what you are seeing. If you are just learning GP, I would recommend getting some training on Bank Rec from your GP Partner. Often going through the first reconciliation together is a great learning experience.
-Victoria
LikeLike
Hi Victoria
please what would be the key fields to create an extender in financials/bank transactions entry window?
Checkbookid, DepositNumber and DepositType would be enough?
Thanks a lot
LikeLike
FAC,
Deposit Number and Deposit Type are not on the Bank Transaction Entry window in my GP version (using US installation). Are they on yours? Or are you asking about a different window?
If you do need this for the Bank Transaction Entry window, from what I can tell by looking at the tables, the Record Number is the key field to use …doesn’t look like you need anything else.
-Victoria
LikeLike
Thanks Victoria
i was looking at the wrong tables (CM10100 deposit work), there are no work tables for CM Bank Transactions (CM20100) right?
The problem is if i create an extender on Bank Transactions Entry and I use RecordNumber as Key Field when i enter data in that extender in bank transaction entry, the key value (Record Number) is 0.00000.
I think it only gets its real value when it is posted, after posting i went to inquiry bank transaction and to the extender inquiry window an record number is 3915.00000 and no data in the other fields.
Thats what i was thinking of using CHEKBKID, CMTrxType and CMTrxNum as Key Fields of that extender.
I hope you can understand me.
Thanks a lot for your time Victoria.
LikeLike
FAC,
Yep, that makes sense. I have not tried this and would not be able to tell you without trying whether it will work for certain, but the CHEKBKID, CMTrxType and CMTrxNum as key fields sound correct to me. 🙂
-Victoria
LikeLike
Hi Victoria,
We just installed Analytical Accounting. And when we reconcile the bank, we are getting the Analytical Accounting Error List and we are unable to continue with the bank rec. The transactions that come up on this error list are INT…, OIN… etc. and these transactions are very old and reconciled already. I’m puzzled as to why these errors are coming up now saying AA required and your prompt response on how to fix this will be much appreciated.
Thanks,
Depar
LikeLike
Hi Depar,
I don’t work with AA too much, but I believe I have seen similar issues in the past when AA was installed, but not set up completely. If it is already set up and you are still receiving errors, then I would recommend looking up the exact error in the Dynamics KnowledgeBase as a first step and maybe posting that on the GP Community forum next to see if you can get some folks that have seen the same error and can help further.
-Victoria
LikeLike
Thank you very much.
LikeLike
Hi Victoria,
I created an Integration for entering data in Bank Transaction in GP. This integration pulls data from an excel file. When I run this integration once, the data enters GP and everything is fine. But If I run it again, the data gets duplicated. Is there a way to avoid duplicates in GP?
I searched a lot on many websites. One solution I got for this is to use a VB Script in IM. Can you help me with this VB Script as I have no idea how to do this.
If there is any other way, then also let me know. Any hlep will be appreciated. Thanks.
Pooja Shah
LikeLike
Pooja,
Two ways I can think of to avoid this:
For help with scripts, you may want to contact your GP Partner.
-Victoria
LikeLike
Hi Victoria,
Thanks for your reply.
But as far as I know, for Bank Transactions window, GP is allowing duplicates. I am using GP 2010. I go to GP->Financial->Transactions->Bank Transaction Window. Here there is a field called Number (CMTrxNum). I entered ‘1’ as the number twice while entering a transaction and posted it & it allows that. It doesn’t give me any message that the number already exists.
If I do the same in Payables Transaction entry window (GP->Purchasing->Transactions->Transaction Entry) and write the same document number again, it does give a message in GP that ‘This document number already exists’.
Is there any solution for this?
Is there any field in Bank Transaction window which is unique and doesn’t allow duplicates?
Pooja Shah
LikeLike
Hi Pooja,
You are right, I was thinking that the combination of checkbook/transaction type/transaction number has to be unique. But I just tested this and it does not seem to be the case, so that’s not a valid method. If you cannot use a non-automated way (manually checking or marking something as already imported), then you may need to code around it – this is beyond what I can help with, you would need to talk to a GP developer for help with that.
-Victoria
LikeLike
Hi Victoria,
Thanks for the quick reply.
So, I think code is the only solution.
Thanks again.
Pooja Shah
LikeLike
Hi, Victoria,
Thank you for publishing all this useful information.
Do you have any idea what the field values might be for the RECONUM field in the Bank Rec tables? I’ve been looking, but have so far been unable to find anything.
Regards,
Lyn
LikeLike
Hi Lyn,
Looks like this is a link to the bank reconciliation that a record is on. In my data, it looks like all the unreconciled transactions have a value of 0 for this and all the others correspond to a record in the CM20500 table (the reconciliation headers).
-Victoria
LikeLike
Wow, thanks so much for the quick reply! I appreciate your help.
Have a great day!
LikeLike
Hello Victoria,
One of my customers wants to have batches for Bank Reconsiliation (which is not available out of the box offcourse). Can you please help me in analyzing its feasibility?
1.Why do u think it is not available out-of-the-box?
2.what can be the challenges if we try to add this feature through customizations?
LikeLike
Hi Sohaib,
I do not have any special knowledge of why the Bank Rec module was written to not allow batches. My guess is that since not too much should be going directly through the Bank Rec and since most companies want to see the effects of cash movements immediately, it may have been felt by the original designers that batches simply would not be needed. While I always make sure that my customers understand that there are no batches in Bank Rec because it is different from other GP modules, I have not had a single customer experience any issues because of this.
Why does your customer need to have batches in Bank Rec? What specifically are they trying to accomplish and what types of transactions would they need to batch? Would this be only for manually entered transactions? Transactions coming from other modules? Imported transactions? In general, and without seeing your customer’s detailed requirements, I would not expect this to be a trivial customization.
Looks like you’ve already started a thread on this topic on the GP Community Forum, so maybe some others will have more feedback for you.
-Victoria
LikeLike
No, I’d prefer to void them if possible. Maybe I’ll have to go the macro route? We were not allowing duplicate checks. So entering in duplicate checks in Bank Rec is allowed, NOT good.
LikeLike
Ouch, not good, but good to know. Yep, I would want to void them, also. Macro sounds like the next best option.
-Victoria
LikeLike
Any idea how to use integration manager to void a check transaction? My original integration brought in checks twice and I need to void the 2nd ones. Oops.
LikeLike
Hi Brynne,
I am not aware of a way to bring in a void check using IM. The only thing I can think of is to import increase adjustments to ‘reverse’ the checks, but that still leaves the original checks in there, which may or may not be an issue. And still has to reconciled. Did you have ‘Duplicate Check Numbers’ allowed on the checkbook setup? I am just wondering if that would have prevented it…doesn’t help with this situation, but maybe for the future?
-Victoria
LikeLike
Hi Victoria,
Quick question: Are you aware of any product that will allow bank transactions to be uploaded into GP? We are looking to automate the process of entering the daily bank deposits since we have many transactions and many accounts. I know there are E-Bank Rec products out there, but bank recs aren’t an issue for us when it comes to time spent, just the entering of transactions.
Thanks and regards,
Gordon
LikeLike
Hi Gordon,
Both Integration Manager and eConnect can import Bank Transactions.
-Victoria
LikeLike
Thanks Victoria.
We have Integration Manager, but not eConnect. I had no idea IM could be used for this.
Are there any templates/step by step instructions/websites that would show me how to do this?
Thanks,
Gordon
LikeLike
Gordon,
There should be a sample integration for this that gets installed with Integration Manager. For anything more, I would recommend getting some help from your GP partner. Bank transaction imports are a little different from others – as there are no batches in Bank Rec, they get posted right away, so you may want to do some testing first.
-Victoria
LikeLike
Hi Victoria
I was wondering If you had a view that could show me the sales tax paid on invoices shipped in a certain state. Specifcally Ohio.
Thanks
Vic
LikeLike
Hi Vic,
Not sure how this is related to Bank Rec?
You should be able to get the information you’re looking for by using the Sales Transactions SmartList.
-Victoria
LikeLike
Hi Victoria
Happy 2011 and congrats on your 7th year as an MVP.
Unfortunately, for me our 1st bank reconciliation is giving me the blues. I have this h-u-g-e difference after (1) entering my bank statement ending balance (2) entering my cut off date (3) ticking all my items that reconcile to my bank statement (4) entering my adjustments.
I cannot figure out how GP is calculating this h-u-g-e difference so I need to find a solution to make it zero so that I can reconcile the bank account. My questions are firstly, what are the correct steps for doing a bank reconciliaiton for the 1st time & secondly, the only way I can think of to get rid of my difference is to post an IAJ or DAJ. What do you think? Will this transaction “bite” me later?
LikeLike
Lulu,
It’s sometimes difficult to help with bank rec questions without seeing it. Sometimes the difference can be handled by entering a different ‘Last Reconciled Balance’ on the Checkbook Maintenance window. Other times an adjustment is needed for the very first bank rec. If you are confident that all your numbers are correct and need to simply wipe out the difference to be able to reconcile, you can enter an increase or decrease adjustment, as you’re suggesting, with a debit and credit both going to the Cash account for the checkbook. Can it “bite” you later? Possibly. If something was incorrect you will most likely need an additional adjustment on the next bank rec. Also, if the amount of your adjustment is very large, your auditors may flag it and you will have to explain why it was needed. You should enter a note as an explanation anyway, so this is not a mystery to anyone looking up the transaction later.
-Victoria
LikeLike
Hello again,
I’ve triend entering the last reconciled balance for the previous month in the Checkbook maintenance window but it didnt help, it just made my difference bigger. If only I can figure out how GP is calculating this difference and storing the values that appear in the Adjustments window! I think my adjustment entry is my only solution in this case.
Thanks for your help as always
LikeLike
Victoria,
Thanks for the reply. I did see that but did not see my comment here so I didn’t update. I think I was looking at the bottom of the page. I was basically trying to cheat and mark all the differences from a test company (restored from a “bad” version of live) to live. I decided to just clear them manually. 🙂
LikeLike
Hi,
Any idea where the Cleared Difference done during a bank reconciliation are stored? I think I’ve looked in every CM table!
Thanks!
LikeLike
Stefanie,
I don’t believe the cleared ‘difference’ is actually stored anywhere… what I do see is the cleared amount (ClrdAmt) in CM20200 that is different from the transaction amount (TRXAMNT) for transactions that were cleared for a different amount.
-Victoria
LikeLike
Hi Victoria,
I am having an issue with an existing Crystal Report I have created that pulls data from the Cash Management tables. Everything seemed to be working fine until a recent snag. Basically what I need this report to do is report CM transactions, both inflows and outflows, which it does. We have multiple companies, so I created the SQL View and everything was working fine until I added new bank accounts. What I now need the report to do is report CM transacations grouped by company and then by bank account (Operating and Deposit – CHKBKID). I get the Operating account info displayed just fine, no issues. However, since I added these new Deposit accounts, those accounts do NOT get displayed on the report and for the life of me I cannot figure out why.
Hopefully I have provided enough info for you to come up with some solutions.
Thanks and regards,
Gordon
LikeLike
Gordon,
Since the report is working correctly for one of the accounts, if I had to guess without looking at it, I would say that you have a restriction for that one account somewhere. If you are using a SQL view or stored procedure, it could be there. Or, it may be in the Crystal report itself – maybe in the Record Selection Formula?
-Victoria
LikeLike
Hi Victoria,
Thanks for the prompt reply and that was my initial thought too. I will go back and double check that, but I didn’t notice anything the first time through. Very strange. I’ll let you know.
Thanks,
Gordon
LikeLike
Hi Victoria,
I have pasted below the SQL View that I am using (well, one segment of it. It is copied to include all 10 of our companies.) As you can see, there are no restrictions in it. So, it must be at the report level. I just haven’t been able to locate the issue yet.
Thanks,
Gordon
SELECT ‘SMP’ AS Company, [Cash Management Trx].CHEKBKID, [Cash Management Trx].CMTrxNum, [Cash Management Trx].CMTrxType,
[Cash Management Trx].TRXDATE, [Cash Management Trx].TRXAMNT, [Cash Management Trx].paidtorcvdfrom, [Cash Management Trx].VOIDED,
[Reconciliation Headers].CUTOFFBAL, [Reconciliation Headers].CUTOFDAT
FROM SMP.dbo.CM20200 [Cash Management Trx] INNER JOIN
SMP.dbo.CM20500 [Reconciliation Headers] ON [Cash Management Trx].CHEKBKID = [Reconciliation Headers].CHEKBKID
LikeLike
Gordon,
You’re using an INNER JOIN to link the 2 tables in your code, which means that the checkbook ID has to be in both tables to make it onto the report. CM20500 will only include a checkbook after at least one Bank Reconciliation has been run for it, so it would make sense that newly created checkbooks would not be in there yet. Try changing this to a LEFT OUTER JOIN.
-Victoria
LikeLike
Victoria,
Thanks. That worked!
You rock!
Regards,
Gordon
LikeLike
Hi
Is there a way I can pull the daily or monthly checkbook balance into a crystal report? thanks!
LikeLike
I am not aware of any table that stored this information, so you would have to calculate it yourself.
-Victoria
LikeLike
wow that was fast! i have not worked with bank rec much –
if i did a running total by date on the trx in these tables CM20200 CM20300 – would that work?
LikeLike
I am not sure I would do it this way. What about using the GL instead? Why not just pull the numbers for the GL account number associated with the checkbook?
-Victoria
LikeLike
Got it, thanks!
LikeLike
Hi Victoria,
I was wondering if you’ve ever experienced getting stuck in the Bank Transfer window? This usually happens when we do intercompany transfers and now we’re left with several batches to be posted to our G/L that cannot be posted because they cannot be recovered. Do you have any suggestions on how to get rid of these “stuck” batches? We’ve moved all the transactions into other batches that could be posted so they’re basically empty batches. Thanks!
LikeLike
Kathryn,
I have not heard of anyone getting stuck in a Bank Transfer window before. Do you have a customization or a 3rd party product that is allowing you to do intercompany Bank Transfers? That is not part of the out-of-the-box GP functionality and I am wondering if that is possibly what’s causing the issues you are experiencing.
If you have GL transactions that cannot be posted and are not needed anymore because you have manually re-entered them, you should be able to remove them directly from the tables. However, I hesitate to simply post advice for doing that, because it is very easy to make a mistake and wipe out critical data. This is something that should be done very carefully by someone that understands SQL Server and the GP table structure well.
-Victoria
LikeLike
Hi Victoria,
Thanks for the input. We do have a 3rd party product that allows us to do Advanced Intercompany Transactions and it probably is this product that is causing us trouble. No worries, we’ll deal with removing the batches when we do the upgrage to GP 10 with our consultants.
Thanks! K.
LikeLike
Looks like a record is held in CB200006 table – I also haven’t heard about this message until I had to unlock an end user…
LikeLike
Hi E-zroda,
In my default US install of Dynamics GP I do not see any tables starting with CB. Not sure if that is part of a non-US module or a 3rd party product…?
-Victoria
LikeLike
I believe this module in pre-GP10 was called European Cashbook Bank Management (CBM). From what I’ve heard about it, it has been bought by MBS and integrated in the core offering.. At the moment gets installed when you tick Bank Management on a standard UK install. It is not considered a third party add-on anymore, but the full integration is still not a reality (e.g. there are two separate Chequebook Master Tables – one prefixed CB and one CM… go figure…) It is great fun though – especially when there is a case of data corruption.
Thank you for the prompt reply , by the way – what time it is at your end? 🙂
LikeLike
E-zroda,
Thanks for the additional info – good to know in case this ever comes up again.
We are in EDT, so it is 6:25am right now. 🙂
-Victoria
LikeLike
Hi Vistoria
I am using GP 10, I have noticed, whenever a CM deposit is made and posted for AR Cash Receipts, Received From information does not get update field ‘paidtorcvdfrom’ in CM20200.
Is this a bug or am i missing something
Would appreciate any help on this
Thanks and regards
LikeLike
Shan,
Since a deposit is typically a grouping of multiple cash receipts, it is logical that the deposit itself would not have a Paid To / Received From entry. The individual cash receipts associated with each deposit would have the Paid To / Received From information. I would not consider this a bug nor a flaw in design.
-Victoria
LikeLike
Thanks Victoria. That clears my doubt
LikeLike
Thank you Victoria for getting back to me so quickly.
I was afraid that was going to be your answer, but I think just entering a correcting JE will do the trick as well.
Again, I want to express how much I appreciate your time in sharing your knowledge with the rest of us.
Thanks!
Stephanie
LikeLike
Hi Victoria,
Is it possible to “undo” a bank reconciliation?
There is a situation where posting was set to post to on bank trx entry, and the entry was done in Aug but the GL batch was posted until September. The checkbook reconciliation was done for August and the receipts were deposited and reconciled…and it wasn’t until October that it was discovered the GL for September did not match the bank rec due to the posting differences. Is there any way to go back in and undo the bank reconciliation for August, so that the receipts could be voided in August and re-entered and posted to the GL in the correct month, or is there a better way to handle this?
Any help is greatly appreciated.
And thank you for all your great posts and information, I utilize your website all the time, as it is so helpful!
Thanks,
Stephanie
LikeLike
Hi Stephanie,
There is no way to undo a Bank Reconciliation. If it just happened and you needed to undo it, possibly restoring from a backup would be an option, but most likely this was done a while ago, so it is too late now. If this is just a timing issue, ie, it catches up in September, and this is not your year end, many companies will find it sufficient to simply add a note to their reconciliation files explaining this. Another option is entering a GL adjustment and reversing it the following month to ‘correct’ for this.
-Victoria
LikeLike
Hi Victoria,
I have this error “There was an error posting to the CBM Bank Reconciliation”.
This error appeared after my computer hanged up during voiding of payment in the Payable Module.
Please help me on this.
Thanks.
LikeLike
Victoria,
Thanks so much for posting this information and making it so readily available!
Can you tell me how to relate CM information to the GL20000 table? Specifically, I’m identifying document types (ORTRXTYP) based on their series (ex: Series = 3, ORTRXTYP =1 is a ‘Sales Invoice’); how can I provide a user-friendly name for CM transactions entered through the Bank rec module?
LikeLike
Rick,
Looking at even a small subset of data, I don’t see how you can use the ORTRXTYP for Bank Rec transactions. I have not done any testing on this, but I would probably start by linking the GL20000 to CM20100 using:
GL20000.ORTRXSRC = CM20100.AUDITTRAILand GL20000.ORCTRNUM = CAST(CM20100.RecNumControl as char)
Then you can use the list of values for CMTrxType above to get the friendly name for the transaction type.
-Victoria
LikeLike
Victoria,
Yeah, I totally understand that it’d have to be a webex session. I have tried a couple alternatives and also just posted an entry on newsgroup.
I’m going to open a Support case with Microsoft, hopefully we could get it clear out.
Thanks so much for your suggestions.
Warm Regards,
Nikki
LikeLike
Victoria,
Still no luck with previous bank deposits. =(
LikeLike
Nikki,
Sorry, this is possibly one of those situations that someone would have to take a look at your system and the tables for. Pretty difficult to figure out what the issue is without seeing it. 😦 Did you try posting this on the GP newsgroup? Maybe someone else will have some more ideas for you to try?
-Victoria
LikeLike
Hi Victoria,
– Yes.
– Yes, it is definitely an order.
– I’ll definitely look into this. Will keep you posted.
Thanks so much for your help.
~Nikki
LikeLike
Hi Victoria,
Thanks for getting back to me so quickly. The credit card is setup to go to a checkbook ID.
LikeLike
Nikki,
A few other things to check:
– Has the person who entered the deposit closed the Sales Transaction Entry window?
– This is definitely an order, not an invoice? On an invoice, the deposit will only be posted once the invoice is posted.
– Have you looked at previous Bank Deposits to make sure it has not already been deposited?
-Victoria
LikeLike
Hi Victoria,
We have a customer who has an Order with a credit card deposit tied to it. However, when we go into Bank Deposit, we couldn’t find the deposit for that particular Order.
Could you please shed some lights on the possibilities this could happen?
LikeLike
Nikki,
How is the credit card set up? Is it set to go to a checkbook or a GL account? If it’s set to go to a GL account, you won’t see the receipt to be deposited until you enter a receipt Bank Transaction.
-Victoria
LikeLike
Thankyou very much Victoria,
You are the best!
LikeLike
Hi, Victoria,
I am trying to copy all my current company file to a testing company file to test bank reconciliation feature(version 9.0). Should I use intergration manager to import all the transactions to a testing company file? Would you please give me some insight?
Thank you very much!
LikeLike
Lily,
KB article 871973 might help: How to set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, or SQL Server 2005.
-Victoria
LikeLike
Hi Victoria,
I am looking to resolve an issue regarding an EFT Error message. “Another User is Already Generating EFT Files”.
I went through knowledge base, and discovered I can clear the EFT Activity Table – ME234601 (ME_EFT_Generation_Activity), however this table does not exist in GP10??
Do you know what the table is in GP10, so that I can resolve this issue?
Thank you!
LikeLike
Hi J.,
Sorry, I am not seeing that table anywhere. And while there is a list of changes from GP 9.0 to GP 10.0 in the SDK with a bunch of ME tables on it, there is nothing for this particular table. Have you tried the new Microsoft Partner forums to see if you can get an answer on this?
-Victoria
LikeLike
I know this is an old post, but it came up when I was searching for this error. Hopefully this response helps someone else. We fixed this error by clearing out the locked entry on the SY00800 table on the DYNAMICS database.
LikeLiked by 1 person
Thanks Michael!
-Victoria
LikeLike
Thanks Victoria. That is exactly what I needed
Seth
LikeLike
Victoria,
I am trying to link an accounts payable check to all of the voucher numbers that the check paid in a sql query. Can you point me in the right direction for the CM tables linked to the correct PM tables to do this?
Thanks,
Seth
LikeLike
Seth,
Here is some code I posted up a while ago to bring back AP document apply information: https://victoriayudin.com/2008/10/22/sql-view-ap-apply-in-gp/. If there is something additional you need to link to the Bank Rec information for, I believe you can just add the following code right before the WHERE section at the end:
LEFT OUTER JOINCM20200 CM ON CM.CMTrxNum = P2.DOCNUMBR
AND CM.CMLinkID = P.VENDORID
-Victoria
LikeLike
Hey! Thanks for the quick response! I am trying to link RM00101.UserDef1 to actual Cash Deposits. Possibly CM20300.Checkbook_Amount.
We have both Residential and Commercial customers and would like to differentiate between the two for cash collections. Currently the UserDef1 field contains the information that can distinguish them apart. Thanks for all your help!
– William
LikeLike
William,
It sounds like you first have to link from CM20300 the to the cash receipt transaction (either in RM20101 or RM30101) using the logic in my previous comment. Then you will have the Customer ID in RM20101 or RM30101. Once you have that, you can link on the Customer ID to RM00101 and pull in the user defined value. Hope that helps.
-Victoria
LikeLike
William,
What specifically are you trying to link? Cash receipts? It looks like depending on where the cash receipt came from you would need to add some different logic. I have not fully tested this, but try something like this:
RM20101.DOCNUMBR = CM20300.SRCDOCNUMor RM20101.DOCNUMBR = CM20300.RCPTNMBR
-Victoria
LikeLike
Hi Victoria!
Do you know the easiest way to link the CM tables to the RM tables? I am having trouble finding the correct keys. Thanks!
– William
LikeLike
Hi Victoria,
Is it possible to generate a daily cash position report in GP or does it have to be done in Crystal? Thanks.
herson
LikeLike
Herson,
In versions 9.0 and 10.0 of GP there is a Checkbook Balance Inquiry window (Inquiry > Financial > Checkbook Balance) that will show you the balance as of a specified date. This did not exist in older versions of GP. Let me know if that’s not what you’re looking for.
-Victoria
LikeLike
It’s not a requirement to use it, but it will still need to be set up.
-Victoria
LikeLike