Sum of field based on value

[fmisaw]

hi,

I'm not sure how to best describe this, so if there is a thread with this question answered it isn't that I'm not too lazy to search.

the problem is this. I have three tables: people, invites, and events. each event and each person have a unique key that are related to each invite entry. invites can only have one person and one event - but each person and event can have many invites. the invite table is essentially glue between the other two.

I have a field in events that gets a sum of all the people who have reserved a spot in the invites table. in the invites table I also have a field (which I set up with some help from here) that has three values - accept, decline, or pending. I would like a count of each of these. however, when I use Count(status = 'decline'), filemaker tells me that function, and some others I tried, only accept straight field values. so...I'd like the same behavior as my Sum(Events::Attended), but I want a conditional state so I only count a given type of value.

I feel like I'm not clear, but any help is very appreciated.

[Lee Smith]

I'm not sure of all of the things you are trying to do, but use Quotes " for anything other than Actual field Names, and numbers. Have you tried Pattern Count Function, i.e.PatternCount(Status ; "Decline" ?

HTH

Lee

Edited January 30, 2008 by Guest

[David Jondreau]

In the invites table, create three more fields, all calculations.

cAccept = Status = "Accept"

cDecline = Status = "Decline"

cPending = Status = "Pendling"

Those fields will return a 1 if the Status matches. Sum() those fields.

[fmisaw]

thanks DJ, that did it.