Count of related records not working

[Erick P]

I've been stumped here for a few days.

I have a customer table and an order table related via the CustomerID field.

I have 3 basic products that customers order. I want to store the count of each order type in a calculated field inside the customer table. I need to store it because I have to export those fields.

For product 1 I created a calc field that says:

Case ( _orders::productA = "Yes" ; Count ( _orders::order_id ) ; 0 )

This works perfectly.  The strange thing is when I add a second calc field for productB, it only works for some records.



For example, customer #80 has 250 orders for productB but the calc field below returns 0.

Case ( _orders::productB = "Yes" ; Count ( _orders::order_id ) ; 0 )

I thought that maybe I have some corrupt data, but then why would the calc work for product A and not product B?

Any ideas would be very much appreciated.

Thanks,

Erick.

[David Jondreau]

Are you sure the first calculation is returning the correct number? I suspect it's not.

When you use a related field in a calculation by itself (not using Sum() or Count() or several other functions), the calculation looks at the first record as determined by the sort order of the relationship.

So what your first calculation is really saying is:

If the first order for this client is type A, return a count of all the orders otherwise return zero.

You second calc is saying:

If the first order for this client is type B, return a count of all the orders otherwise return zero.

Obviously, the first related record is of Type A.

You can solve your problem in two ways. Create a calc field in the Orders table for each of the fields you want to test. That calc would simply be:

_orders::ProductA = "Yes"

For those with the Yes, this will return 1, for the others, 0. Then Sum() that field from the Customers table with no test for "Yes."

Or, you can create new relationships between Orders and Customers which you relate the orders::ProductA field to a text constant in Customers of "Yes" and Count() all the related records.

[bruceR]

I'd suggest this custom function instead:

http://www.briandunning.com/cf/894

[bcooney]

Great catch, DJ, and terrific explanation.

[David Jondreau]

I'd suggest this custom function instead:

http://www.briandunning.com/cf/894

Which is also right here on these forums:

TypeSumField()

but the OP doesn't claim to have Advanced version of FileMaker (which is what you need to install custom functions).

[David Jondreau]

Thanks!

[Erick P]

DJ,

Thanks for the quick replies. I Implemented your idea of setting a binary field in the order table, then used a sum function in the customer table. Worked perfectly.

Bruce. I'll take a look at the custom function. I did recently upgrade to Version 9 Advanced.

Thanks again. I never would have figured that out on my own.

[LaRetta]

You can solve your problem in two ways. Create a calc field in the Orders table for each of the fields you want to test. That calc would simply be:

_orders::ProductA = "Yes"

For those with the Yes, this will return 1, for the others, 0. Then Sum() that field from the Customers table with no test for "Yes."

Or, you can create new relationships between Orders and Customers which you relate the orders::ProductA field to a text constant in Customers of "Yes" and Count() all the related records.

As an aside ... instead of creating number calculation flags to count the specific products, I would prefer to fill the calculation in Orders with the ProductID. It can still be counted (instead of summed) but it also can be used easily in many other filtered relationships.

So calculation for ProductA might be called c_ID_ProductA as:

Case ( productA = "Yes" ; _orders::order_id )

... but of course the real answer is to have a lineitems table and I think we would be negligent not to suggest it. Because, sure as sun rises, next year the boss will take on a fourth product. :crazy2:

Edited October 24, 2008 by Guest

[Erick P]

LaRetta,

I am stuck with a very poorly designed table structure. 5 years ago when I hired a company to build my online ordering system I was thrilled just to have it done. Today, I'm feeling much pain for poor planning.

Typical small business mentality, I suppose. We are planning to update the db soon. But for now I'm diving in to Filemaker to fill in the gaps. This forum has been a huge help already.

Erick.

www.emotionmedia.com