The Child Parent to Parent Trick

One of the most important ways to leverage the power of QuickBase in streamlining organizational data management is to understand and utilize table relationships.  QuickBase use a very simple but powerful model for creating relationships that relies on the following key principles:

1.     All relationships are specified as one-to-many.  There are creative ways to create other variants of relationships (one-to-one and many-to-many) but QuickBase specifically structures all relationships as one-to-many.  For people new to relational data, this means that a parent record such as a project has many children (tasks) and, vice versa, a task has at most one parent (project) to which it belongs.

2.     The method of defining the relationship is to create a Reference Field in the child table  that matches the Key Field of the parent.  The Reference Field can be a pre-existing field or can be automatically generated by QuickBase when the relationship is formed.

3.     Relational data is passed throughout the application via Lookup fields associated with the Reference Field that forms the relationship.  In order for a child record to display data inherited from its parent a Lookup field  must be created in the child table that pulls in the information.

4.     Summary data can be generated in parent records based on numerical information in the child records.  Without special methods, parent records cannot inherit information from their child records but they can output quantitative information such as the number of children, the last date a child record was created, etc.

MAKING A CHILD A PARENT

In certain circumstances the application model may require that a parent record inherit information from its child records.  Some examples where this might happen are:

-        A company record needs to have a primary contact and that contact’s information needs to be displayed on the company record.

-        Certain information pulled from an external source is loaded into a child records as a result of the structure of the source data but is really data that needs to be displayed at a parent level.

-        Approvals for tasks or other processes need to be tracked and the final approver name displayed at the task level.

The method for achieving this application structure is achieved by setting up the following tables and relationships.

1.     Create the parent table.

2.     Add  the child table

3.     Create the parent child relationship and make sure that the key field is either the default (Record ID#) or is a numeric  field.

4.     Add a summary field to the parent record that summarizes the key field of the child table.  Selecting the right summary is very important and may require some additional criteria.  For example, in the event that you want to set up a primary contact for a company you will need a checkbox or other identifier on the contact field so that you will know which contact is the primary.  The Summary Field should then be set to the Maximum of the Key Field and filtered for only those contacts checked as primary.

5.     Set up a relationship from that initially designated parent to its initial child, but in this case set the child table as the parent.  Be sure to designate the Reference Field as the newly created Summary Field.  You can then pull through any Lookup Fields that you need.

This simple way to “trick” a QuickBase relationship is just one example of the many ways that creative application architecture can be used to leverage information throughout your applications.  One strong recommendation when designing any application is to think through the architecture carefully before beginning.  Tables that have non-numeric key fields can’t be used as child tables in this kind of special case so it’s best to know early if your design might need a child record to sometimes function as a parent to its parent.

I realize that this can be a bit of a mind twister, but trust me it works, and creates a very powerful way to add leverage to your existing data.

 

Mike Hansborough is a Senior Partner at MCF Technology Solutions and a 20 year veteran of using IT to support process improvment. MCF is a SaaS company, using Webware platforms, like QuickBase, to create Applications that enable a wide variety of Business Process Redesign (BPR) efforts from Financial Servies to International Supply Chain Management. MCF also coined the phrase “spreadsheet abuse” and has helped companies, from small NPOs to Fortune 100, with their “recovery”. Find out more at spreadsheetabuse.com and mcftech.com
 
  • Ben

    Funny, that link to spreadsheetabuse.com goes to http://www.quickbaseabuse.com…address not found.

    [Reply]

  • Ben

    Funny, that link to spreadsheetabuse.com goes to http://www.quickbaseabuse.com…address not found.

    [Reply]

  • Philip Gross

    Thanks, Ben. I fixed it.

    [Reply]

  • Philip Gross

    Thanks, Ben. I fixed it.

    [Reply]

  • http://www.cictr.com/ Tim Rowe

    Mike,

    I have been trying to figure out a way to solve this problem for YEARS! Thank you for this tip. I presume by the fact that the QB team has seen fit to publish this gem that at some level this is a supported method. Wow!

    Tim

    [Reply]

  • http://www.cictr.com Tim Rowe

    Mike,

    I have been trying to figure out a way to solve this problem for YEARS! Thank you for this tip. I presume by the fact that the QB team has seen fit to publish this gem that at some level this is a supported method. Wow!

    Tim

    [Reply]

  • http://www.tower-defense-game.com/ Tower Defense

    I think this article was a great.
    Easy to follow!

    [Reply]

  • http://www.tower-defense-game.com/ Tower Defense

    I think this article was a great.
    Easy to follow!

    [Reply]

  • Samantha

    Mike – This doesn’t quite help me out..nor does the suggestion of creating a third table. It’s sooo close though.

    I’ve got two tables, call’em “tasks” and “files”.
    On a form for a Task (create/edit/view) I would like to display the following info:
    - all ‘files’ associated with this task
    (easy enough, created a one-to-many tasks==>files relationship, and embedded a mini-report in the form)
    - all other tasks ALSO associated with each listed file

    ** I’ve made the filename in each “FILE” record a multiple choice, so that users can select existing records.

    I’ve created some additional relationships to no end.. I can’t get a handle on the ‘many’ tasks associated with a file.

    Any suggestions would be most welcome.

    thanks!

    [Reply]

  • Samantha

    Mike – This doesn’t quite help me out..nor does the suggestion of creating a third table. It’s sooo close though.

    I’ve got two tables, call’em “tasks” and “files”.
    On a form for a Task (create/edit/view) I would like to display the following info:
    - all ‘files’ associated with this task
    (easy enough, created a one-to-many tasks==>files relationship, and embedded a mini-report in the form)
    - all other tasks ALSO associated with each listed file

    ** I’ve made the filename in each “FILE” record a multiple choice, so that users can select existing records.

    I’ve created some additional relationships to no end.. I can’t get a handle on the ‘many’ tasks associated with a file.

    Any suggestions would be most welcome.

    thanks!

    [Reply]

  • Brian Beasley

    I would like to avoid having to manually connect each record back to the child (formerly parent) table. Doesn’t this require that your child (currently parent) table contain a unique record_id for each entry that relates to a unique record_id in the child (formerly parent) table?

    [Reply]