1 2 Previous Next 22 Replies Latest reply on Mar 22, 2013 8:24 PM by mickwilli

    Filemaker Invoicing and Accounts Receivables

    mickwilli

      Title

      Filemaker Invoicing and Accounts Receivables

      Post

      Hi all,

      I've been trying to think of a way to do this and to do it tidily and I'm coming up blank.

      What I want to do, is to have invoices in Filemaker, which will link to out other systems in the same database. I have the Invoices setup in Filemaker with a related table for invoice line items, so that all works fine.

      Where I come up blank is when it comes to processing payments against invoices. Currently we use an MYOB accouting package for our invoices and Accounts Recievable, but we want to reduce the admin work and double entry hence the move to recreating this in Filemaker. Currently when we process a payment for a client, we're taken to a screen that shows all the outstanding invoices for that particualr client. You then enter the details of the total payment, the payment date, etc. MYOB then automatically attributes the payment against the oldest invoices first until it uses the total amount. If there's an overpayment, it creates a credit.

      Does anyone have any ideas on how to make such a system in Filemaker? I can make a screen to show all the outstanding invoices for a client by going to the record of the client and then using a portal that has the records filtered to only show invoices that are unpaid, but I'm not sure where to go from here.

      Any help or ideas appreciated.

        • 1. Re: Filemaker Invoicing and Accounts Receivables
          philmodjunk

          Years ago, there used to be a third party product that would serve as a "bridge" between MYOB and FileMaker. Don't know if such is available today, but if it is, it might be a quicker way to avoid the double entry tasks than developing your own accounting package within FileMaker.

          Linking payments to invoices is a many to many relationship. Several payments might pay off one invoice and one payment might pay off more than one invoice.

          You need a table for Payments where you log each actual payment amount and a join table where you link that payment to one or more invoices and log the exact portaion of the payment applied to that invoice.

          Invoices----<Invoice_Payment>------Payments

          Invoices::InvoiceID = Invoice_Payment::InvoiceID
          Payments::PaymentID = Invoice_Payment::PaymentID

          In terms of relationships, Invoice_Payment is linked to Invoices much like your line items table, but then links to payments records instead of products.

          Scripts can take the payment amount and apply it to a set of unpaid Invoice records for a specified client, starting with the oldest invoice that has an unpaid balance.

          • 2. Re: Filemaker Invoicing and Accounts Receivables
            Jade

            This thread on FM Technet describing how others have implemented data export/import with MYOB may be of help:

            https://fmdev.filemaker.com/thread/64376

            • 3. Re: Filemaker Invoicing and Accounts Receivables
              mickwilli

              Hi PhilModJunk,

              Thanks for that reply. I'll have a crack at those relationships later on when I get a chance, I think I follow what you mean though. What would be the other fileds that we'd require in the payments and invoice payments tables?

              Would you have an example of what the related script might look like? That might just help me in understanding exactally how the relationships interact for the sake of applying payments etc.

              • 4. Re: Filemaker Invoicing and Accounts Receivables
                philmodjunk

                I can list the basic fields for both tables, but as you adapt it to meet the needs of your specific business practices, you can always add more fields if needed.

                Payments:

                __pkPaymentID
                DateReceived
                PaymentAmount
                _fkCustomerID
                PaymentMethod (Cash, CreditCard, Check, etc.)

                Invoice_Payment:

                _fkPaymentID
                _fkInvoiceID
                PaymentPortion

                The scripts used would depend on the needs of your business and the specific interface design. Do you mean the script to take a payment an automatically pay off a customer's unpaid invoices?

                You'll need an additional relationship for it:

                Payments::_fkCustomerID = Invoices::_fkCustomerID

                You can use this to find all invoices for a given customer and then use constrain found set to filter it down to just those that have an unpaid balance. And then you sort by __pkInvoiceID in ascending order, go to the first invoice record and start paying off invoices.

                I have to go fire up the BBQ for the 4th now. I'll check back tomorrow and put up a sample script if no one else puts one up first.

                • 5. Re: Filemaker Invoicing and Accounts Receivables
                  mickwilli

                  Hi PhilModJunk,

                  Thanks for that, I think I'm with you now.

                  If you had a chance to throw together a sample script that'd be great, otherwise I'll have a bash at it tonight.

                  Enjoy your BBQ, it's not exactally BBQ weather over here at the moment!

                  • 6. Re: Filemaker Invoicing and Accounts Receivables
                    philmodjunk

                    Note:

                    Payments::_fkCustomerID = Invoices::_fkCustomerID

                    will require a new occurrence of the Invoices table, so it might look more like this:

                    Payments::_fkCustomerID = PaymentsINVOICES::_fkCustomerID

                    To create this new occurrence, you select Invoices in the relationship graph and then click the duplicate button (two green plus signs).

                    #This script would be run from the Payments layout
                    If [ Not IsEmpty ( paymentsINVOICES::_fkCustomerID ) // make sure invoice records exist for selected customer]
                       Freeze Window
                       Set Variable [$PaymentID ; Payments::__pkPaymentID]
                       Set Variable [$UnassignedPmt ; Payments::Amount ]
                       Go To Related Records [ show only related records ; table: paymentsINVOICES ; layout: Invoices ( Invoices ) ]
                       Enter Find Mode []
                       Set Field [Invoices::UnpaidBalance ; ">0"]
                       Set ErrorCapture [on]
                       Constrain Found set []
                       IF [ Not Get ( FoundCount ) ]
                           Show Custom Dialog ["No unpaid invoices for this customer were found."]
                           Go to layout [original layout]
                       Else
                           Sort Records [Restore ; no dialog ] // sort by ascending order to put oldest invoice first.
                           Go to record/request/page [first]
                           Loop
                              Set Variable [$InvoiceID ; value: Invoices::__pkInvoiceID ]
                              Set Variable [$Unpaid ; value: Invoices::UnpaidBalance ]
                              Go to Layout [Invoice_Payment]
                              New Record/Request
                              Set Field [Invoice_Payment::_fkPaymentID ; $PaymentID ]
                              Set Field [Invoice_Payment::_fkInvoiceID ; $InvoiceID ]
                              Set Field [Invoice_Payment::PaymentPortion ; Min ( $Unpaid ; $UnassignedPmt ) ]
                              Set Variable [ $UnassignedPmt ; value: $UnassignedPmt - Invoice_Payment::PaymentPortion ]
                              Go To Layout [ Invoices ]
                              Exit Loop if [ $UnassignedPmt < 0 ]
                              Go to Record/Request/Page [ Next ; Exit after Last ]
                         End Loop
                         Go to Layout [original Layout]
                    Else
                       Show Custom Dialog ["No Invoices exist for this customer."]
                    End If

                    • 7. Re: Filemaker Invoicing and Accounts Receivables
                      mickwilli

                      Hi PhilModJunk,

                      Thanks for your continued assistance.

                      I think I've done what you've intended, apart from the script. Attached is a screenshot of the relationship graph.

                      I've also created a layout in the Payments Table with a portal that shows related invoice records (so invoices for the related customer) that is filtered to invoices that have money outstanding. That works ok. On the portal lines is also the Payment Portion field of the invoice Payments table. The problem I have with this layout is that I have no way of automatically creating a related record as it's not a direct relationship of the payments table. So I can't modify the Payment Portion field in this layout.

                      I haven't tried putting that script together yet, I'm keen to try and get this layout working manually first.

                      Have you any ideas how to get this working? I've spent about an hour or so mucking about with things and trying to get it working, but I've come up blank again. It is possible that I might be going about it the worng way and doing it this way is never going to work.

                      Your assistance is much appreciated.

                      • 8. Re: Filemaker Invoicing and Accounts Receivables
                        philmodjunk

                        I've also created a layout in the Payments Table with a portal that shows related invoice records (so invoices for the related customer) that is filtered to invoices that have money outstanding. That works ok. On the portal lines is also the Payment Portion field of the invoice Payments table.

                        And is this a portal to Invoice 2? Adding Payment Portion to this portal will be a problem. Not only is there no direct link between invoices 2 and Invoice_payment--which means you won't see the expected totals in this field, there can be more than one payment portion for any given invoice should a payment result in partial payment towards that invoice.

                        I suggest two portals. One to Invoices 2 to get your list of unpaid invoices for the current customer and one to Invoice_Payment to list the payment portions linked to that total payment. A button in the invoices 2 portal can perform a script that assigns a payment to that invoice. The script can put either the total unassigned funds from the payment or the unpaid balance for that invoice--whichever is smaller into the payment portion field. This is a simpler script to create than the one I've posted and provides more control over which invoice is paid.

                        • 9. Re: Filemaker Invoicing and Accounts Receivables
                          mickwilli

                          Hi PhilModJunk,

                          Yes, the portal on the payments layout is a portal to Invoices 2, as I couldn't come up with a way of relating it back to either the customer or the invoices to display only the unpaid invoices for a particular customer. Do you have a way to have the payment portion field on the invoices portal and have that working? This is ultimatly what I'd like to achieve.

                          I think I follow your drift with the script, I'll have a bash at putting that together myself later on, but I'd be interested to know how you intended this script in looking, just so that I'm on the same page.

                          • 10. Re: Filemaker Invoicing and Accounts Receivables
                            philmodjunk

                            My point is that the placing the PaymentPortion field in this portal can't work for all possible payment situations. How would you display 2, 3 or more different values in that one single field?

                            Example:

                            Say you process a payment of $200 to apply it to an Invoice with a total of $211. Perhaps the client made a mistake in filling out the amount on a check or something. This results in an Invoice_Payment record with a matching Invoice ID and a payment portion of $200. Then you get the customer's payment for another invoice and he's made it for the amount of that invoice plus the $11 outstanding on the other invoice. This results in a second Invoice_Payment record with the same InvoiceID but a paymentPortion of $11.

                            In a table view of Invoice_Payment, the data might look like this:

                            InvoiceID      PaymentPortion     PaymentID
                            23                    $200                            2
                            23                    $11                              3
                            24                     $300                           3

                            So with your portal to the Invoices 2 table occurrence, what number do you put in the paymentPortion field for InvoiceID = 23? $200 or $11? What you can do is show the total of all paymentPortions for that invoice: $211.

                            To do that you add a calculation field to Invoices defined as Sum ( Invoice_Payment::PaymentPortion ) define to evaluate from the context of Invoices. This gives you the total paid against each invoice, but isn't a field you can use to enter a new payment portion--which is why I suggested a second portal that lists all Invoice_Payment records for the current Payment.

                            • 11. Re: Filemaker Invoicing and Accounts Receivables
                              mickwilli

                              Ah yes, I see what you mean. I guess I was hoping for the invoice payment record to be for both the invoice record and the payment record.

                              I guess what I'm trying to achieve is a layout where you can see all the open invoices for a client, enter details of the overall payment (Payment total, payment date and payment method) and then have the system automatically attribute the payment to the oldest invoices first and then stopping when it runs out of money. The user then needs to be able to override what the system has automatically attributed to each invoice by removing or adjusting the money paid to a particualr invoice and then putting the ballance onto another invoice.

                              Is there a way of doing that, maybe in list view rather then using a portal or is it just not going to work that way?

                              • 12. Re: Filemaker Invoicing and Accounts Receivables
                                philmodjunk

                                The script I posted does the automatic portion. The need to do the rest of what you have described here is why I am suggesting two portals, one to Invoices 2 and one to Invoice_Payment. You use the portal to Invoice_Payment for changing how a payment is apportioned to different invoices. The portal to Invoices lets you see a list of all invoices for the current customer that have not been fully paid.

                                • 13. Re: Filemaker Invoicing and Accounts Receivables
                                  mickwilli

                                  Hi PhilModJunk,

                                  I've worked out a way to get the layout to work as I want.

                                  What I've done is to return the relationships back to the basics of Payments -> Invoice Payments -> Invoices. In the payments layout there is a portal that shows related records from the Invoice Payments table. In the portal rows are the feilds from the invoice (i.e. invoice total, invoice date, invoice number etc). Now the way that this works is that the intention is that when you load into this layout you've run a script first. In this script you specify the customer that you wish to process a payment for. The script then finds all the outstanding invoices for that client and creates an invoice payment record for them with a zero payment portion. This then shows the invoices in the portal as they are related by the Invoice Payments records that have been created. From there all you have to do is attribute the payment portions to the invocies and then you're done.

                                  I've tested this theory by manually creating and setting the records and it works as expected. What I need some help with now, is writing the script to create these records to create the initial relationship. How do I go about creating the related records in the script? I can easily create the invoice payments records and set the payment ID, but I'm not sure how to create records with each of the invoice numbers in the invoice payments table.

                                  Have you any ideas on how best to do this?

                                  • 14. Re: Filemaker Invoicing and Accounts Receivables
                                    philmodjunk

                                    One of the interesting parts of database design is adapting a general database solution to the specific "business rules" of a particular organization. Am I correct that you want to generate an invoice payment record for each unpaid invoice for a given customer?

                                    What do you have in invoices at this time to show that the invoice is or is not paid in full?

                                    1 2 Previous Next