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 ;-)
1 of 1 people found this helpful
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 )
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" )
That's off the top of my head, I haven't tested it.
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!
Peter, How can I mark your answer as answer? I didn't find any action for that?
Thanks, yamu... unfortunately I have no idea how to mark an answer as answered... thanks for the Helpful uptick, though!
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.
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