Identify Invoices Paid

[LindaG]

I have a lineitems file which contains both invoices and payments. Payments are PA (payment) and Invoices are SA (sale). It also contains the client account number. When viewing this file from my clients via a portal, it is difficult to tell which invoice has been paid. I want to flag invoices as paid if Invoice# and Type PA and Amount = Invoice# and Type SA and Amount.

I assume I need a self join but I'm having trouble separating the PA from the SA. The left should be the SA because there are sometimes several payments against one invoice. Maybe I should have had payments in another file for one to many. Is there an easy way to isolate payments for an invoice and, if the total payments for that invoice equal the invoice amount, flag it as paid? I want to set a field as 1 for paid and 0 if unpaid (I've started auto entry on new invoices with 0 but I have 45,000 unmatched entries.

I don't know how to match them up. I think I would want to, once matched, also flag the payments so that if payments were made that didn't match an invoice amount they would stand out. And so those payments won't try to match somewhere else once matched to a payment. Can I get some suggestions on how to attack this problem? I'm a bit lost. I would sure appreciate it. Thank you.

Linda

[mdpres]

Linda,

You could make a calc field to show Paid,Bal Due or Unpaid. assuming you have a Amount Invoiced Field,Paid Date Field and a Amount Paid Field you could put a calc together somthing like this.

Code:

Case(IsValid(Date Paid Field)and Amount Paid Field = Amount Invoiced Field, "Paid",

IsValid(Date Paid Field)and Amount Paid Field <> Amount Invoiced Field, "Bal Due",

IsEmpty(Date Paid Field)and Amount Paid Field >0 and Amount Invoiced Field = "", "Unpaid"))

This Calc may need a little tweaking but it should give you an idea ot two.

Dean

[RickGrundy]

Linda,

I have a similar setup here. I made a 'Payments' DB and linked it to the Invoices by 'InvoiceID'. Then, create a calc field in the Invoices DB that generates a sum of all related payments for that invoice. If the CalcPayments field >= InvoiceTotal, set the 'Type=PA'.

Hope this gives you an idea. It works great here and allows for multiple payments for a single invoice.

Good luck!

[LindaG]

Hi Dean, thanks for the ideas which would work except that my payment is a different record. I can't figure out how to relate the calculation between two different (or more) records. If the payment was part of the invoice record, that would work great - which I guess I may consider.

Hi Rick, well yes, I think I've come to the conclusion that the payments should be separate. But originally I started with that but then I had trouble consolidating the information into one statement of accounts. I thought I was smart to combine them because the customer statements are now right and their overall balance is correct. But, as you know, I now have trouble reading the statements (as I'm sure my clients do also). You have to view 100 lines and manually match the payments to the invoice to tell which is paid and which isn't and why. Payments also lists the invoice number but it's still hard to 'match up' and read.

I also can't generate aging because I can't flag an invoice as paid. This is major problem. In future, I think I'll flag the invoice as paid when the payment is made but well, I don't know what to do now. I don't know how to correct this mess.

Please someone - isn't there a way to solve this problem? Have the payments invoices and credits etc in one file but have them match up? If not, how can I group them to generate statements if they are all in their own files. I don't think I like accounting at all. I prefer sales, myself.

Linda Gent

[mdpres]

Linda,

Do you have a unique ID for your Invoices & your receivables?

If you do make a relationship between the ID, then you can create a calc field to flag your invoice.

Dean