Nesting calculations - cant figure this one out...

[johnmac]

Can anyone help. FMP heldesk couldn't. Have data in File 1 which I need to display in File 2 (and other files). File 1 has Field ('need met or unmet') which can contain either the value 'Met' or the value 'Unmet' which is unique to a record for a particular client, identified by their unique ID number.. I have been able to create a calculation field that will Count(File 1::need met or unmet) and returns the correct numerical answer for a given client, for example 8 (which comprises 3 'Met' needs, and 5 "Unmet' needs). I require to further refine this calculation to count/display ONLY the values 'met' or 'unmet' and return the numerical results 3 or 5. I have tried to nest a calculation within the overall Count calc. to force the calculation to look for only field content > 3 (ie 'Unmet) or < 4 (ie 'Met') however it does not work. I have spent hours and every conceivable permutation to get this to work but have realised am not skilled enough to figure this out. Any suggestions for what the nesting calculation should be to obtain the desired result!?

[LiveOak]

If you are through beating yourself up with this complicate approach, the answer is simple, but requires some more fields in the original file. The Count function only counts the number of related records for a relationship. You can get the met and unmet totals in one of two ways.

1) Create two new relationships, one to match only client#/unmet and the other to match client#/met. Then the Count function will work using two (actually three with your original relationship) relationships.

2) Create flag fields in your original file:

MetFlag (calculation, number) = If(need met or unmet = "met", 1, 0)

UnmetFlag (calculation, number) = If(need met or unmet ="unmet", 1, 0)

Now use a calculated field in your related file Sum(relationship::MetFlag) or Sum(relationship::UnmetFlag) to give you a Count. REMEMBER the Count function only counts the number of related records.

I'm sorry to hear FMI tech support couldn't answer the question, FMforums is better and cheaper anyway! (a little plug).

-bd

[The Bridge]

Off the top of my head:

Define fields:

Is_Met (number)

Met_Constant (number, indexed) = 1

Unmet_Constant (number, indexed) = 0

Define Valuelist:

vl_1: "1" (for user interface: Format Is_Met on layout with checkbox using this valuelist)

Define Relationships:

Self|Met (match Met_Constant to Is_Met)

Self|Unmet (match Unmet_Constant to Is_Met)

Define fields:

Count_Met = Count (Self|Met:Is_Met)

Count_Unmet = Count (Self|Unmet:Is_Met)

Hope this helps!

[johnmac]

Phew - thanks folks! Excellent quick responses which are very gratefully received. Now off to try them!

[Vaughan]

Can I suggest you change the options "met" and "unmet" to something different: the string "met" appears in both and will make some functions (like find) harder in the long run.

Why not "met" and "unresolved" or something...

[johnmac]

LiveOak thank you that worked as perfectly and as elegantly as I could wish for!