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

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)

Got it. Thanks!

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

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.

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

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

Sales.fp7.zip

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

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:

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.