Help: "families" of related records in same table; how to do?

[mikedr]

My filemaker database is about patent applications.  So I have a table of patent applications, one record per application.

 

Now, two or more applications can be in the same family.  A given application can only be in one family.  So, say we have patent applications Alice, Bob, and Carol.  If Alice and Bob are defined in be in a family, and Bob and Carol are defined to be in a family, then there is just one family, including Alice, Bob, and Carol.

 

I have absolutely no idea how to set up such a "family" in Filemaker, nor an easy way to specify a layout object so that a user can, when in Alice, for instance, specify that Bob and Carol are in the same family, such that when you navigate to Bob, you see that Alice and Carol are in the same family.

 

The idea is basically that when viewing any given application, you can specify which other applications are in the same family -- and if you view any of those other applications, you can also see the family it's in.

 

I'm certain that there's an easy way to do this.  Or at least a straightforward way, but I'm flummoxed as to how . . . . .

[comment]

I think you need to separate this into two issues: structure and user interface. 

 

On the point of structure, it seems you are saying that there is a one-to-many relationship between Families and Applications. If I am hesitant about this, it's only because you haven't explained what these "families" represent in real life - and the way you present the relationship as being established at the sibling level, instead of between parent and child. How exactly would Alice and Bob be defined as being in the same (unspecified?) family?

[mikedr]

They would all be siblings -- no family member is hierarchically above any other family member.

The number of family members is technically unlimited.  In general, you're talking about 2-3-4, but I've seen up to 20 or 30 in rare instances.

[comment]

If they are to be equal siblings, then they all must have a common parent ID value - and no mutual references to each other.

 

You didn't answer my question about what the "family" represents in real life. In the (unlikely) case that such family has no attributes of its own, you could perhaps get away without a Families table. In such case, assigning Bob to be "in the same family as Alice" could mean one of three things:

 

[a] If Alice has a FamilyID and Bob doesn't, then copy the FamilyID value from Alice to Bob;

 

If neither Alice nor Bob have a FamilyID, then generate a new unique ID and populate the FamilyID field of both with this value;

 

[c] If Alice has a FamilyID and Bob already has a different one, then either refuse the transaction or proceed as in [a] (i.e. reassign Bob to Alice's family).

[mikedr]

In real life, it's really just that. The "dirty" of it is that a court has decided in a decision named McKesson that patent attorneys are responsible for tracking related patent applications and submitting relevant Things in any application in the family to the patent office for every other member of the family. So there is no hierarchical relationship. The important thing is, if an application is a member of a family, then we need to do certain things.

Now, if I created a new field called, say, FamilyID, in my table, I'm not sure how the layout stuff would work. At a minimum, I'd want to be able to show all related family members (i.e., all applications assigned the same FamilyID). To assign a patent application to a family, it would have to be done by "select another application to which this one is in the same family". Script?

Looking at your a, b, c, I can do the logic for the script I think. But when you view a record, I'd like to be able to see any related family members, in a portal-like view. Something like:

if no family members or if no other records with same familyid:

show "no related applications"

else

display applications with same family id

[comment]

Viewing other records should be quite simple: use a portal based on:
 
Applications::ApplicationID ≠ OtherApplications::ApplicationID
 
to view all applications, except the current one;

 

 

Use a portal based on:

Applications::FamilyID = SiblingApplications::FamilyID
AND

Applications::ApplicationID ≠ SiblingApplications::ApplicationID

 

to view all sibling applications.

 

--

In case it's not obvious, both relationships are self-joins.

 

 

I am still having trouble accepting that a family has no name, or date, or any other fact to set it apart from other families.

[mikedr]

Great! I will try this out in the next few days. Yes, it's a bit odd. No other attributes other than the fact that they're related. it's a legal thing (sadly).

[mikedr]

Thank you again for your assistance! Got it working, and it works great! Each record has a uniqueID. To relate two records, it assigns the UID of one to the other -- and checks to see if the other itself has any related records, and assigns the same UID to those, too. To remove a related record, I simply give it its own UID again.

[OlgerDiekstra]

So assuming Alice and Bob and John are related, and your using Alice's ID to relate them all together, how would you unrelate Alice? You can't use it's own ID because that's being used as a ParentID.

I think its better to create a parent table, with only a ParentID field, and use Parent::ParentID <-> Sibling::ParentID to create a relation.

[mikedr]

This is what I ended up doing. When you add another record to be related, it assigns the current record's "related ID" to that record, or to any other record which with the new record is already related. If you remove a record that is related, a new "related ID" is generated for it that is unique.