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