8 Replies Latest reply on Jun 22, 2016 8:30 AM by yamu

# counting group of records in child-table

Hello,

My scenario: I have a table "Inspections" with a child-table (1:n) "InspectionQuestions". For 1 "Inspection" there can be a lot of "Questions", and a question can have 1 "Type" of A, B or C. In the table "Inspections", I need 3 calculated fields ("CountQuestionTypeA", "CountQuestionTypeB" und "CountQuestionTypeC") which shows the count of records for each question-type in the child table. I now tried a lot of formulas, but I didn't get the right result. For example, for Type A, I tried this:

Sum ( If ( Length ( Inspections::FText ) > 0 AND Inspections::Type = "A" ; 1 ; 0 ) )

This delivers the famous "?" ;-(

What's the correct way to calculate the number of records grouped by a field-value?

• ###### 1. Re: counting group of records in child-table

You didn't give your calculation a field to sum.

If (Not IsEmpty(Inspections::FText) and Inspections::Type="A";Sum(keyField))

• ###### 2. Re: counting group of records in child-table

Thanks for your answer, but that's not what I want. I had a typo-failure in the above formula. it should be:

Sum ( If ( Length ( InspectionQuestions::FText ) > 0 AND InspectionQuestions::Type = "A" ; 1 ; 0 ) )

This formula is in the field "CountQuestionTypeA" in the table "Inspections" and should count every joined record for this "Inspections"-record in the "InspectionsQuestion" table which is of type A. So in other words, if this Inspections-record has 7 child-records in InspectionQuestions with 1 of type A, 2 of type B and 4 of type C, than the value in "CountQuestionTypeA" must be 1, the value of "CountQuestionTypeB" must be 2 and the value of "CountQuestionTypeC" is 4.

My thoughts for this formula is: I check, if Type = "A" then I give the value "1", if not, I give "0". The SUM of all "1" is equal to the count of records with value "A". In this way I do it all the time in T-SQL with Microsoft SQL Server or in Microsoft Access. I know, FileMaker is different, but I can't figure out how ;-)

• ###### 3. Re: counting group of records in child-table

Unless I'm mistaken, you want two sets of fields for each question type:

IsQuestionA = If ( Length ( InspectionQuestions::FText ) > 0 AND InspectionQuestions::Type = "A" ; 1 ; 0 )

and

QuestionACount ( summary, total of IsQuestionA)

QuestionACount will give you a sum of questions of type A across the found set.

Or, you could do something like:

Type_ListOf (summary, ListOf Type ) -- a summary field that returns a ListOf the Type field across the found set, and

QuestionACount = (unstored, number) PatternCount ( Type_ListOf ; "A" )

QuestionBCount = (unstored, number) PatternCount ( Type_ListOf ; "B" )

etc.

That's off the top of my head, I haven't tested it.

1 of 1 people found this helpful
• ###### 4. Re: counting group of records in child-table

Thanks a lot Peter. I think I understand you right, because now it works ;-) In "InspectionQuestion" I created a new calculated field "IsQuestionA" with the formula above, and in "Inspection" I made a calculated field "QuestionACount" with "Sum ( InspectionQuestions::IsQuestionA )". And that works as expected!

• ###### 6. Re: counting group of records in child-table

Thanks, yamu... unfortunately I have no idea how to mark an answer as answered... thanks for the Helpful uptick, though!

• ###### 7. Re: counting group of records in child-table

I'm glad that it's working for you, yamu. I guess the big drawback to this solution is that you'll have to add new question type fields for each new question type you add to the system. If this is for reporting purposes only, you might want to explore using a combination of Merge Variables, Hide Object When, and ExecuteSQL to generate these totals only when needed.

• ###### 8. Re: counting group of records in child-table

Peter, I'm aware of this drawback, but I only have 8 fixed types, it's only for reporting and as a calculated field in the table, I need no scripting, and ExecuteSQL in the report. I find it smoother, having reports without scripting if it's possible.

Thanks again, Hans