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?