1 Reply Latest reply on Feb 24, 2014 2:40 PM by philmodjunk

# Count records in related table with a specific value.

### Title

Count records in related table with a specific value.

### Post

I need to count records in related table where the value of a specific field has a specific value.

I have a customer table that has the total number of 200 ML bottles and 375 ML bottles (separate fields)

I have transactions that have a field (Bot) with different bottle discriptions/values i.e. 200 ML, 375 ML etc.

I want to count the number of 200 ML bottles/records, in the transaction table for a given customer.

I am trying to use a calculation field "Customer ::Total 200 ML Bottles" = If (InvDetail::Bot = "200 ML" ; Count ( InvDetail::Bot ) ; 0)

I don't seem to be getting the correct number.  It is counting records with the "200 ML" but also fields that are zero.

• ###### 1. Re: Count records in related table with a specific value.

I could take my wife out to dinner several times over at a pretty swanky restaurant if I were given a dollar for every time this question comes up.

If (InvDetail::Bot = "200 ML" ; Count ( InvDetail::Bot ) ; 0)

is a very common mistake made with this issue as well. In any calculation that refers to data from a related table (invoice::Bot), unless you use a special function that does something different, you are only referencing the data in the first related record. In your calculation, if the first related record is is for a 200 ML bottle, the Count function will count all related records, whether the record is for a 200 ML bottle or one of a different size. If the first related record is not a 200 mL bottle, you get zero. Of course, neither result is what you wanted.

Two methods are found here: Sum_Calculation based on condition

Another method using Execute SQL() is described here: FMP 12 Tip: Summary Recaps (Portal Subtotals)

And while I can't really recommend it, you could set up a bunch of calculation fields in InvDetail like this one: If ( Bot = "200 ML" ; 1 ) and then you can sum or count this individual field to get a count for each size bottle.

PS. do you really only list one bottle in each detail record? Wouldn't it make more sense and save time to have a qty field so that if a customer buys 30 200 mL bottles, they can create one detail record with a qty of 30? In such case you don't want count you want to sum the quantity field.