I have a database in FMP 8.5 to keep track of class action lawsuites. There are tables for Plaintiffs, Defendants, Cases and other things. The Claims table links a specific Plaintiff and Defendant combination together in a Case. Claims can sever from one case into another and I need to track that. To sever a claim I essentially duplicate a claim and change the case it's in and then change the origional and new claim to link to one another. Then the origional claim's status becomes severed.

The Claims table has:

Claim_ID

Plaintiff

Defendant

Case

Claim_Severed_To

Claim_Severed_From

So I created three table occurrences for the Claims table: Claims, Claims__Severed_From, Claims__Severed_To. This allows the Claims TO to have relationships for everything but the Claims__Severed_From and Claims__Severed_To TOs don't have relationships for Claim_Severed_To and Claim_Severed_From.

Is FMP smart enough to figure out what to do?

Is there some trick to making the relationships work without making endless table occurrances?

Thanks!

Is FMP smart enough to figure out what to do?

It depends on what do you actually expect it to do. It seems like you have enough there to cover the situation, but this type of data model can be difficult. See also this thread and the one mentioned in it.

You already have everything you need to have. For each claim you can view the severed claims in a portal. You can go to any of these claims and see the claims severed from the sub-claim and so on, ad infinitum. You can also display the "parent" claim and also go to it. That is you can browse the hierarchy of claims in both directions.

BTW, two keys may be redundant. You can have just one field "Severed From" and use it in both the relationships:

Claim::ID - Severed Claim::Severed From

Claim::Severed From - Parent Claim::ID

My concern has been that if I have four records in the Claims table with data:

Record 1:

Claim_ID = Claim_1

Severed_From = ""

Severed_To = Claim_2

Record 2:

Claim_ID = Claim_2

Severed_From = Claim_1

Severed_To = Claim_3

Record 3:

Claim_ID = Claim_3

Severed_From = Claim_2

Severed_To = Claim_4

Record 4:

Claim_ID = Claim_4

Severed_From = Claim_3

Severed_To = ""

that somehow the data tunneling would not work out correctly. That the link between Claim_1 and Claim_2 would work but after that FMP would either stop or not know what to do.

After reading the other threads suggested and looking through the examples I am more convinced that the relationship graph I attached earlier is correct.

All the testing I have done makes me think that it's going to work but it has not been something I have been convinced will work correctly once there is a large ammount or real data in the system.

None of the books I have read or internet sites I have searched have been very clear on this.

Thanks so much for your help.

Scott