Calculation problem... I am stumped!

[Eclipse]

Hello, i have been trying to figure this out for a while now, hopefully someone can help me...

I have 2 files, "import"fp5" & "members.fp5".

Each file has the following Fields and values:

Field Name: Member_Status

Values: value list - choices: Active, Inactive, Suspended

Field Name: DMA

Values: value list - 001 New York, 002 Los Angeles, 003 Chicago, etc...

What i am trying to do is create a calculation field in the "import.fp5" file that can tell me how many records in the "members.fp5" file have the same DMA as the current record in the "import.fp5" file BUT ALSO only have a status of "Active".

I have built a relationship named "count_qty_mbrs_same_DMA" from "import.fp5" to "members.fp5" based upon the DMA field. Also, in the "import.fp5" file, i have created a calculation with the following definition:

Count(count_qty_mbrs_same_DMA::Member_status)

However, the calculation only returns the total quantity of records in the "members.fp5" file with the same DMA as the current record in the "import.fp5" file.

For instance, if I am browsing the "import.fp5" file and the DMA value for that current record is "001 New York" , then it will correctly show that there are 12 records with the value "001 New York" in the "members.fp5" file, however I can't seem to get it to tell me how many of those related records have a value of "Active" in the Status field.

It's confusing, because i do not want to have to do a find script, it needs to be a calculation with unstored results.

I hope i explained this well. Thanks!

Chris

[ESpringer]

Chris,

This is the kind of thing that has become much easier in FMP7, where you can have a relationship match on two parameters. But it's entirely doable in FMP6. You need to create a concatenated calc field in the members file that will show values that look like this:

001_Active

002_Inactive

003_Active

001_Suspended

etc.

Make the calc (called "c_DMA_Status_Concat" or something like that) do something like this:

[Eclipse]

Hello again, made a quick example file i attached hoipefully it will help explain exactly what i am trying to do.

In the file "member.fp5", there are six records with the value "001 New York" in the DMA fields however only 4 of them have a value of "Active" in the Status field.

In the file "import.fp5" when looking at the first record, since the value of that record's DMA field is "001 New York" i need it to give a count of 4 instead of 6. It's showing count of all related records and i only want it to show the count of all related records that have a Status of "Active".

Please help

Thanks!

Chris

FM_problem.zip

[ESpringer]

Chris,

Sorry: another quicker way, given your simple task: have a calc field in the members file that displays the DMA *only if* the member is active.

[Eclipse]

E Springer thank you very much. Your solution worked perfectly. I was meddling around with the IF statement yesterday, but having problems, I tried to work something with CASE as well, but couldn't figure it out. Your proper use of the CASE statement along with the chenge in the relationship workled perfectly. Thanks!

I posted the corrected file here in case others have a similar problem and looking for an example to look at.

FM_problem.zip