marination
commented
9 months ago @barredterra reference_number is a good metric to check similarity here right ? Description could be the same if its vague among many unrelated transactions. But reference number is unique to a transaction (stays the same through all the events).
barredterra
Problem
I noticed that, for bank drafts, we get duplicate transactions. For example, the same Bank Transaction got created four times within the course of a week. The first three ones have Status "Pending" and no Transaction ID set. The final one has Status "Settled" and a Transaction ID.
Example transaction data
```json [ { "allocated_amount": 0.0, "amended_from": null, "bank_account": "GiroFirm (Consulting GmbH) - Sparkasse Hamburg", "bank_party_account_number": null, "bank_party_iban": "DE02200505501015871393", "bank_party_name": "Hauptk.f.FK", "company": "Consulting GmbH", "creation": "2023-09-14 00:01:54.563374", "currency": "EUR", "date": "2023-09-15", "deposit": 0.0, "description": "STEUERNR 123/456/78910 UMS.ST JUL.23 1.234,56EUR, 123/456/78910-----L1234567891234567", "docstatus": 1, "doctype": "Bank Transaction", "idx": 0, "kosma_party_name": null, "modified": "2023-09-19 15:01:57.230528", "modified_by": "mail@example.com", "name": "ACC-BTN-2023-00263", "naming_series": "ACC-BTN-.YYYY.-", "owner": "Administrator", "party": null, "party_type": null, "payment_entries": [], "reference_number": "123/456/78910-----L1234567891234567", "status": "Pending", // <----- "transaction_id": null, // <----- "transaction_type": null, "unallocated_amount": 1234.56, "withdrawal": 1234.56 }, { "allocated_amount": 0.0, "amended_from": null, "bank_account": "GiroFirm (Consulting GmbH) - Sparkasse Hamburg", "bank_party_account_number": null, "bank_party_iban": "DE02200505501015871393", "bank_party_name": "Hauptk.f.FK", "company": "Consulting GmbH", "creation": "2023-09-15 00:02:30.530476", "currency": "EUR", "date": "2023-09-15", "deposit": 0.0, "description": "STEUERNR 123/456/78910 UMS.ST JUL.23 1.234,56EUR, 123/456/78910-----L1234567891234567", "docstatus": 1, "doctype": "Bank Transaction", "idx": 0, "kosma_party_name": null, "modified": "2023-09-19 15:01:57.198979", "modified_by": "mail@example.com", "name": "ACC-BTN-2023-00265", "naming_series": "ACC-BTN-.YYYY.-", "owner": "Administrator", "party": null, "party_type": null, "payment_entries": [], "reference_number": "123/456/78910-----L1234567891234567", "status": "Pending", // <----- "transaction_id": null, // <----- "transaction_type": null, "unallocated_amount": 1234.56, "withdrawal": 1234.56 }, { "allocated_amount": 0, "amended_from": null, "bank_account": "GiroFirm (Consulting GmbH) - Sparkasse Hamburg", "bank_party_account_number": null, "bank_party_iban": "DE02200505501015871393", "bank_party_name": "Hauptk.f.FK", "company": "Consulting GmbH", "creation": "2023-09-16 00:01:19.135758", "currency": "EUR", "date": "2023-09-15", "description": "STEUERNR 123/456/78910 UMS.ST JUL.23 1.234,56EUR, 123/456/78910-----L1234567891234567", "doctype": "Bank Transaction", "deposit": 0, "docstatus": 1, "idx": 0, "kosma_party_name": null, "modified": "2023-09-16 00:01:19.157301", "modified_by": "Administrator", "name": "ACC-BTN-2023-00272", "naming_series": "ACC-BTN-.YYYY.-", "owner": "Administrator", "party": null, "party_type": null, "payment_entries": [], "reference_number": "123/456/78910-----L1234567891234567", "status": "Settled", // <----- "transaction_id": "fcc9c88165b042e10f273dd8690b6a5b", // <----- "transaction_type": null, "unallocated_amount": 1234.56, "withdrawal": 1234.56 } ] ```Cause
I guess this happens because the bank draft is announced, updated and finalized and we receive each of these events.
Proposed Solution
To prevent duplicates, I'd suggest that we stop syncing "Pending" transactions. A more complicated solution would be to find a way of identifying that they are the same (other than the Transaction ID ) and continue updating them.
Workaround
Before reconciling, filter your Bank Transactions by Status = Pending and Transaction ID Is Not Set and cancel these.