Complex formulas help

Question

Formula help
&nbsp;
I am trying to figure out how to apply 1 incoming payment to many invoices.&nbsp; I have 3 tables Clients, Invoices and payments, each client has many invoices. Client usually makes 1 payment which I need to apply to several invoices at one time.&nbsp; 
&nbsp;
Can I set up a checkbox that would trigger a formula to divide the payment equally among all the invoices for that client that have a status of balance due?
&nbsp;
Forn example, Client A has 10 invoices with a status of balance due totaling $10,000.00 and we receive a single payment of $7500.00 which I want to divide among these invoices via my payments table. 
To complicate things, I have another field in my payments table that automatically calculates the write off amount.&nbsp; Then I have a formula field which takes the invoice amount due – payment received – write off amount = $0.00 (all invoices always have to have a zero balance to close them out.
Originally, I had this set up through my Clients table.&nbsp; I had a summary field for all invoices with a balance due and then 2 summary fields – payment amt and write off total from my payments table, which worked fine.&nbsp; However, I have added AR aging to the invoices table and now the payments are not being deducted accurately from the AR since the total AR is calculated in the Clients table.&nbsp; Any ideas on how to tackle this???
&nbsp;
&nbsp;
&nbsp;------------------------------Kelly Lyons------------------------------

chayceduncan · Answer

To start - how many people manage this process? Is it primarily you or do you have a handful of users that support this?&nbsp;

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&nbsp;add&nbsp;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:&nbsp;
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.&nbsp;

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.&nbsp;

------------------------------Chayce Duncan------------------------------

michaelknecht · Answer

We just tackled this ourselves this week.&nbsp; And our solution is as follows.&nbsp; Sounds very similar
We have Accounts and Invoices.&nbsp; &nbsp;We setup a "Transactions" table and "Transactions relationships" table.&nbsp; One to Many Many to One Scenario with Transaction Relationships being the joining table. When creating a Transaction the User also chooses a Related Account.&nbsp; The form will then populate the open invoices related to the Account on the form with the ability to mark the invoices in grid edit as Paid in Full using a checkbox, or enter a Partial Paid Amt using a Currency Field.&nbsp; They save the form which updates the math on the Transaction and then they "Apply the Transaction" which runs a pipeline and creates all the necessary Transaction Relationships to apply the amounts to the invoices and keep the transaction straight.&nbsp; If this is the route you want to explore, Id be happy to go into more detail.&nbsp; Very similar to what Chayce posted, but the form allows us to have more control over what invoices are paid and then a pipeline does the work.------------------------------Michael Knecht------------------------------

kellylyons1 · Answer

Hi Michael,&nbsp;

This is exactly what I am looking for, and would love more details as too building this out.&nbsp; I have only built a a couple of pipelines, so anything you are will to share from your tables/fields setup, forms and relationships to the pipeline steps would be awesome!

------------------------------Kelly Lyons------------------------------

kellylyons1 · Answer

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.&nbsp; 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.&nbsp; The issue is I do not have this set up to apply across all the open invoices for the Clients account.&nbsp; 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??&nbsp; &nbsp;Michael, anything you can share to help me set up this process from start to finish would be so helpful.&nbsp;

Thanks to both of you in advance for the help!
------------------------------Kelly Lyons------------------------------

chayceduncan · Answer

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:&nbsp;

Trigger: New Payment received
Query for Invoices with an open Balance.&nbsp;
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.&nbsp;
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.&nbsp;

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.&nbsp;

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.&nbsp;
------------------------------Chayce Duncan------------------------------

Forum Discussion

Complex formulas help