6 Replies Latest reply on Nov 13, 2013 7:56 PM by jared944

    Creating a reciept from invoices and payments table

    jared944

      Title

      Creating a reciept from invoices and payments table

      Post

           Hello,

           I am looking to create a receipt from a set of 4 tables. I have a Invoice table fro SERVICES, an Invoice table for PRODUCTS, A PAYMENTS table and an ADJUSTMENTS table.

           I began this file with the FM Starter Solutions Invoicing template, but found it didn't have the details I wanted in an invoicing system.

           I deal with two distinct aspects of my job (as an optometrist), I can invoice for services and products. I can see keeping both of those in one table can be problematic. Also, I need to record payments by the patients and insurance companies. If the insurance company only pays a set amount, I need to make an adjustment to set the invoice to "paid."

           I understand that I need to have one table with all of this information in it in a "List" format to be able to create a receipt, but I am stuck on the process. Enclosed is a screenshot of my my table arrangements.

            

           Any help would greatly be appreciated,

           Jared

            

      Screen_Shot_2013-11-13_at_11.32.30_AM.png

        • 1. Re: Creating a reciept from invoices and payments table
          jared944

               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,

               Thanks,

                

               Jared

          • 2. Re: Creating a reciept from invoices and payments table
            philmodjunk
                 

                      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?

            • 3. Re: Creating a reciept from invoices and payments table
              jared944

                   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

                   Final Balance_____________________$0.00___

                   So, I'm stuck on how to incorporate the payments into the Invoice Line Items.

                    

                   Thanks!

                    

                   Jared

                    

                    

              • 4. Re: Creating a reciept from invoices and payments table
                philmodjunk

                     Silly me, you DO have invoice data, I missed that table. That is normally the table from which you would print an invoice.

                • 5. Re: Creating a reciept from invoices and payments table
                  philmodjunk

                       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.

                  • 6. Re: Creating a reciept from invoices and payments table
                    jared944

                         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.

                         Thanks!

                          

                         Jared