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

    counting group of records in child-table

    yamu

      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?

       

      Thanks for your help! Hans

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

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

           

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

           

          See link Sum    Sum(field)

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

            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
              PeterDoern

              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
                yamu

                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!

                • 5. Re: counting group of records in child-table
                  yamu

                  Peter, How can I mark your answer as answer? I didn't find any action for that?

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

                    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
                      PeterDoern

                      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
                        yamu

                        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