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 uses a very simple but powerful model for creating relationships that relies on a few key principles. Once mastered, these basic techniques can be used in creative combinations to achieve almost any manipulation of data necessary. This article will review these basics and then show how they can be applied to accomplish something that may seem impossible with the secret technique known only as the Parent to Child Trick:
THE BASICS: KEY PRINCIPLES OF RELATIONSHIPS IN QUICK BASE
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 one Parent record (called the “Master” record in Quickbase) such as a project has many Children (or “Detail” records in Quickbase) such as tasks under that project. We often see this graphically represented in an Entity Relationship Diagram (ERD) such as this:
The reverse is of course true, that a child (task) has at most one parent (project) to which it belongs. Thus the nature of one-to-many relationships, created between Master and Detail tables (see diagram below) or Parent-to-Child, as we will call them in this article.
This is a view of the Master (parent) table on the left and the Details (child) on the right in the Quickbase relationship setup dialog. Notice the universal one-to-many symbol at the top:
Note: the reason I am not using the Master and Detail names here is because a lot of other databases and schools have used the Parent and Child taxonomy and it also works as a visual analogy as well. Human Children share one parent and all inherit attributes from that parent just like tables do in a one-to-many relationship.
2. The method of defining the relationship is to create a Reference Field in the child table (Related Project in the example above) that matches the Key Field of the parent (Record ID# in the example above, notice the gold colored label “(key field)” next to it. The Reference Field can be a pre-existing field or can be automatically generated by Quickbase when the relationship is formed like in the example above “Related Project” how catchy.
3. Once you have a relationship setup in Quickbase it can be used to move some data around automatically. This “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. Lookups get an automatic name from Quickbase when you create them that shows the parent they come from, but you can also change it to suit your needs like any other field. See the image below. The Add Lookup Fields button is at the bottom right and 4 existing lookup fields are circled in red. Notice that each field name is followed by it’s field type and the indicator (lookup) to make this very clear.
4. Summary data goes the other way. Summary fields can be generated in parent records based on numerical information in the child records. (Only numbers can be summed up, Averaged, have a Max value and a Min value – so only numeric type fields can be Summarized). See the image above, the Add Summary Field button is on the bottom left and the (summary) fields are listed on the left “parent” side of the relationship. Summary fields have another important setting that allows you to set conditions so that you only summarize a subset of all the child records, for example only add up the number of hours on all my tasks where the status is “completed”. This is very powerful and can help you quickly do simple summary math on exactly the set of child records you want. See the image below to see how this example condition is set when you don’t want to summarize all the child records. Notice the 5 types of summary perations circled in red, you can choose from. In this example we are choosing to Total all of the actual hours. Later we will see how the Maximum operation can be very useful in identifying only the largest value that meets your condition.
Sometimes you run across a situation where you want to lookup some data from a child record up to the parent record. But wait you say, that is backwards, you can’t do that. You are correct. Without the special method we are going to teach you hear, parent records cannot inherit information from their child records, they can only get SUMMARY data such as the number of children, the last date (maximum) a child record was created, etc.
THE GOOD STUFF: MAKING A CHILD A PARENT
So what do you do in those certain circumstances that 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 last note under a Lead needs to put displayed in the Lead report.
- The project manager name from a child table with all the project team member records needs to go up to be on the Parent project record for reports, etc.
The method for achieving this application structure is achieved by setting up the following tables and relationships with the 5 Steps below..
Step 1. Create the parent table if not already there.
Step 2. Add the child table too
Step 3. Create the normal parent child relationship and make sure that the key field is either the default (Record ID#) or is another unique numeric field. Important: it MUST BE A NUMBER! (hint you can only summarize number fields, hmmmmm you say)
Step 4. Next 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 if you need to set a condition in the summary field. Your ultimate goal here is to use the Summary field to isolate just ONE child record by its key field. So often Maximum and Minimum are used, as they guarantee only ONE child will be identified by that Summary field. Here are a couple examples:
- 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 (usually the record ID#) and filtered for only those contacts checked as primary. (see image below) This isolates the last (maximum) contact record (child) that has that primary checkbox checked. So lets give that new summary field a name: “Maximum Primary Record ID#”
- In the event that you want the latest note to show up on the parent as in our sales lead example you have to do an extra step in order to dynamically get a checkbox on one of the note records. So, first, you would summarize the maximum datetime created of all the children, then create a lookup field to send that value back down to all the “children” notes (remember that is what lookup fields do, send parent data to all the children). Then use a formula in the note record to identify which record is the latest one at any given time. I like to use a checkbox formula because it’s easy to troubleshoot. Simply make your
formula be true (i.e checked) whenever that max date that you looked up from the parent equals the create datetime of the record. Now you have one single record with a check box and you do the same remaining steps as our
first example, where the primary contact was checked. The Summary Field should then be set to the Maximum of the Key Field (usually record ID#) and filtered for only those contacts checked as primary If that seems complicated, it gets a lot easier after you do it a few times, just remember your goal is always to have some identifier (like a checkbox) to single out the ONE child record from which you want to bring some data up to the parent. Sometimes additional steps are required but it’s always that goal first. Then on to the final, special step.
Step 5. We will continue from the example Step 4.1 just to keep things simple. Set up a new relationship between the original parent to its initial child, but this time set the child table as the master or “parent” (in other words the child table should be the one and the parent should be the many side of the new relationship).
BEFORE you save that new relationship, here comes the secret, be sure to designate the “Numeric (Reference) Field” on the right side of the relationship dialog as the newly created Summary Field from step 4. Let that sink in. So if our summary from step 4.1 is called “Maximum Primary Record ID#”, it would look like this:
One the reference is set, you can then pull through any Lookup Fields that you need from the “primary contact” up to the company record, such as their name, phone #, address, email, etc.
Looking for Quickbase Relationship? click the button below.
Now you can see that the original Parent, Companies in this example, has become the Child.
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. MCFTech often uses this technique and other more advanced ones to help our clients get the most value out of the apps and Quickbase Subscription. I hope this helps many of you out there to get the job done right!