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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.