Complex formulas help
Formula help
I am trying to figure out how to apply 1 incoming payment to many invoices. I have 3 tables Clients, Invoices and payments, each client has many invoices. Client usually m...
Forum Discussion
To start - how many people manage this process? Is it primarily you or do you have a handful of users that support this?
The long and the short is that you'll need more than just a single formula when it's all said and done. If the team of users is small enough - you could tackle this semi manually, and create a table that would handle the 'Invoice Payment' which would act as a join table to solve the problem that your invoice to payment setup is many to many. What it sounds like you're saying and a case I imagine would not be uncommon is that each invoice can have many payments and each payment can be for many invoices. The most prevalent example is that Client A has a few outstanding invoices and the just submit a single payment each month for $N until the total balance regardless of the invoice is paid.
If you go this route - you would set up the Payment and effectively add an 'Invoice Payment' record to this new table that joins the payment to a specific invoice. You can then enter how much of that payment will apply to that specific invoice. Rinse and repeat until the funds are exhausted on the payment. So that would look like:
INVOICES
Invoice 1 = $1000
Invoice 2 = $2000
PAYMENTS
Payment 1 = $1500
Payment 2 = $1500
INVOICE PAYMENTS
Invoice 1 - Payment 1 - $1000
Invoice 2 - Payment 1 - $500
Invoice 2 - Payment 2 - $1500
What this would allow you to do it itemize what collection of payments actually paid off the balance of an invoice, and also tell which invoices a specific payment would have been applied to. Using the relationships you could then derive how much is outstanding on each invoice, the last time it was paid/partially paid, compare that to what you want to write off on that specific invoice etc.
IF you wanted something more automatic - I would still suggest that you still create the intermediary/join table for 'Invoice Payments' so you can still track the above and itemize this way - but when a new payment is received you could have a Pipeline run the query for all of your unpaid invoices and creates the records for you, essentially trying to allocate the funds automatically until the payment funds are exhausted.
------------------------------
Chayce Duncan
------------------------------
Hi Chayce and Michael,
I think we are all on the same page as to what I need to accomplish, I just need help with the set up and execution of the table and pipeline.
There is only a couple users at this time that would be entering transactions.
I believe the "transactions" joining table is the best route to go. I would like this process to be as automated as possible to eliminate entry/user errors.
I do have an incoming payments table that I am using to enter all my payments and that calculates the math for me. The issue is I do not have this set up to apply across all the open invoices for the Clients account. Would I be able to use this table as the "transactions table"? I think all I would need to do is modify a few fields and build a pipeline to apply over all open invoices right?? Michael, anything you can share to help me set up this process from start to finish would be so helpful.
Thanks to both of you in advance for the help!
------------------------------
Kelly Lyons
------------------------------I will defer to Michael to provide more specifics about his particular implementation / feedback. Based on similar use cases and experience, high level the general setup would be a Pipeline for when a new payment is received that would do something like the following:
Trigger: New Payment received
Query for Invoices with an open Balance.
Loop each Invoice
For each invoice - determine if you have enough funds on the payment to pay off the balance of that invoice. If there is enough, create a transaction between the payment and invoice for the amount outstanding on the invoice as the $ amount of the transaction. Move onto the next invoice and compare the outstanding amount to the payment amount less prior transactions. Repeat the same thing until you can no longer meet the balance of the invoice and you'll end the Pipeline by making a final transaction to an invoice with whatever is left on the payment.
If the final transaction isn't enough to pay the invoice, then you can summarize the total transaction $ that were applied and that brings down the outstanding balance on the invoice.
Your actual transaction table would have 2 relationships, one to invoice and one to payments. Invoices and Payments are both parents to the transaction. The only other field you need is an entry field for Amount to log the actual $ amount.
The above is just based on a generic suggestion of how to handle it. As you have the client aspect and as compared to how you're currently doing you may need to adjust accordingly or incorporate specifics from Michaels suggestions.
------------------------------
Chayce Duncan
------------------------------
