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

    Count records in related table with a specific value.



      Count records in related table with a specific value.


           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.