Ok, Ive simplified my table structure a bit. I now have one INVOICES table and one PAYMENTS table. My question is - how can I add this information to one table so I can print a receipt using the List view?
Any help would greatly be appreciated,
I can see keeping both of those in one table can be problematic.
Actually, I would keep them in one table and select them for an invoice in the same related LineItems table--the one key table that you do not seem to have here.
I understand that I need to have one table with all of this information in it in a "List" format
It is a very good way to go when it works. That approach isn't absolutely necessary and often is not possible, but what you are referring to is the LineITems table (called InvoiceData in the filemaker 12 starter solution) that you don't seem to have here.
Also, won't you sometimes have a customer making a single payment that pays for more than one invoice? How do you plan to handle that situation with your database?
Thanks for the reply.
I found a way to use a separate Services and Products and link that to the line items table. This table is the "Invoice Data" to the right of my lower screen capture.
Its quite possible that a customer may make a payment on a few different invoices, but it doesn't happen too often. My invoice would usually be as follows :
Comprehensive Eye Exam 92004 $90.00
Contact Lens Fitting 92310 $80.00
Marchon Yellow Frame SN23452 $180.00
Progressive Lenses X12345 $120.00
AR Coating 3456 $30.00
Copayment - $20.00
Amount Billed to BCBS $480.00
~ Of course the insurance company will not pay for the total bill, so I have to make an adjustment for the invoice to show as being "paid" ~
BCBS Payment - $300.00
Insurance Adjustment - $180.00
So, I'm stuck on how to incorporate the payments into the Invoice Line Items.
Silly me, you DO have invoice data, I missed that table. That is normally the table from which you would print an invoice.
Your payments can be displayed in a portal on your layout, but you need a relationship that links payments to invoices and that's where a single payment for multiple invoices adds a level of complication to this. Adding a join table between payments and invoices is how this type of payment is usually managed.
Excellent. I never thought to add a payments portal to the trailing grand summary part of the receipt layout. You can have a portal with 20+ lines and all you need to do is select "Slide up based on all objects above" and "auto resize enclosing part." This turns out to be an elegant solution.
Reference http://forums.filemaker.com/posts/d63dadd4c7?commentId=137892#137892 for PhilModJunk's more detailed info about adding a join table between invoices and payments.