5 Replies Latest reply on Oct 25, 2010 1:31 PM by philmodjunk

    Reporting with two tables

    wyrd9

      Title

      Reporting with two tables

      Post

      Hello.  I'm developing my first database using FileMaker Pro 10 on Mac OS 10.6.4.  I am the only user, and it is currently in use (thousands of records).  The relevant tables are related as follows:

      Accounts --< Users--< Appointments >--< Invoices   and   Appointments >--Resources  

      The Appointments and Invoices tables are related in that many to many way in this table occurrence group (along with an unseen relationship with another Invoices TO) in order to forward a balance from earlier invoices on the same account.  However, there are many appointments to one invoice. 

      I use one layout (Assign Invoices; table=Invoices) to assign invoice #s to each account for a given period (quarterly), and a report based on the Appointments table to display an itemized list of appointments for the numbered invoices for that period to export and print.  This may not be the most efficient way to do it, but it's all I could come up with.  The portal on the Assign Invoices layout is insufficient in cases where there are many appointments (75 or so) and doesn't display them as nicely.  Now that I have forwarded the balances from one quarter to the next, I am running into the following problem:

      Because the report is from the context of the Appointments table, only invoices that have appointments associated with them show up.  This excludes invoices that have a balance due but no appointments for the period of the found set.  If I run the report from the context of the Invoices table, I see all invoices, but of course only the first Appointment is listed in cases where there are appointments.  Is there a way to display all invoices with a balance due and all appointments?  (Please be gentle; I haven't scripted yet.)  ;P

      Please let me know if clarification is needed.

        • 1. Re: Reporting with two tables
          philmodjunk

          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?

          • 2. Re: Reporting with two tables
            wyrd9

            I'm sorry; my nomenclature was unclear.  There is no "invoices and appointments".  I should have said:

             Accounts --< Users--< Appointments >--< Invoices

              and  

            Appointments >--Resources  

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

            • 3. Re: Reporting with two tables
              philmodjunk

              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>--Appointment_Invoices---<Invoices

              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.

              • 4. Re: Reporting with two tables
                wyrd9

                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.

                • 5. Re: Reporting with two tables
                  philmodjunk

                  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