4 Replies Latest reply on May 27, 2014 9:29 AM by philmodjunk

    Question about creating a database that track unpaid bills and...

    leonhart1981_1

      Title

      Question about creating a database that track unpaid bills and...

      Post

           I have created a database for my venders which contains the following fields: 

                                                                                                                                                                                                                             
                          Bill                     ETD                     ETA                     Destination                     Vender
                          Vender Invoice                     Discount %                     Discount Amount                     Subtotal                     Grand Total

            

           Next, I would like to create another database called "Payment" which will contain the following fields: 

            

                                                                                                                                                                                   
                          Date                     From
                          Beneficiary                     Paid Bill
                          Bill Amount                     Total Amount

            

           What should I do so when I run a report I can easily see what bills still remained unpaid, and their balance if I make a partial payments for certain bills? Like QuickBooks; when making payment from QB, after select a payee, it will list the unpaid bills and allow me to enter the amount that I want to pay for each bills. 

           I am wondering if FMP can do it or similar task?  Please help~

        • 1. Re: Question about creating a database that track unpaid bills and...
          TKnTexas

               You should have a field on the invoice of Date Paid, Amount Paid, or both or just a flag as Paid.  For the internal working of LINKING a KEY FIELD on the payment would need to be noted on the INVOICE record to tie the invoice to the payment.  

               Doing a search of your PAID field, for being blank will show those to select for payment.

          • 2. Re: Question about creating a database that track unpaid bills and...
            leonhart1981_1

                 Dear TKnTexas, 

                 Are you talking about my current Database for vender or the new "Payment" database that I'm going to create? 

                 If it is the "Payment" database, I have the filed of Amount paid as Bill Amount, and the Date is the payment date. 

                 I don't quite understand your instruction? 

                 What if a single transaction contains multiple bills payment? How do I distinguish them? Can I enter multiple bill number in a single field? 

            • 3. Re: Question about creating a database that track unpaid bills and...
              TKnTexas

                   A table for Vendors.  A table of Invoices.  The invoice table will contain one or more invoices that are to the same vendor awaiting payment.  In addition to the fields you noted, you need a field to note if it is paid or not.  That could be a Payment Date (blank until paid) or it could be a CheckNumber from the Payment file.   Something that by its absence is unpaid.  
                    

                   Below is a quick mock-up in a spreadsheet with basic fields.  More can be added to meet you needs.  There are two invoices with a blank CkNumber field.  These are unpaid.  A search for "" in this field will display these.  The check number notation can be a link to the payment table.  
                    

              • 4. Re: Question about creating a database that track unpaid bills and...
                philmodjunk

                     Is it possible that one payment might be used to pay off more than one invoice?

                     Is it possible that one payment might pay off a portion of what is owed on a single invoice?

                     If you answer yes to either question, if there is the slightest chance that you might answer yes either question (and remember that mistakes can be made by the person issuing the checks....) then your system has to become a bit more sophisticated to manage those details.