Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. KB 860314 - No transactions show when you open the Select Bank Transaction window in Microsoft Dynamics GP

KB 860314 - No transactions show when you open the Select Bank Transaction window in Microsoft Dynamics GP

View products that this article applies to.

Symptoms

The Select Bank Transactions window shows no transactions when you try to reconcile your checkbook in Bank Reconciliation.

↑ Back to the top

Cause

This problem may occur for any of the following reasons:
  • Bank Reconciliation is not registered or marked in the Registration window.
  • The Reconcile Number column (RECONUM) in the CM20200 table does not match the Reconcile Number column (RECONUM) in the CM20500 table.
  • The transactions have already been reconciled. The specified transactions will now have a value of 1 flagging the transaction as already reconciled in Microsoft Dynamics GP. Therefore, it will no longer be pulled into the Select Bank Transactions window when you perform reconciliation by clicking Transactions, pointing to Financial, clicking Reconcile Bank Statements, and then clicking Transactions.
  • The records were in a damaged reconciliation.
  • The SQL Sort Order/Collation is not supported with Microsoft Dynamics GP.

↑ Back to the top

Resolution

To resolve this problem, follow one of the methods below: 

METHOD 1:
  1. Verify that Bank Reconciliation is registered and marked in the Registration window. To do this, click on Tools under Microsoft Dynamics GP, select Setup, select System and click on Registration. Scroll down in the Registration window and verify that Bank Reconciliation is listed and the checkbox is marked.

    METHOD 2:
  2. Verify that the transactions have not yet been reconciled. To do this, follow these steps:
    1. Click Inquiry, click Financial, and then click Checkbook Register.
    2. Type or select the appropriate checkbook ID.
    3. On the View menu,click by Date, and then click the From field.
    4. Set the From field to have a zero value, and then type the date that is used as the bank statement ending date or cutoff date in the Reconcile Bank Statements window.
    5. Click Redisplay.
    6. Click Show Details to expand the provided information and verify that the Reconciled column is set to Yes. If the transactions are not reconciled yet, and you are reconciling, the RECONUM field from the CM20500 table may be the reason that you do not see any transaction in the Select Bank Transactions window.

      In Microsoft Dynamics GP, a specific RECONUM value is assigned to the checkbook when information is entered in the Reconcile Bank Statements window. When a transaction is marked in the Select Bank Transactions window for this checkbook, then the RECONUM column for that transaction in the CM20200 table is populated with that same RECONUM value that is in the CM20500 table. If the RECONUM values in the CM20200 table and the CM20500 table are not the same, then no transactions will be generated in the Select Bank Transactions window during the reconciliation process.

    METHOD 3:
  4. Verify the value of the Reconciliation Number matches between tables. To do this, follow these steps: 
    1. Open SQL. To do this, do one of the following:
      • For SQL Server 2005, open SQL Server Management Studio, click on New Query, and select the correct Company database.
      • For SQL Server 2008, open SQL Server Management Studio, click on New Query, and select the correct Company database.
    2. Verify that the RECONUM field has the same value in the CM20200 table and the CM20500 table by running the following SQL statements.
      SELECT MAX(RECONUM) FROM CM20200 WHERE CHEKBKID = 'XXX' 

      SELECT MAX(RECONUM) FROM CM20500 WHERE CHEKBKID = 'XXX' 

      SELECT RECONUM FROM CM20500 
      WHERE CHEKBKID = 'XXX' 
      and RECONUM = (select MAX(RECONUM) from CM20200 where CHEKBKID = 'XXX')
      Note In these statements, the XXX placeholder represents the checkbook ID.

      Also run this script to see if there is a damaged or stuck recon for the checkbook ID:
      select distinct (RECONUM) from CM20200 where RECONUM not in (select RECONUM from CM20500) and RECONUM <> '0.00000' and CHEKBKID = 'xxx'
      ---where xxx is the checkbook ID you need
       
    3. Note the results that are generated from the SQL query in step 2.
    4. Verify that the following statement is true:
      The maximum value of the RECONUM field from the CM20200 table that is generated from the first statement from step 2 is one number less than the maximum value of the RECONUM field from the CM20500 table that is generated from the second statement that is outlined in the same step.
    5. The result that is generated from the third statement that is outlined in step 3 returned with blank results. If statements from the previous step are verified to be true, run the following SQL query to further resolve the issue: 
      update CM20500 set RECONUM = (select MAX(RECONUM) from CM20200 where CHEKBKID = 'XXX') 
      where RECONUM = (select MAX(RECONUM) from CM20500 where CHEKBKID = 'XXX')
      Note In this query, the XXX placeholder represents the checkbook ID.
  5. Log on to Microsoft Dynamics GP. Click Transactions, click Financial, and then click Reconcile Bank Statements. Select the checkbook ID, and then click Transactions. Verify that the transactions now show correctly in the Select Bank Transactions window. 


    METHOD 4: Run check links:
  6. Make a backup or do this in a test company first. Under Microsoft Dynamics GP, point to Maintenance and click Check Links. Select the Financial Series. In the Logical Tables list, select CM Transaction and click Insert so it displays under Selected Tables. Click OK. Review the Error Log Report and if you agree with what it did, then you can do this in the live company.
  7. Now check to see if the transactions show up in the Bank Reconciliation window.

    METHOD 5: SQL Collation:
  8. Run the below script in SQL Server Management Studio to check the SQL Sort Order and SQL Collation. In the results, find the line for the company that you are working with and drag the Status field out so you can read the data in this field. Verify that the Collation and SQLSortOrder are a supported version. (SQL Sort Order should be 50 or 52). The window may not populate if you are not on a supported version. 

    SP_Helpdb

↑ Back to the top

Keywords: kb, kbsurveynew, kbexpertisebeginner, kbtshoot, kbprb, kbexpertiseinter, kbmbspartner, kbmbsmigrate

↑ Back to the top

Article Info
Article ID : 860314
Revision : 2
Created on : 2/13/2018
Published on : 2/13/2018
Exists online : False
Views : 427