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
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
------------------------------