5 Replies Latest reply on Apr 3, 2015 11:03 AM by philmodjunk

    Reporting On Multiple Tables

    DanPhillips

      Title

      Reporting On Multiple Tables

      Post

      Hi All,

       

      I am trying to create a report that accesses data and calculations from two different tables.

      The first table is a basic customer table that contains basic contact info, name, address, phone, etc. 

      The second table is a table that tracks all appointments with those customers, they are tied together by the primary key of the contacts table in a one-to-many relationship. This table tracks what product we sold them, how many, how much they paid, etc. 

       

      What I want to do is get a count on a report that will show the total number of contacts, even if they have never purchased anything or they don't have an appointment record in the other table. I just want a record count of the total. I then want another count of how many out of those records have set an appointment with us regardless if they made a purchase or if the appointment was cancelled. I just want to know how many have converted into customers/potential customers from those contacts. 

       

      How would I go about doing something like this? I've tried using calculations that count the ID records but if the report is based off of the appointment table I only get a count of the appointment records that exist, and if I try to base the layout off the contacts table, all the sales information and percentage calculations won't show up on the report. 

       

      I've been scratching my head for hours trying to figure out some way to use calculations to no avail. Please someone tell me you can help me. 

       

      Thank you! 

        • 1. Re: Reporting On Multiple Tables
          philmodjunk

          Don't you really have more than two tables?

          Seems like you need both a table for appointments and a table for sales.

          Even if you are only selling big ticket items like houses or such, I would think that you don't have a one to one relationship between appointments and items sold. (Might there be more than one appointment before the item is sold?)

          This sounds like a classic problem that results in people complaining: "Why doesn't FileMaker support Left Outer Joins?" If we could set that up on a layout, this would be easy to do.

          One option to work around this is to add a portal to appointments sized many rows tall, but set to "slide up", "Resize enclosing part". This gives you a portal that shrinks down to just the rows needed for a given contact when you print, preview or save as PDF.

          Another option is to add one dummy appointment record for every contact that lacks any appointment records. A number field in appointments that has data in records for actual appointments but does not have data in this "dummy" record can be counted to give you a count of appointments while still allowing you to list contacts that do not have any appointment.

          • 2. Re: Reporting On Multiple Tables
            DanPhillips

            Yes, we do have more than one table. The appointment table stores info on the specific appointments including how much a product was sold for and the little details on financing, etc. but we also have a table specifically for jobs we install. We do residential solar energy and other home improvement products, so there's a bunch of details in that table. The table I need to pull information from is mostly the appointments table, but I need a count of the total contacts we have to get a percentage of how many we set appointments with out of that. That's literally the only information I need from that table is how many total contacts we have. 

            • 3. Re: Reporting On Multiple Tables
              philmodjunk

              If all of the data you need for your report is in contacts and appointments, I don't read anything in your last post that changes my previous suggestions.

              • 4. Re: Reporting On Multiple Tables
                DanPhillips

                Well, this report only uses the sub-summary layout parts because it's basically showing sales by office and zip code and summarizing the gross sales, net sales, cancels, etc. I don't actually want to view any of the individual appointment or contact records. Created dummies won't work because our system is setup so that before a contact has an appointment created it is set to a different status and will only show in a certain part of the system - if I setup an appointment it will change this status and show up in the leads section instead of contacts. Can't have that because it will cause confusion for the database users. 

                • 5. Re: Reporting On Multiple Tables
                  philmodjunk

                  I think you need either those dummy records--what you describe does not make that option impossible or a separate reporting table into which you import just the records you need for your report. That would make the "dummies" records in a table separate from appointments.