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

# Calculation Question

### Title

Calculation Question

### Post

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

Greetings.

• ###### 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.

Greetings.