Hi All,

I have a database where I have a lead record (contact or person record) which is related to an appointments table where the different appointments are stored for each lead.

Basically, 1 lead can have multiple appointments. I need to run a report based off of the appointments table, and see how many total appointments I have and then also see how many unique people are in there.

It can't be based off the leads table or else this would be really easy - it has to be based off the appointments table because it needs other information that the leads table can't access directly.

So if I have 6 appointments with 1 person and 6 appointments with another person, I need to see the table below:

Sales Rep | Total Appointments by Sales Rep | Total Unique Leads by Sales Rep | |
---|---|---|---|

Sales Rep 1 | 6 | 1 | |

Sales Rep 2 | 6 | 1 | |

Company Total: | 12 | 2 |

Does anyone have any hints on how I can build a calculation in the appointments table to see how many unique Lead ID's there are in a found set of appointments?

Thanks in advance,

Dan.

EDIT:

To clarify any misunderstanding on the objective -

This is a sales report by sales rep. The sub-summary is breaking down the total number of appointments the sales rep went out on, and then I want to see how many of those appointments were a unique person and not just 5 appointments for 1 person.

So a sales rep could go out to 50 different appointment records, but the actually unique people he went to see could be 10. I need to know that. Using another sub-summary part that sorts by the lead will not work as I do not need the lead information I just need the count.

Sorry my first post wasn't clear, I didn't think of it from that angle which might have confused you.

My earlier (amended) suggestion still stands. Always assuming that you want this result in a found set sorted by rep:

• create a summary field ListOf: Appointments::_fk_LeadID

• create a value list of Appointments::_fk_LeadID

• create a calculation field where you

• use GetSummary ( sListOLeadfDs ; rep) to get a list of (potentially non-distinct) leadIDs by rep

• FilterValues() that list through the ValueListItems() of the value list to make it distinct

• ValueCount() that filtered list

That calculation field would give you the result per rep; place it into the sub-summary part by rep.

If you want a total of distinct leads in the found set, use another calculation field where instead of GetSummary ( sListOLeadfDs ; rep), you only use sListOLeadfDs.