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” …
"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” …
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.
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?
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.
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.
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.
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..
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.
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.
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.
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.