Forum Discussion

SamW's avatar
SamW
Qrew Cadet
6 years ago

How to create 'families' of records within one table?

I have a table filled with people. Some of these people are related.

(this is not my actual data, but I thought it easier to explain than the slightly over-complicated reality).

I want to record those relationships within that one table.

1) So if Matt is Jim's son, I'd want Jim's record to state that Matt is his son, and Matt's record to say that Jim is his father.

2) Then, if I later add Lisa as Matt's sister, I'd want Jim's record to automatically show that Lisa is his daughter.

Does anyone have any ideas about the best way to set this up?

I can get 1. working by setting up a hidden second table and running two relationships to it from my main table, and then setting up an automation so that when I add one record in this hidden table, it creates another record with the inverse information. I'm guessing this isn't the best way?



------------------------------
Sam W
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Re: I'm guessing this isn't the best way?

    i agree, not the best way.
    A table can be related to itself.  So for example I just did the setup for a traditional Bill of Materials.  But in this situation i have one Master table of all the SKUs.  Sme end up being parent and some end up being childrten of parents and then the children might have children so think of this like an Assembly composed of other child assemblies (which have their own SKU)  and then the assemblies either have a child sub assembly or eventually you get down to a component with no children.

    This is no problem for Quick Base to handle, but it is a problem for yourself to get the setup correct and especially to get the fields labelled correctly.

    I my case i set up a Bill of Materials table (BOM) so that I had full flexibility and that is realy what would be the Many to Many join table between the Parents and the children.

    The BOM table will have two relationships to the Master SKU Table.  One will be [Related Parent] with a Proxy lookup field as Parent SKU.
    The other will be [Related Child] with a Proxy set as lookup called BOM SKU.

    Once you have that setup you can have Report Link fields on any record to show either the BOM children, and then another Report Link field called Where used?  That would show the Parents when that BOM SKU is used in.

    Edit (I started out by saying that a table can be related to itself, but having a middle join table probably provides more flexibility in our use cases as you are probably not talking about people but rather parts / SKUs and assemblies, here on this Forum.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------