What are the exact details of the relationship matching invoices and appointmentments to appointments? (what field matches to what field?)
When your refer to "invoices" are you always referring to "Invoices and appointments" or is there a separate table for Invoices not shown in your last post?
I'm sorry; my nomenclature was unclear. There is no "invoices and appointments". I should have said:
Accounts --< Users--< Appointments >--< Invoices
When I refer to "invoices," it is just the Invoices table.
Appointments and Invoices are related in this TOG as follows: Invoices::_kf_Account ID = Appointments::_kf_Account ID AND Invoices::Invoice Quarter = Appointments::Appointment Quarter. I am allowing creation of records in both tables via the relationship. Both tables are sorted (ascending) by Account ID and Resource ID foreign keys. To be perfectly honest, I don't remember why I did this. I know that the relationship makes filtering the invoices by Account and Quarter possible.
Additionally, there is another copy of the Ivoices table occurrence (Invoices 2), which is related to Invoices as follows: Invoices 2::_kf_Account ID = Invoices::_kf_Account ID AND Invoices 2:_kp_Invoice ID > Invoices::_kp_Invoice ID, with records sorted by _kp_Invoice ID (descending) in the Invioces 2 table. The purpose of this second relationship is to forward balances from previous invoices and is the result of some help on this forum http://forums.filemaker.com/posts/d2fe4af90d
(above _kf_ refers to a foreign key, and _kp_ a primary key.)
That helps, seems like there's some relationship issues with your basic structure.
Just to confirm: One invoice can bill for many appointments and more than one invoice can be issued to bill/pay for a single appointment? That's what this part of your post implies: Appointments>---<Invoices.
If so, you need a join table between these two to implement the many to many relationship:
Appointments::ApptID = Appointment_Invoices::ApptID
Invoices::InvoiceID = Appointment_Invoices::InvoiceID
The relationship you've posted:
Invoices::_kf_Account ID = Appointments::_kf_Account ID AND
Invoices::Invoice Quarter = Appointments::Appointment Quarter
Isn't one you can use to assign a specific invoice to a specific appointment.
Honestly, I am a little confused as to why I have an apparent many to many relationship between Appointments and Invoices, as it should be one to many, invoices to appointments (that is to say, there are many appointments on one invoice, but one appointment is never billed twice). However, it is working this way, currently. I think I need to comment my process more, as I rarely get time to work on it.
I think I round up all the appointments that belong to the given period (Appointment Quarter) with the following calculation:
Let ( MonthNo = Month ( Appointment Date ) ; Case ( MonthNo < 4 ; "First Quarter" ; MonthNo < 7 ; "Second Quarter" ; MonthNo < 10 ; "Third Quarter" ; "Fourth Quarter" ) ) & ", " & Year ( Appointment Date )
then, with the questionable relationship, made Appointmens::Appointment Quarter equal to Invoices::Invoice Quarter, along with the Account ID so that I could assign the appointments from that Account and Quarter to a particular Invoice... (?) There is another TOG that has Invoices::_kp_Invoice ID = Appointments::_kf_Invoice ID. However, I am using the TOG I originally mentioned for the Layout that assigns appointments to invoices. I'm sorry; I guess it's a bit of a mess. It's my first effort and I'm unable to prioritize it, given my workload.
Each post clears away some smoke. No need for the join table, it's just that you have this relationship: Appointments>---Invoices not Appointments>---<Invoices. In some businesses, they do indeed issue multiple invoices for one sale or service provided so that a customer can make partial payments over time. I suspected that would not be the case for you, but need to be sure.
Looks like you issue a quarterly invoice for all appointments made during that time interval. Working from that information, you should create your report layout based on the appointments table, not the accounts table. You can put fields from the accounts table and Invoices in the header of this layout and put the appointments field in the body to get a list of appointments for a given invoice. You can then perform finds and sorts to get all the appointment records for a given account for a given invoice number.
If you want print all invoices for a given quarter, do the same, but put the account and invoice fields in a sub summary part when sorted by Invoices::InvoiceID. Then you can find all appointments records that fall in the given date range, sorting them by accountID to group them by customer. You can set a page break after every occurrence option for a print below sub summary to force a page break after the end of each client's list of appointments.
Here's a tutorial on summary reports. Just think of the LineItems in this tutorial as appointments records and it should work for you: Creating Filemaker Pro summary reports--Tutorial