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::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.
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.
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.
PaymentMethod (Cash, CreditCard, Check, etc.)
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.
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!
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]
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]
Sort Records [Restore ; no dialog ] // sort by ascending order to put oldest invoice first.
Go to record/request/page [first]
Set Variable [$InvoiceID ; value: Invoices::__pkInvoiceID ]
Set Variable [$Unpaid ; value: Invoices::UnpaidBalance ]
Go to Layout [Invoice_Payment]
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 ]
Go to Layout [original Layout]
Show Custom Dialog ["No Invoices exist for this customer."]
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.
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.
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.
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?
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.
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?
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.
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?
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?