How you set up to process payments can depend a bit on your business rules. Can a customer pay off multiple invoices with a single payment? Can they make a payment that pays off one or more invoices with the balance to be applied to the oldest invoice left unpaid?
Generally, you log payments in a table set up to function as a register and then use one of several approaches to link that payment to specific invoices for a given customer. This often results in tables and relationship that look like this:
A record in Payment_Invoices links one Invoice to one Payment record. Since you can have multiple records in Payment_Invoices with the same PaymentID or with the same InvoiceID, it allows you to link many invoices to one payment and also to link many payments to one invoice.
Hi Phil, thank you for your response. I should of clarified the business rules. One payment can be applied to one or more invoices. In addition, there are times when a payment is received and we are not immediately sure what invoice to apply to apply it to. Therefore a customer could have a credit that could be applied at a later point to one or more invoices. It is for these reasons that I cannot process all payments through a simple portal on the invoice layout. It would seem that a payment layout is needed. In this layout I would choose a customer and have all open invoices (invoices with a balance due) populate in a portal below. This is where things get a little confusing for me. I think I figured out how to show the open invoices, but now the scripting is the sticking point. There has to be a solution for this. It seems to me that this is a fairly common business need.
Also is there a way to make a value list from data that comes from more than one field? Every time I try to create this, I get the poison pen message about not being able to create the value list because the fields cannot be indexed. Ugh, sigh.
As long as the fields are in the same table, you can use them in the same value list definition. If you use a calculation to combine fields from more than one table, then the field is unstored and there's no index for the field that can be used to define a valuel list. In many cases, the solution is to use a conditional value list to eliminate the need for the additional data that you are trying to add from a related table.
I haven't made any suggestions with regards to portals one way or the other though what you describe doesn't preclude using a portal on a given invoice--though you may need an additional layout for situations where the payment applies to more than one invoice or where you don't know what invoice to which to apply the payment. I've simply suggested a table structure that supports all possibilities.
Since payments may split over multiple invoices, you need a way to document how the funds from one payment is divided. I'd have the following fields in Payment_Invoice:
PaymentID (links record to specific payment)
InvoiceID (Links record to specific invoice)
PaymentPortion (number, may be part or all of total payment, represents that portion of a given payment identified by PaymentID that is applied to a given invoice identified in InvoiceID.
In the Invoice table, Sum ( Payment_Invoice::PaymentPortion) gives you the total of all payment funds applied to the current invoice. If this sum equals the invoice total, the invoice is paid.
On a payment ledger layout, you can have a portal to Payment_Invoice where you can select an invoice to which you will apply all or part of a given payment. You can have a conditional value list in this portal that allows you to see only the unpaid invoices for the client who made the payment. In the PaymentLedger table, sum ( Payment_Invoice::PaymentPortion ) should tell you the total amount of your client's payment.
There are many details from there that you'd need to work out, but that should at least point you in the direction I'm suggesting...
Thanks again Phil!!! :)
re value lists: the data does indeed come from different tables. I don't think I see a solution as of yet. I might be in trouble on this one.
re portal: You are absolutely correct, you did not make the portal suggestion, I just wanted to mention that, that solution would not be sufficient. In addition, you are correct again in saying that I am however not precluded from using the portal on the invoice layout in some situations.
re payments: Say I create a new payment, then select the client. Now my portal populates with open invoices for that client. Now I enter in amounts to apply to one or more invoice. It would seem that the fields in the portal would all come from the given invoice, except for the "amount field". This is the field that belongs to the "Payment_Invoices" table (i think), the field you call PaymentPortion? If this is correct, then I think I follow you on the table structure, it is the scripting that is giving me some difficulty. Do I need to do some looping to apply the "PaymentPortion" to the Payment_Invoices table?
In addition, wouldn't the portal on the payment_ledger layout come from the invoice table in order for it to show open invoices? You got me stumped on this Phil. I know, I know, your probably scratching your head, saying to yourself how easy this is and why can't this guy get it! Sorry I am doing my best to try to keep up.
Join tables in filemaker aren't the simplest database concepts to implement.
"Say I create a new payment, then select the client. Now my portal populates with open invoices for that client."
Not quite. A conditional Value List will populate with a list of open invoices. You'd then select an invoice and either pay it in full or assign any amount up to the unassigned total to that payment to the selected invoice. Once you've selected that invoice, you could indeed show other fields from that invoice if they are helpful. Even though the portal is based on the payment_invoice table, you can add fields from Invoices to this portal and they'll display information from there once you've selected an invoice.
No scripting is strictly needed, though a few can help data entry run a bit smoother. Since you may be splitting up an payment over several invoices, you may want scripts that help you with the math and another that flags an invoice as paid once the total assigned payment funds in Invoice_Payment equal the invoice total. That flag field will give you a stored field in invoices that you can use in a relationship to set up a conditional value list of unpaid invoices for a selected client. You can also define the payment portion field to look up the invoice total of the selected invoice as this will often be the correct amount when logging payments. When it's not, such a looked up value field can be edited to show a lesser amount.
In many ways, this payment ledger will function just like your invoice. It's just that you are selecting invoices to which you apply payments that total up to your total payment amount instead of selecting products that total up to the total invoice amount.
Are you saying that on the payment layout, there will be portal from payment_invoices and on the same payment layout there will be a conditional value list showing unpaid invoices? If not, I do not see how the payment_invoices table can show unpaid invoices. My understanding is that the payment_invoice table is a join table allowing the many to many relation between invoices and payments.
Phil, I am missing the magic that allows the payment_invoice table (a join table) to show open invoices.
Hmm, after writing this I paused and reread your instructions again. The functionality I was thinking of, was having a portal on the payment layout that lists (shows) all unpaid invoices for a particular client. I think the method you are describing does not show the unpaid invoices, but allows the user to choose the foreign key InvoicesID from the payment_invoices table, of course the drop down list that appears is the conditional value list of unpaid sessions for the client. Am I still lost in the forrest?
Phil thank you for being so patient with me, I have been trying to figure out this payment solution for what seems like forever. I am sorry I am having such a hard time with the logic.
"Are you saying that on the payment layout, there will be portal from payment_invoices and on the same payment layout there will be a conditional value list showing unpaid invoices?"
That's essentially what I meant. More specifically, this conditional value list would list all unpaid invoices for the customer making the payment in a drop down list inside the portal so that you can select a invoice against which you can apply all or part of the customer's payment.
You'd select a customer in the payment layout to identify whose making the payment, then go to the portal and itemize it by selecting invoices from the dropdown, one invoice per row in the portal. The sum of the payment portions in the portal would equal the total payment being made.
First, thank you for all your help. Second, I think I follow you to this point, but I could use some instruction on how to set up the conditional value list? Again I would like to show unpaid invoices that are related to the client who has made the payment.
I figured we'd get to that point sooner or later. To set this up, you'll need a stored indexed field in Invoices that indicates whether or not the invoice has been paid. A calculation field that uses the Sum function to compare the total payment portions to the invoice total tells the invoice is paid, but it can't be used for this as it will be an unstored calculation. Hence my earlier mention of a "flag" field in Invoices.
We could use any number of types of fields here, but let's use something with very obvious values.
Define a text field, PaidStatus, in Invoices.
Define a calculation field, cTotalPayments as Sum ( Invoice_Payment::PaymentPortion )
Write this script:
IF [ Invoices::InvoiceTotal > Invoices::cTotalPayments /* invoice is paid in full */]
Set Field [ Invoices::PaidStatus ; "Paid" ]
Set Field [ Invoices::PaidStatus ; "" ]
Use the ONObjectExit script trigger in your Invoice_Payment portal on your payment ledger to perform this script everytime you exit the PaymentPortion field.
Define a calculation field in Payments, cPaid as "Paid".
Now you can define a relationship to a new table occurrence of Invoices that links the current Payment Record to all unpaid invoices for the specified client:
Payment::CustomerID = UnpaidInvoices::CustomerID AND
Payment::cPaid ≠ UnpaidInvoices::PaidStatus
Define your value list of invoices specing Show related records from UnPaidInvoices, starting from Payments.
"I figured we'd get to that point sooner or later" Hey, well thanks for being so patient and taking the time to walk me through the entire process! You are a true asset to the forum. Not on FM payroll, hmm should be in my humble opinion.
Anyway I got things set up the way you described and things are working well. Keeping my fingers crossed :)
Thanks again Phil, you are the consummate agent!