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.