Importing credit card transactions [in-depth guide]
FTDIS's | 1. OFX/QFX/QBO import | 2. CSV import | Resources
This guide will assist you in the event that you are not able to auto-download credit card transactions via the OFX connector/Plaid for the workflow designed in the Credit card connector screen.
FTDID's
A few notes about the FTDID that is part of each individual credit card transaction.
1. Each transaction is recognized with a unique ID called FTDID. Workamajig will only allow one unique transaction per FTDID per GL Account. This is to help prevent duplicate transactions from being added.
2. Sometimes the banks change the FTDID, if the charge is still in the process of being finalized on their end, which could end up adding a duplicate transaction in Workamajig as a result, if the FTDID changes.
3. Importing the same OFX file again, or one with an overlapping date range will not import duplicates.
4. If switching To or From the Plaid connection, keep in mind that Plaid has their own set of FTDIDs,s (alphanumeric vs numeric). Which means you could see duplicates appear. But you can check Mark As Cleared on the duplicates to hide them in the Connector screen. Plaid only imports the last 3 months of data, so after that, you can delete those duplicates and they won't re-appear from an active Plaid connection.
Method 1 - .OFX download/import
Most banks/credit cards allow you to download an electronic statement/report of the transactions within a specified date range.
Be sure you have the Credit Card Number entered in the GL Account Setup as this is how the OFX import ensures the transactions get added to the correct account.
When you go to export the statement of transactions, there may be an option to choose a file type. If they do offer multiple file types, look for a file type called OFX, QFX, QBO, or similar, as these are industry-standard financial files.
You can take these files and import them right into the Credit card connector screen so you can process them as if the system was able to download them automatically.
If this works, then you will want to set up a reminder to do this as a daily or weekly manual process.
Method 2 - .CSV download/import
If your bank does not offer any type of formatted OFX file, yet you can download a report of the transactional data, similar to it looking like an excel file where each row of data is a single credit card transaction, then you can take this file and add a header row to resemble the importable fields allowed in Workamajig's connector screen.
- For a detailed list of the importable fields available, please refer to the Import matrix: credit card transactions (credit card connector) [guide].
NOTE: You can manually import this type of file in two places. 1. The Connector screen so you can have users review & process their own transactions which then turns them into charges, or 2. Import them directly as approved & posted credit card charges, skipping the Connector screens workflow.
Once the spreadsheet edits have been completed, use your spreadsheet File>SAVE AS function to save and perhaps rename the file that will be imported. The file should be saved as a .csv (Comma-Separated/delimited Values) file. However, depending on the spreadsheet program there may be options:
Google Sheets> File>Download>Comma Separated Values (.csv)
MS/O365: File>Save As>in the file type dropdown select CSV (Comma Delimited)(*.csv)
Mac MS Office: File>Save As>in the file type dropdown select CSV (MS-DOS) or CSV (WINDOWS)
Then import the CSV file in the Connector screen & process them using the Connector screens workflow.