9 Replies Latest reply on Apr 5, 2011 1:52 PM by brianquillin

    Tracking mulitiple payments for invoices

    brianquillin

      Title

      Tracking mulitiple payments for invoices

      Post

      I need a jump start to this portion of my solution.  I'm building a database for managing an auction for a fundraising event.  It is nearly complete (from a functional standpoint) but I've lost my mind trying to read about other solutions and then figure out how it should be applied to mine.

      Setup so far: Buyers, Items, Invoices (all subtotals and totals working as expected).  There are other components such as Donors and Donation Receipt printing which is all setup and working fine.  (my screen shot will only show the area of my relational table that I'm working on)  I've setup "Invoice Payments", a "join table" to connect my "Invoices" to a newly created "Payments" table. Things you see in the Payments table in the screen shot are experimental (obviously justifying my need to post this)

      What I need to do:  Track multiple payments from a single buyer to their invoice.  Invoices will be created at the end of the event when the bidding has ended.  Therefore, my solution really only needs to account for ONE Invoice for ONE Buyer with MULTIPLE payments (any thoughts here?).

      * Some Buyers will pay in FULL at the event.  For this, I have placed a "payment" field on the invoice but I don't believe that payment field is actually "assigned" to the buyer (it is Invoice::payment).  Meaning it is reflected on the buyer's invoice but not otherwise related to them.  I'd like to modify that so that I can see ALL payments received from a particular buyer at anytime (after the event obviously).

      ** Some Buyers will pay a deposit that night and then mail in their remaining balance later.

      *** Some Buyers will pay nothing at the event but pay in full (or in several payments) later (we hope!)

      I want to use portals to enter new Payments, preferably on the Buyers table.

      My problem:  I just don't know how to get started.  I have found nothing specific that deals with the initial steps of getting this going.  Perhaps I wasn't searching the correct terms or in the right places.

      Screen_shot_2011-04-03_at_8.02.17_PM.png

        • 1. Re: Tracking mulitiple payments for invoices
          philmodjunk

          You've already gotten started by creating a join table for logging payments against an invoice. This may not be needed. The join table makes it possible for a buyer to make one payment to pay off more than one invoice. That doesn't sound like a possibility here, but correct me if I am wrong.

          If a buyer cannot pay off multiple invoices with a single payment, you can link your payments table to the invoice table by InvoiceID. A portal to payments on the Invoice layout can then record all payments made against that invoice. If they pay in full from the start, you just make a single entry in the portal--so don't use a separate field on the invoices layout for recording these.

          If you do want to enable a buyer to pay off multiple invoices with a single payment. Make your portal on the payments layout a portal to the Invoice Payments join table. You then use a drop down on the Invoice_ID field to select invoices to which to apply the payment and enter a partial payment amount in a field defined in this join table. The sum of these portions will equal the total payment made so this is a way to apply $500 to one invoice and $50 to a second invoice for a total payment of $550 dollars.

          • 2. Re: Tracking mulitiple payments for invoices
            brianquillin

            Thanks again.  I think I'll proceed with the Payment Portal on the Invoice Layout.  For this application, there really is no situation that will result in a single payment to payoff multiple invoices.

            For future reference (I do have more solutions to build after this one), can you explain/reword this statement?:

            "Make your portal on the payments layout a portal to the Invoice Payments join table."  

            Do you mean to MOVE a portal FROM the payments layout TO the Invoice Payment layout?  Or did you mean CREATE a new portal on the Invoice Payments layout?  Or is that a portal in a portal (is that possible?)  (That was the only statement I didn't follow).

            I'm still in week 2 of learning FileMaker and this is the 2nd time you've offered great advice.  Much appreciated and very helpful.

            Thanks...

            • 3. Re: Tracking mulitiple payments for invoices
              philmodjunk

              Create a portal. You probably wouldn't try to log payments on the invoice layout in this situation as a payment might apply to more than one invoice. You'd want to enter the total payment and then use the portal to "break it down" over 1 or more invoices.

              Current Filemaker does not support creating a portal inside a portal.

              • 4. Re: Tracking mulitiple payments for invoices
                brianquillin

                Makes sense...

                Thanks again...

                • 5. Re: Tracking mulitiple payments for invoices
                  brianquillin

                  Another question; this may be a "no-brainer" but I have to ask.

                  I had to create another TO for Invoices to connect the Payments Table to Invoices.  When I do, I don't have the ability to use my other tables because those two tables are floating on their own relational island.  Is there a trick or some other method for still having access to all my other tables?  I assumed that my Invoice and Invoice 2 would have the same relational values since they are made from the same table.  

                  • 6. Re: Tracking mulitiple payments for invoices
                    philmodjunk

                    Why did you have to create another TO? You could have simply removed Invoice_Payments, added an InvoiceID field to the Payments table and then made a relationship from Invoice to Payments.

                    The main purpose of TO's is to define relationships which are then referenced throughout FileMaker. The specified TO defines what relationships are in place relative to the current record of the current layout (which refers to one specific TO in Show Records From.) Thus a relationship to Inovice 2 will not produce the same results as your existing relationship. Your layout can only refer to one directly and then any references to the other are controlled by the intervening relationship links.

                    You might want to read this link on table occurrences. If you want to dig deeper, check out the link to yet another article on this concept embedded at the start of this article.

                    Tutorial: What are Table Occurrences?

                    • 7. Re: Tracking mulitiple payments for invoices
                      brianquillin

                      I did delete the Invoice Payments Table but next mistake was trying to put a payment_ID field in the Invoice table (which it suggested that I create an Invoice 2 table - which as you stated did not work as needed).

                      I have now done as you suggested and added an invoice_ID field in the Payments Table and linked it to Invoices::invoice_ID.  It is indicating a one-to-many going from Invoice::invoice_ID---------<Payments::invoice_ID  (I know FileMaker assumes most things correctly but should I try to modify that somehow? - I'm still learning my way through the tangled webs of designing relational tables, so my apologies for the more basic questions).

                      So far, I'm still trying to adjust the relationship setting to allow me to create a payment in the payment portal on my Invoice layout.  (no such luck as I am typing this).

                      I will read the articles you've suggested on TOs.  I tried to add another screen shot of my updated graph but didn't see that option when writing this response.

                      • 8. Re: Tracking mulitiple payments for invoices
                        philmodjunk

                        One to many is the correct relationship here. You can have one invoice, but many payments to pay it off, correct?

                        So far, I'm still trying to adjust the relationship setting to allow me to create a payment in the payment portal

                        In Manage | Database | Relationships,

                        Double click the line between payments and Invoices.

                        Select the check box for "allow creation of records via this relationship" for the Payments side of this relationship.

                        Now you can log a payment in the portal just by typing data into the bottom blank row of the portal.

                        I tried to add another screen shot of my updated graph

                        That limitation in this forum can be irritating, it used to be even worse. You can either click the edit post link on your original post to upload a new screen shot in place of the old or you can upload a screen shot to a file sharing site and post the image to any message you add to this thread.

                        • 9. Re: Tracking mulitiple payments for invoices
                          brianquillin

                          Shortly after writing my last post, everything fell into place (once I choose the allow records on the "Payment" side)...

                          I did read the recommended article on Tables.  Thanks for that link.