calculation using multipe relationships

[maura]

I have a database in FMP 6, which I am working on rebuilding in FM 7. I am having a problem with calculations. Perhaps someone can tell me what I'm missing, or perhaps a better way to solve the problem.

 

I have 2 tables: Frog ID(a list of all the frog families(clutches) and individual frogs within the families) and Mating Reminders( a list to remind me which female and male frogs need to be mated each day).

 

In FM6, I had 2 relationships from FrogID to Mating records:

 

matingLogByFemaleIndividual - matches data between the Individual ID field in Frog ID and the Female Frog field in the Mating list.

 

matingLogByMaleIndividual - matches data between the Individual ID field in Frog ID and the Male Frog field in the Mating list.

 

I had a "Case" calculation field in the Frog ID file called matingReminder, which displayed an "M" to let me know the frog is on the mating list if either:

 

((matingLogByFemaleIndividual: FemaleID) and (matingLogByFemaleIndividual: matingcompleted=""))

 

(if the frog in the current FrogID record is a female and is on the mating list, and the checkbox indicating the mating is completed is not

checked)

 

or

 

((matingLogByMaleIndividual:MaleID)and (matingLogMaleIndividual: matingcompleted=""))

 

(if the frog in the current FrogID record is a male and is on the mating

list, and the

checkbox indicating the mating is completed is not checked)

 

Now I am trying to rebuild this in FM7. Instead of one Frog ID file, I have 2 occurrences, one for each relationship. In the calculation box, I have to define the context of the calculation, which can be either one occurrence or the other. How can I write this calculation using both of the relationships? I apologize for the lengthy description, but I figure more info is better.

 

Thanks for any help/advice!

 

maura

[FileMakin' Tom]

Hmmmmmmm. Maybe I should build a mating reminder for my girl friend . . .

[maura]

OK, so clearly either my question was too long or the solution is so obvious no one wants to bother. So I'll try a shorter question with out any references to mating...

 

How do I set up a calculation using multiple relationships(table occurrences?) when it seems I can only write a calculation from the context of one table occurrence?

 

Maura

[comment]

Short or long - I still don't understand your question. Seems to me you are misinterpreting the meaning of the 'context of calculation'.

 

Let's take a simple situation: Table MAIN and Table RELATED. There are two relationships from MAIN to RELATED. Therefore there are two table occurences of RELATED: RELATED_A and RELATED_B.

 

A calculation field in MAIN =

Case (

RELATED_A::field or RELATED_B::field ;

"good" ;

 

"bad"

)

 

This calculation needs to be evaluated from the context of MAIN. The evaluator is in the MAIN file, looking at the related fields as they are seen from the main file. AFAICT, this is the situation you have, therefore you have no issues with context of evaluation.

 

The situation would be different if the calculation field was in the RELATED table, and went like =

Case ( MAIN::field ; "ugly" )

 

Here it is possible for a single record in RELATED to have two different related records in MAIN. One record is seen over the relationship from RELATED_A to MAIN; the other comes in over the relationship from RELATED_B to MAIN (please remember that in ver.7 all relationships are bi-directional).

 

The calculation field in RELATED can have only one value, regardless of the table occurence. Therefore, when you define the calculation, you need to decide which MAIN record is the right one: the one seen from RELATED_A, or the one seen from RELATED_B. This is the meaning of "Evaluate this calculation from the context of ... ".

 

Again, if I understand your description, this is not your situation.