Complex formulas help

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. 

We just tackled this ourselves this week.  And our solution is as follows.  Sounds very similar

We have Accounts and Invoices.   We setup a "Transactions" table and "Transactions relationships" table.  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.  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.  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.  If this is the route you want to explore, Id be happy to go into more detail.  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.

Hi Michael!

I am still looking to set something like this up!  Would you be willing to give me some additional details?