Calculating Across A TO Group

[Ron Cates]

Hi all. I have been trying to pull a percentage using values from 2 different tables across a TO group. It seems like it shouldn't be too complicated but I have run into a snag. I am looking for the pecent of sales a salesperson sold from a group of leads called. I have provided an image of the TO group in question. It has been stripped down to just the TOs in question.

From a layout based on the Employees 2 TO I have been able to pull in the number of leads a salesman called with a field in the Leads table that gets the found Count, filtered through the relationship from the Leads 2 TO. In the same way I have been able to get a found count of sales from the Tickets table (a ticket is an invoice) filtered through from the Tickets 2 TO.

So it seems that I should be able to get a percent of sales with a calculation field in Employees with a calc such as:

Tickets 2::zfound / Leads 2::zfound

(zfound is a maintanance field I add to every table that gets the found count)

However, when I try to create a calculation field in Employees, the Leads 2 and Tickets 2 TOs are listed in the specify Calculation dialog box as unrelated tables and can't be refferenced in a calculation.

Questions:

Why are these TOs not recognised as related?

How can I achieve my goal?

Thanks for any help :P

Edited November 11, 2010 by Guest

[comment]

Caveat: I did not fully understand your explanation, and my remarks are targeted at selected snippets only:

I have been able to pull in the number of leads a salesman called with a field in the Leads table that gets the found Count, filtered through the relationship

That's not possible. A relationship ignores any found set.

when I try to create a calculation field in Employees, the Leads 2 and Tickets 2 TOs are listed in the specify Calculation dialog box as unrelated tables and can't be refferenced in a calculation.

I believe you need to select 'Evaluate this calculation from the context of: [ Employees 2 ]' - since Leads 2 and Tickets 2 are related only to this TO.

[Ron Cates]

Hi Comment. The evaluate from did the trick.

As for the found count thing. I have a field (in every table actually) called zfound with a calculation of Get(FoundCount). I don't remember where I first heard the technique but when accessing the field through a filtered relationship it returns the number of filtered records. I remember reading about this as an alternative to the Count() function. It is supose to be faster.

Thanks again :P

[Vaughan]

I remember reading about this as an alternative to the Count() function. It is supose to be faster.

Something I've picked up (from either here or TechNet) is to have an unstored Get( RecordNumber ) field and use Last() function on it through the relationship to get the "count" of related records.