Customer count from detail table

[T.J.]

I have two tables and I'm trying to figure out the caculation so that I can display the number of customers that a salesrep sold to in the (slsmcustcnt) table pulling from the (slsmdetail) table.

(slsmcustcnt) and (slsmdetail) are joined (slsmnum) field

(slsmcustcnt) has 1 field "slsmnum" with 3 records

W1

W2

W3

slsmdetail has 4 fields "slsmnum" "custnum" "custcnt" and "sales"

This table has several records

Example

"slsmnum" | "custnum" | "sales"

W1 | AA10 | $1

W1 | AA10 | $2

W1 | AA20 | $1

W1 | AA30 | $1

W2 | AA40 | $1

W2 | AA50 | $1

W3 | AA60 | $2

So what I'm looking to see in the (slsmcustcnt) table is

slsmnum | custcnt

W1 | 3

W2 | 2

W3 | 1

[Aussie John]

make a calculation in the slsmcustcnt table; count(slsmdetail::slsmnum) and select the pull down "evaluate calculation based on…" the slsmcustcnt table (in calculation dialog)

[T.J.]

Got it. Thanks!

[T.J.]

Acutally this only calculates the number of records for each slsmnum and not the unique number of customers.

[comment]

Do you have a table of Customers, where each customer has one unique record?

---

P.S. I cannot help wondering why you torture yourself - and us, by extension - with such cryptic names as "slsmcustcnt", "slsmdetail", "slsmnum", "slsmcustcnt", etc. If you have a structure of:

Customers -< Sales >- SalesReps

you can show a list of customers that a sales rep has sold to by placing a portal to Customers on a layout of SalesReps.

[T.J.]

Sorry, was trying to explain the issue as best I could.

I could add a table of customers where each customer is one unique record. But I'm not sure how to make that work?

I'm attaching a sample file

slsmcustcnt.zip

[comment]

I am afraid I don't understand your file at all. See if the attached helps.

Sales.fp7.zip

[Aussie John]

Acutally this only calculates the number of records for each slsmnum and not the unique number of customers.

Shoot - I thought that was what you wanted

[LaRetta]

Hi Michael,

Would you explain your reasoning in choose calculation in Sales Reps over summary in Customers (simply placed on Sales Reps layout)? Unless there is need to search that calculation, a summary field would then be available to all other other customer-related tables (without additional calculation fields in those tables) just by placing the summary on the related layouts.

I have been wondering about this - I even started a thread on the subject but abandoned it when Vaughan said they were used for different things - causing me to doubt my perception.

Today I'm feeling bold enough to ask and this is perfect example. I would have used a summary field. :cry:

[comment]

I don't know that I had a specific reason to prefer one over the other; I guess I tend toward a local calculation field, if only because the related summary field is a relatively recent development.

Note also that the calculation can not only be searched; it can also be used for sorting (which sales rep has the most customers?) and for further calculations down the line. Still, I suppose it would have been best to include both options in the demo.