Duplicate Transactions on Bank Import

C

cppwood

New Member
Hi,

First, great program well done!

I am a MS Money refuge who has just finished importing all of my data into iCash without any trouble and everything is ready to go for my first transaction import (OFX, QFX) from my bank.

The result of the import was plenty of duplicate transactions, i.e., same date, dollar amount, contact.

What I would like to know first is, what criteria does iCash use in order to determine whether or not a transaction is a duplicate? There was about 4 months worth of data in the OFX file hence there was a little work to do to clean it up.

Any explanation of how iCash works in this regard would be appreciated as I am a new user I may have set up my data incorrectly.

Thanks
 
stanbusk

stanbusk

Administrator
Staff member
You should look at your OFX file with a text editor. Each transaction record has a unique reference. iCash store those references preventing duplicates. Is it possible your file has duplicate entries with different references? If I remember well the field is <TRNUID>.
 
C

cppwood

New Member
I believe you're refering to the OFX Fitid field which iCash stores in the tblTransactions.Transaction_ID field, here's an example of what is in my OFX file and there are only 2 for this payee:

<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20091101000000.000[-5:EDT]
<TRNAMT>-162.36
<FITID>20091102210812901727RT10II3061645178773
<NAME>COGECO CABLE SYSTEM IN BURLINGTO
</STMTTRN>

<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20091001000000.000[-5:EDT]
<TRNAMT>-162.36
<FITID>20091002201247859420RT10II2751693544626
<NAME>COGECO CABLE SYSTEM IN BURLINGTO
</STMTTRN>

Note: If I rerun the import iCash will add the same records all over again so after 2 imports for this payee I have a total of 6 entries, the 2 originals plus 4 more, 2 additionals for each one. The Target_Account ends up being Miscellaneous-Expenses instead of Cable-Satellite Television.

This bank will always have historical information as well as any new entries not yet downloaded, like a sliding 3 or 4 month window, you have no option to only pick entries after your last download.

Hopefully I'm clear if not please let me know what you need.

Thanks for your response.
 
stanbusk

stanbusk

Administrator
Staff member
Have you looked at those transactions with the SQL query editor to make sure those transaction IDs are correctly inserted? I have looked at the code and used the example OFX files I have here but I don't see any issue.
 
C

cppwood

New Member
Yes I have, and the first entry of each of the above examples did not have any Transaction_ID entered after the initial import of the data from another finance app. So, after i ran iCash's import the first time it inserted the records along with the appropriate transaction id's.

Now, after running the import again it just did the same thing adding the same records and of course inserting the same transaction id's so yes, the transaction id's are present and accounted for. I did use the sqlquery tool to verify the tran id's were in fact inserted.

Am I to interpret your program logic as follows:

1) check to see if the record iCash attempts to insert has a record already inserted with the same transaction id, if so move on to the next one - i.e, do not insert.

2) if no equivelant transaction id found insert record.

Is that it? If so I can see the first set of records slipping through which would still be wrong because they are in fact duplicates from a pure financial point of view, the same expenses twice. However after the 2nd iCash import you would have the tran id's to compare with so iCash should not insert yet again yes?

The tran id's inserted have 4 trailing spaces at the end of each id so I'm not sure how you guys handle this but they are being inserted with the spaces.

Thanks for your response.
 
stanbusk

stanbusk

Administrator
Staff member
Interesting. Would you mind in opening a support ticket and attach your OFX file (or a simplified version of it) so I can try to reproduce the problem here?
 
You must log in or register to reply here.
Top