Jump to content

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


mikedr
 Share

This topic is 2919 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 . . . . .

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2919 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.