11 Replies Latest reply on Sep 19, 2015 3:22 PM by electon

    Reporting from 2 Tables

    danjamins

      Hi everyone,

       

      I'm trying to figure out a solution for a report that needs numbers from two tables.

       

      In my database, I have three tables. Leads (contacts basically), appointments, and contracts (sales)

       

      Leads is related in one-to-many to appointments by the unique ID in leads table. One lead can have many appointments.

       

      Appointments is related to contracts by the appointment ID. A contract record is generated when an appointment result is changed to sale and the script runs to create the contract record and store that appointment ID into the foreign key field in contracts.

       

      The report I want to get numbers on is currently performing a find that goes into the appointments layout, finds all appointments within a given date range, and then goes to the report to break it down. It shows how many appointments and how many sales (based off result of appointment) were made in that date range.

       

      The problem is, sometimes a contract isn't created for an appointment for a few days after it was scheduled, so the contract date on contracts table might be in this month, but the appointment date is in last month, which means it won't show up on this month's report.

       

      What I want to do is base the calculation for sales to pull from contracts that have a contract date which matches the same date range specified for the report, and still show only the reports that have been scheduled within that date range.

       

      I am not sure how to go about doing this, can anyone give me an idea of how I could possibly accomplish this with maybe table relationships or something I just don't know yet?

       

      Thank you,

      Dan.

        • 1. Re: Reporting from 2 Tables
          erolst

          danjamins wrote:

          What I want to do is base the calculation for sales to pull from contracts that have a contract date which matches the same date range specified for the report, and still show only the reports that have been scheduled within that date range.

          Do you mean “still only show the appointments”?

           

          I'm not sure how exactly you want to design that search, but be aware that you can search in simultaneously in the Appointments date and the related Contract date.

           

          So if you'd you make this a bit clearer:

           

          "I'm trying to report on all appointments that fall within the date range and have a related contract that also fall within that range” …

           

          or

           

          "I'm trying to report on all appointments that fall within the date range or have a related contract that also fall within that range” …

           

          or …?

          • 2. Re: Reporting from 2 Tables
            danjamins

            I want to view all appointments within that date range AND all contracts with a signed date in that date range.

             

            The problem is a contract might be signed on 9/1/2015 but the appointment record that it was generated from could have an appointment date of 8/31/2015.

             

            So if I searched in appointments for appointment date 9/1/2015 that contract wouldn't show up in the report because the report layout is based off the appointments table - so the sales for this month would show less than what they actually are.

             

            Basically, I want to see the total number of appointments scheduled for 9/1/2015 and also the total number of contracts signed on 9/1/2015 even if the appointment that they are generated from is not on 9/1/2015 all on one report.

            • 3. Re: Reporting from 2 Tables
              electon

              I'll break it down:

               

              First comes the Appointment, then the Contract created from an Appointment.

              You want a report that shows Count ( appointments ) and Count ( related signed contracts )

               

              If both are related by an ID then create a summary field in Appointments: Count ( primaryKey ),

              and a summary field in Contracts: Count ( DateSigned ).

               

              All you need to do is perform a date range search in appointments. the rest will sort itself out when you put those field on the layout ( in the header part or leading summary, for example ).

               

              The Count ( DateSigned ) will omit empty fields in the count, so only signed contracts will show.

               

              Is this you're looking for?

              • 4. Re: Reporting from 2 Tables
                danjamins

                No. The contract date signed will never be empty. The only contract records in the database will always have a contract date - that is gven when it is generated.

                 

                I don't want just related contracts to the appointments found though. Some of the appointments found will not be the appointment that generated the contract.

                 

                Sometimes the appointment that generated the contract could be outside the data range specified, but the actual contract date will be within that date range.

                 

                I don't want to see that appointment in the report for this month if the appointment date is last month, but I do want to see the contract from that appointment if its contract date is this month.

                 

                Right now if I pull up a search in appointments date 9/1/2015 through 9/19/2015 there is a contract signed on 9/3/2015 that has the appointment date on 8/31/2015 so it doesn't show up in the report for this month as a sale because the appointment related record is not within the date range. I want it to be counted for in the sales column even though the appointment won't count in the appointments column because the sale is from this month not last month.

                • 5. Re: Reporting from 2 Tables
                  electon

                  I see. Unfortunately having the report tied to either appointments or contracts will not work.

                  If you search for the range in both tables, only the results for that one table will show related records.

                   

                  Are you familiar with Virtual Lists?

                  Otherwise you could create a table with unstored calculations where for each record you populate one date.

                  Maximum of 31 records, starting from first date of the month, ending on last date.

                  If the report should show one month at a time.

                  Relate this to both appointments and contracts and omit records only if there are no related records in both tables.

                   

                  It looks to me that you want to group them by date or month, not appointments.

                   

                  This method can be adopted to other models as well, depending on extra fields you create in other tables.

                  Like: Month ( DateSigned )

                   

                  Then you can have months table with 12 records. Works well in charting, if you want to see the whole span, not only where the data is.

                  • 6. Re: Reporting from 2 Tables
                    danjamins

                    I've never used a virtual list before.

                     

                    The report is basically a sales report.

                     

                    It doesn't summarrize by date, it just pulls the found set by date then summarizes the data by sales office. Underneath the sub-summary for appointments::Office it shows metrics like total appointments, total sales count, total gross sales amount, total net sales, total cancelled appointments, total no show appointments, etc. Then I want it to show what the percent of sold deals is out of the total number of appointments so a calculation for:


                    Count(Contracts::__pk_ContractID) / Count(Appointments::__pk_AppointmentID) which gives me the perentage of sales out of appointments.

                     

                    I thought maybe if I create a TO based on contracts and do the relationship Contracts::Date = Appointments::Date it would then be able to count the contracts that fall within that date range, because the find performed on appointments::date would relate even contracts that aren't created from those appointments that fall within the same date range. That's my thinking anyway, but I'm not sure.

                    • 7. Re: Reporting from 2 Tables
                      electon

                      For reasons explained earlier basing your report on either of your tables will not work.

                      Now things get even more juicy since you bring up that it's actually sorted by Office.

                       

                      You could have a look at this post which is somewhat similar..

                      Trying to Figure Out Summary Fields

                      • 8. Re: Reporting from 2 Tables
                        danjamins

                        Hmmm... I think I figured out a way to do this.

                         

                        Get the found contract count within the date range and store it within a global variable $$ContractCount that passes into a calculation field in appointments, grab the gross and net sales from the contracts and pass into $$GrossSales and $$NetSales and then use these all in calculation fields on the report. The rest can be generated off the appointments table.

                        • 9. Re: Reporting from 2 Tables
                          electon

                          You're on to something here.

                          But wouldn't having one global summary for the found set in Contracts make it impossible to sort by Office or any other field in Appointments?

                          You need a way of syncing both sets of data, I'd think.

                           

                          Unless that's exactly what you're after: one row of data.

                          • 10. Re: Reporting from 2 Tables
                            danjamins

                            Yeah, you're right about that, it would only show the same value across all rows. Bummer, I knew it sounded too good to be true.

                            • 11. Re: Reporting from 2 Tables
                              electon

                              Look, records in two tables are either related or not. What you're trying to do is more abstract than a regular report.

                              Therefore you need to resort to some other table, a work around.

                              If you look at that post, there's a file you can download and have a poke around. Hopefully it will inspire an idea.

                              HTH

                              Thomas.