2 Replies Latest reply on Sep 2, 2010 5:14 PM by SFX

    Calculation Question



      Calculation Question


      Hello everyone, I'm kind of new to the FileMaker DB, and I have a question, first this is what I have:

      Tables: Table1, Table2

      Fields: Table1::ID; Table1::Field1; Table2::id; Table2::Field1

      Relationship between both tables using the id field on both, Table2::Field1 has a list of values (a,b,c)

      What i want to do is put in the Table1::Field1, the number of fields that match with the ID and Table2::Field1 = a

      I tried different things with Sum, Count, Get but nothing is working properly, such as:

      Sum ( Table2::id = Table1::ID; Table2::Field1 ="a")

      But if there's any other record with the same id but with value b, it puts 1 and if I change any value it only displays 2 an do nothing more.

      I will greatly appreciate your help


        • 1. Re: Calculation Question

          Sum ( Table2::id = Table1::ID; Table2::Field1 ="a") if defined in table 2 is adding the two boolean expressions which is not what you want here.   (if Table2::id = Table1::ID is true, that's a 1, If Table2::field1 = "a" is true that's a 1 and 1 + 1 = 2.)

          Define a calculation field, cAflag, in Table 2 as Field1 = "a", This field will show a 1 (true) if field 1 = "a" and 0 (false) otherwise.

          Define a calculation field cAcount in Table 1 as Sum ( Table2::cAFlag ) and it will return the total number of related records in Table 2 where Field1 = "a".

          It's very possible from what you have posted that what you really want is a report that groups all the records in Table 2 by ID and lists a subtotal count for each value in field 1. That can also be done with a summary report and some summary fields.

          • 2. Re: Calculation Question

            Thank you for the very quick answer, I tried and worked perfectly, thank you very very much.