We have a database of transactions that is monthly via csv import. This contains information like transaction id, amount, duration.
Most transactions are simple one-time occurrences; however, some transactions happen over the span of months.
For these long duration transactions, we apply a partial payment at day 30, 60, 90, etc. until the final payment which is a remainder of the total transaction. The issue we run into is that the transaction length and amount can adjust part way through.
For example, transaction A has a value of 1,000 and duration of 40 days. Day 30 has passed and day 40 is in the future so our system flags this transaction for partial payment.
Calculating the day 30 partial payment is simple:
Daily rate * 30
(1,000 / 40) * 30 = 750
The issue happens when we import the following month's transaction data. transaction A imports again, but this time it has been adjusted to have a duration of 43 days and value of 1,100.
So a different calculation is needed to reconcile and finalize the transaction:
(Total Transaction Amount - sum of prior payments for transaction A)
(1,100 - 750) = 350
Each time we upload the csv, these long duration transactions have the same transaction #, so it is easy to tie them together in excel via a sumif function, but of course airtable is different.
I've added formulas that determine whether a transaction is ongoing or completed to direct which of the above two formulas to use. Combined with an if statement it looks like this:
IF(
(Transaction is Ongoing or Completed?)="Ongoing",
(Total Transaction Value / Number of Days) * 30,
(Total Transaction Value - sum of prior payments for transaction A)
)
The missing piece I can't figure out is the (sum of prior payments for transaction A). Is there a way to formulate this in airtable?
Apologies for the length and lack of conciseness - have spent too much time in the weeds. Any help or advice is much appreciated!
