1 2 Previous Next 29 Replies Latest reply on Sep 19, 2011 3:21 PM by philmodjunk

    Invoice Payment Solution

    jlhsolutions

      Title

      Invoice Payment Solution

      Post

      I am currently working on adding a payment tracker to my invoicing solution. I started with the FM StartingPoint database and now I am trying to add in a way to enter payments and mark invoices as PAID. I have done some research and have gotten a few ideas, but I am struggling on how to implement this.

      First, all invoices are tired to an ACCOUNT. So I am not sure if I should have the Payments be applied to the account or the invoice. I want to be able to have each invoice marked as PAID when a payment is recieved. Sometimes payment is for the full amount of the invoice, sometimes it covers a few outstanding invoices. I suppose there could be a time when it will cover a full invoice and only a partial invoice.

      So I am trying to get something set up that I can enter in a payment and have it apply the amount to the oldest invoice and mark it as paid. Then take the remaining amount and apply it towards the next oldest invoice and keep working up the list. Maybe I need to just have the payment applied to the account and then have something that determines if the invoice was paid and if so mark it as paid.

      Any help or suggestions are greatly appreciated.

        • 1. Re: Invoice Payment Solution
          philmodjunk

          If you establish a table linked by accountID to account, a running total summary field of all invoice amounts for the same account could be compared to total payments. If the total payments is greater than or equal to the running invoice total, the invoice is paid.

          • 2. Re: Invoice Payment Solution
            jlhsolutions

            Phil,

            After more thinking and researching, I think what I should do is a a Payment Ledger table and have each payment stored in there. Then each payment gets applied to an invoice or a couple of them. I would say that most of the time, payment will be for a full invoice or a couple of invoices. I had noticed that you had mentioned to someone else about the partial payments and I suppose it could happen.

            What are thoughts? It seems like you have quite a bit of expertise in this area.

            • 3. Re: Invoice Payment Solution
              philmodjunk

              If you've read my earlier threads, what I described was a system that allowed the user (or a script) to designate portions of a single payment to different Invoices.

              What I described implemented a many to many relationship between a payments and invoices with payment portions recorded in the join table. Whether this is the best approach depends on your business procedures.

              • 4. Re: Invoice Payment Solution
                jlhsolutions

                Yep, I have been reading them and working on getting my tables and relationships to work. My business is still in the early stages so I don't have really any set procedures yet.

                I have my INVOICES table, INVOICE_PAYMENTS table, and PAYMENT_LEDGER table. Now I am trying to get the relationships.

                These are the fields I have in the tables.

                INVOICE_PAYMENTS table: id_invoice - text,  id_payment - text,  Payment_Portion - number

                INVOICE table : ID_Invoice - text, Payment_Sum - calculation (Invoice_Payment::Payment_Portion

                PAYMENT_LEDGER: ID_Payment

                I created a relationship between INVOICE::ID_Invoice <--->INVOICE_PAYMENTS::id_invoice. Also made INVOICE_PAYMENTS::id_payment <--->PAYMENT_LEDGER:ID_Payment. I am not sure if I should allow the creation or deletion of records here.

                You mentioned above about using a script and that is my idea, once I get the backend in place. I would like to have it as automated as possible. I would like to be able to select the ACCOUNT and then apply the payment and have it link up to the invoice and mark it paid if its paid in full, or apply the payment to the invoice total.

                 

                • 5. Re: Invoice Payment Solution
                  philmodjunk

                  id_invoice and id_payment are better defined as number, not text, though it may make no difference in your solution in most cases, if you ever sort on this field, you'll get very different results if this is a number field.

                  Payment_Sum should be defined as Sum ( Payment_Sum::Payment_Portion )

                  Payment_Ledger will need more fields, at least Payment Amount and Date. Summary fields are useful in this table as well. Since you want to establish an account for each customer, you may also want to set up an Account table and add an AccountID field to the ledger table. This way you can perform a find for all records of a given account. (on the other hand, the account ID can be replaced by a CustomerID if you define a customer table, You don't really need both an Account and a Customer table.)

                  The "Allow creation" and "delete" options are useful for the Invoice_Payments side of the relationships. Do not, under any circumstances enable the delete option for the Payment_Ledger or Invoices sides of these two relationships or deleting a record in the join table will automatically delete the matching invoice and/or payment ledger record. "Allow creation..." makes it easier to add new records in a portal to the join table.

                  On your Payment_Ledger, you can select a client or account ID in a drop down list or pop up menu, then enter an amount. Clicking a button could then run a script that finds all unpaid invoices, sorts them by date and uses the Invoice amounts to create records in The Invoice_Payment table until either all portions of the payment are assigned to invoices or there are no more invoices to pay off. That might happen if a customer accidentally overpays.

                   

                  • 6. Re: Invoice Payment Solution
                    jlhsolutions

                    You mentioned I should add in ID_Account.  I am assuming I add this to the PAYMENT_LEDGER table as I don't think I need it in the join table. When you are talking about ID_Account, are you talking about the one that auto-serializes my records?I added another TO of my PAYMENT_LEDGER and linked the ID_Account fields together.

                    Since I started with the FM StartingPoint DB, it has Accounts and Contacts, so I will stick with the Accounts as it would be a lot of to change it. 

                    Thanks for clearing up the "ALLOW CREATION" and "DELETE". I have the ALLOW and DELETE options checked on the INVOICE_PAYMENTS join table only. Everything else is unchecked as normal.

                    Looks like I am getting there on the backend of it.

                    • 7. Re: Invoice Payment Solution
                      philmodjunk

                      If you plan to organize your ledger entries by account, you need a field to identify the account. All payments in the ledger for the same account would have the same value in this field to identify them as belonging to this account. You can then sort or perform a find on this field to see all payments made on this account.

                      This would be the value of a serial Id field defined in another table. This could be a clientID serial number in a client table or an accountID number defined in an accounts table. You'd then enter/select the account ID when logging a payment. This might be automatic or a value selected from a value list.

                      • 8. Re: Invoice Payment Solution
                        jlhsolutions

                        I was planning on having a portal on my Accounts Layout that would show the payments recieved for that particular account. I think I can do this since I created a relationship between the ID_Account fields.

                        As for my layouts, what do you recommend? I was thinking a portal on the PAYMENTS layout, but wasn't sure if this is the best approach or not.

                        • 9. Re: Invoice Payment Solution
                          philmodjunk

                          I'd think you'd use a portal to the join table on your payments layout. You can specify an account (drop down list or other method), Enter the payment amount and then your script can pull up all unpaid invoices for that account, start with the oldest and generate matching records in the join table until every dollar of the payment is accounted for.

                          • 10. Re: Invoice Payment Solution
                            jlhsolutions

                            Do you have an example of this portal or a screenshot? Doesn't the join table only have ID_PAYMENT, ID_INVOICE and PAYMENT_PORTION in it? It would seem to me that I would need to see the Invoice Number, Date, Invoice Total.

                            Maybe I don't have something setup right, or maybe I am overthinking this too much.

                            • 11. Re: Invoice Payment Solution
                              philmodjunk

                              With portals, you can include fields from a related table inside the portal rows. Thus, you can add fields from the invoice table to the rows of your portal to the payment_invoice table.

                              • 12. Re: Invoice Payment Solution
                                jlhsolutions

                                Here is an image of my relationships. Does it look like I have it correct? I have tried to keep the naming the same as the original file so it would all make sense if someone were to look at it later.

                                T24_PAYMENTS = PAYMENT_LEDGER

                                T24a_payments_ACCOUNTS||id_account| = ACCOUNTS

                                T24b_payments_INV_PAYMENTS||id_payment|id_account| = INVOICE_PAYMENTS

                                T24b1_payments_INVOICES||id_invoice| = INVOICES

                                 

                                Payment Relationship

                                • 13. Re: Invoice Payment Solution
                                  philmodjunk

                                  I don't see any errors. Just make sure that any invoice fields that you add to the join portal are selected from the T24b1_payments_INVOICES||id_invoice| table occurrence.

                                  • 14. Re: Invoice Payment Solution
                                    jlhsolutions

                                    Thanks Phil. Since I am still learning FM, some of the other examples that I have been looking at, the RELATIONSHIP graphs look a little different to me. For example, how come some have 3 lines coming off to the relationship on one side, but not on the other side. I am not sure what this is called yet. 

                                    An example post of yours, http://forums.filemaker.com/posts/e1714dd77d/, that image shows the 3 lines and some of the fields have the little circles next to them. I was just curious what these mean. Also, what are they called so I can start calling them out correctly.

                                    1 2 Previous Next