Hi @alden and thanks for your reply,
First of all I want thank all the your as well since one of the most nice things of coda is your support.
Let´s digg in to the topic itself: I´ve tested the app you created but I guess I must have explained myself better.
For bank reconciliation here is what we need to look at:
We have 3 tables:
- Expenses with the columns: expense date, provider and amount.
- Invoices with the columns: invoice date, customer and amount.
- Bank transactions with the columns: transaction date, transaction information (sometimes it might now have the provider or the customers name) and amount.
When we try to reconcile to need to take a look at the “bank transactions” table so we can search first for the date of the transaction, second for transaction information and third for the amount. Based on those things we could possibly reconcile transactions.
There are some scenarios we need to consider:
- The transaction amount could be a partial payment of an invoice. So the invoice payments will be split in two transactions.
- If its a transfer made by you to a provider in the transaction there could be the expense amount plus the transfer cost.
- Payments are normally made within a month or if you pay with credit card it can be later but the date is something to consider so you can search in the expenses and invoices table the date and the amount.
- We have an expense of the provider “Coda best app, Inc.” in the date 02/26/2019 and the amount of $ 300.
- In our transactions table we have three or four transactions for that amount but two for the amount $ 150 each and the date of this last ones is 27/02/2019 and in the transaction you have “codapp”.
With this information you can tell that the probability of the conciliation is bigger for the two transactions of $ 150, since you´ve the name of the provider and also the date is closer to the expense date.
So the thing will be that in the transactions table we have a column that shows to us the possible invoices (if it´s a positive value) or the the possible expenses (if it´s a negative value) so that when the app does not know the match (since we´re smarter ) to let us decide to which one matches.
Wohoo this is big.
Ok, based on this situation if we solve this out I will certainly say that this is freaking valuable to all the business owners that has to deal with this situations. I know there are services out there that can do this but I actually want to explore coda because I think it could be actually done with it.
Have a great day team.