Share this:

Like this:

156 Responses to “Bank Rec Tables”

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

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.

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.

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.

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)

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

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. 🙂

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).

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.

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.

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?

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.

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.

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).

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.

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.

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

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.

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

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)

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.

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.

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?

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.

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.

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?

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.

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?

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. 😀

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.

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.

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.

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?

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?

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.

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.

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.

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.

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. 🙂

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.

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.

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.

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?

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.

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).

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.

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?

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.

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.

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?

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.

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 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. 🙂

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.

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.

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?

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.

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.

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!

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.

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.

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? 🙂

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.

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

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.

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?

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.AUDITTRAIL
and 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.

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?

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?

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?

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?

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.

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?

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 JOIN
CM20200 CM ON CM.CMTrxNum = P2.DOCNUMBR
AND CM.CMLinkID = P.VENDORID

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!

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.

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.SRCDOCNUM
or RM20101.DOCNUMBR = CM20300.RCPTNMBR

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

Trackbacks/Pingbacks

[…] Victoria Yudin | Bank Rec Tables – 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 … […]

[…] GP Bank Rec table information A new page is now available with table information for the Bank Reconciliation module. I have reorganized all the pages a little so they are now alphabetized on the sidebar to the […]