Time for an evening confession. I hear it's good for the soul.
I had a client call with a problem running a VI job. It was set up to read data from a CSV file and add adjustment transactions to Check, Deposit and Adjustment Entry in Bank Reconciliation. The job was set up and put in place some 5 months ago. Low and behold, they have been running it 2 to 3 times each month but he is just now getting around to reconciling cash. Yes, 5 months later.
What he found was a number (think 30% of all deposits imported and they import about 750 deposits a month) of their deposit entries couldn't found. When I ran the import in a test company, the job said it imported 341 entries. It should have been 342. When I exported the BR_Transactions table to Excel, I only found 270 entries.
The lesson reinforced / remembered, is that one must be careful to ensure that a unique key is created when importing rows into a table. In BR_Transaction, the primary index to the table includes Bank Code, Transaction type, Check Number, Check Sequence Number, Transaction Date, and Entry Number. In our import, the bank code and transaction type are assigned so they are always the same. These are deposits so there is no Check Number of Sequence. That leaves transaction date and entry number. Some of the deposits had the same entry number for the same date. Where there were duplicates, only the last row imported survived.
The fix was easy, we moved the number that was going into the entry number column over to the Reference No column and let the system assign an incremental value to Entry Number.
I learn a lot from this forum every day but I guess I decided that I had to learn this lesson the hard way. I thought I would share to possibly save someone else in the future. I'm not sure Custer would have listened had someone told him that there was a whole mess of pissed off Indians over the next hill. However, he surely would have been better off if he had!