We are sourcing Plaid transactions which contain the following logic for negative/positive markers on the 'amount' field (from Plaid docs):
The settled value of the transaction, denominated in the transactions's currency, as stated in iso_currency_code or unofficial_currency_code. Positive values when money moves out of the account; negative values when money moves in. For example, debit card purchases are positive; credit card payments, direct deposits, and refunds are negative.
Part of this logic is giving us trouble, which is the fact that when money moves into a Checking account, it is represented as a negative amount.
A main use case of our app is to sum up transaction values to provide an aggregated view of revenue/spend in a given month (like a personal finance app).
We would also potentially ideally show these as 'Spent' and 'Received' columns on the front-end, as Quickbooks does, rather than just one column (w/ sometimes unintuitive negative and positive markers) in a Transactions table.
So the questions are:
- How can I best convert the values Plaid gives us into what we need in the DB? Is there any way to do this that's more efficient than a For loop on each transaction that looks at certain criteria and then makes an update to the DB? E.g., Loop through transactions and where amt = a negative amount AND account type = Checking, update another field for this item with a value that makes more sense.
- Should this happen as soon as transactions are fetched from the Plaid API (we're planning to do this in a batch process), or when we Query All Records? Any way particularly better for performance?
Thanks for any guidance you can provide, understand this is a bit open-ended.