xTuple.com xTupleU Blog & News Customer Support

Bank Reconciliation Error After Upgrade


We recently upgraded our xTuple version from 3.8.3 to 4.4.0. After this upgrade, we noticed that the bank reconciliation portion of the app is not working as expected. When we navigate to Accounting -> Bank Reconciliation -> Reconcile…, we receive the following SQL error in the log:

Tue Jul 3 12:45:01 2018 ERROR: column “bankrecitem_effdate” does not exist
LINE 5: COALESCE(bankrecitem_effdate, gltrans_date) AS f_date…

When we try to manually add the bankrecitem_effdate column into the public.bankrecitem table, the bank reconciliation functionality seems to work. However, with this also comes a huge performance hit. When we navigate to Accounting -> Bank Reconciliation -> Reconcile… again, the query to open the Reconcile Bank Account menu takes anywhere from a minute to a minute and a half to open. Any actions in this menu also take a similar amount of time to carry out. I’m just wondering if there is something I could be missing when adding this missing column in? Or if there could be more that I need to add/change in the database rather than just adding this column.

Thanks in advance!


There are two things wrong here:

  • The bankrecitem_effdate column wasn’t added to the bankrecitem table until v4.5.0Beta. No functions or queries should be trying to COALESCE() it. Something is out of sync.
  • Simply adding a column shouldn’t affect performance like that.

With a couple more lines of context we’ll know where that COALESCE() call is coming from. Simply using the v4.4.0 version of the offending function or query should fix both problems. If you need that new column and code, in spite of using an earlier version of the application and database, check how the column is being used. That how will help determine the right approach to dealing with the performance problem.


I did try to check the source files for the 4.4.0 xTuple client that we are using, perhaps to see if there was a problem with this particular module, but i could only find source files up to version 4.3.0 out on xtuple.org and github. Perhaps there is something wrong with the 4.4.0 client we are using. I did try to re-download the 4.4.0 client from xtuple.org just in case, but was met with the same results.

Here is the error window we see whenever we even try to open the Bank Reconciliation Menu:

I did notice a performance improvement on that form in my test environment (which has that bankrecitem_effdate added manually) once I used some of the metasql statements for bank reconciliation functions from the 4.5.0 release: https://github.com/xtuple/xtuple/tree/4_5_x/foundation-database/public/tables/metasql

While I know the best way to go from here is to upgrade (were still a little ways off preparation wise for this step), it makes me curious if anyone else has run into this before.