Demystifying Relationships, References and Proxies and more
Relationships are the core functionality of Quick Base. If you are new to Quick Base or relational databases this concept may be difficult to wrap your brain around at first. However, once you have a strong grasp of how to properly set up relationships your Quick Base building will come naturally to you. This article will break down when and how to properly use a relationship and all the pieces that are involved so you can get the most out of your Quick Base applications.
Before diving into the specifics, it is important to understand the concept of a relationship. A relationship is how we are able to "connect" two tables of data to each other. This allows you to create a workflow in your application, see information from one table(parent), on a record in another(child), as well as summarize important information from one table(child) and have it visible on another record(parent). In this blog we will cover the following:
1. Key fields - unique identifiers
2. Parent vs Child Tables
3. Reference Fields
3. Lookup Fields
4. Reference Proxies
5. Summary Fields
Below is a diagram of an example relationship we will cover throughout this post. Our example will be a relationship where Accounts have many Contacts.
Before we tackle creating a relationship it is important to understand the concept of a key field. Key fields are unique identifiers for each table in your Quick Base application. This is what allows you to relate or "connect" data between two different tables. By default each new table you create in Quick Base uses Record ID# as the key field. Record ID# is a built in Quick Base field that is an auto incrementing number. The first record you add will have a value of 1, the next 2,3,4...etc. Quick Base also allows you to set your own key field. For example, if I had an Accounts table where each record represents a unique account, we may have a field we are already using to track each individual account e.g. an Account ID field. We could set this field as the key for our table. Moving forward when we add a new Account record the Account ID field must be filled in and when the record is saved and it must be a unique value or the record will not be able to save. Setting your own key field can come in handy down the line when we are importing records to a table that is related to our Accounts table. It can also be useful to make sure we are not adding any duplicate records when importing data from a system outside of Quick Base.
Parent vs Child Tables in Relationships
Now that we have an understanding of key fields we can move on to the meat of this article, Relationships. When creating a relationship in Quick Base we need to understand what our data is and how it logically needs to be structured. Let's continue with the example of an Accounts table. What type of information may we want to relate to an Account? How about our Contacts for each Account? Every relationship in Quick Base requires at least two tables be involved. In this scenario we have an Accounts table and a Contacts table. Before we move on it is important to understand the difference between a "Parent" table and a "Child" table. Each record on a Parent table is unique, while there can be many records of a Child table related to a single unique Parent record. When a relationship is created a few things happen.
1. A button is created on the Parent table to add a new Child record.
2. A report link is created, which displays a report of all related Child records, on the Parent record form.
3. A dropdown is created on the Child record, to select/relate the Child to its Parent.
To get started let's go to the settings on your table > table to table relationships > click New Relationship. Once you get to the new relationship screen, you will be prompted to select a table to connect with like the screenshot below.
In this example, I'll select my Contacts table, once I do so I will have two options.
Ok, now we need to choose an option. The first option in the screenshot above is saying One Account(parent) can have many different Contacts(children). The second option is saying One Contact(parent) can have many different Accounts(children). For this scenario the first option makes the most sense. What is important to understand in this step is where you need a button to add a new record and where you need a drop down to relate or "connect" that record to another table.
If we choose the option where an Account can have many Contacts, we will have a button on the Account record to add a new contact and an embedded report on the Account record displaying all the related Contacts for this account.
On the Contact record you will have a dropdown to select the correct Account to relate to your Contact. This will bring us to our next topic, Reference fields and Reference Proxies.
A reference field
, is a field on the Child
table that matches its value with the value of the key field
of the Parent
table. By default when a relationship is created the reference field
is automatically created at the same time. The way Quick Base knows if the Child
record is related to theParent
is if the reference field
value matches the key field
value. For example, if you have a Parent
record with a Record ID# of "1", the reference field
on the Child
will need to have a value of "1" to be related.
A Lookup Field is any field originating from the Parent Record, that you would like to view on the Child Record. All Lookup fields are read-only and are populated when the Child record is related to the Parent. For example in our Accounts have many Contacts relationship, we can lookup any field specific to the Account and view on the Contact record, e.g. the Account Address, Account ID etc.
To create a lookup field, go to your table settings > table to table relationships, and select the relationship you wish to modify. Once you're in the relationship screen you will see the "Add Lookup Field" button on the right hand side.
Note: the first lookup field I created defaulted to the reference proxy, we'll cover what this means in the next section.
A reference proxy is a lookup field that allows you to use another field from your Parent table as the dropdown field. Without a proxy field, when you have a dropdown on the Child record, and save it, it will display the reference field value. For example, if we're adding a contact, select the correct Account and save. Instead of displaying the Account Name it would display the key field value of the Account e.g. 1,2,3,4 etc. In many cases, it makes more sense to display another field rather than our reference field value.
If you do not want the first lookup field added to be the reference proxy or you do not want a reference proxy at all, go to the field properties of your reference field(in this case "Related Account"), under "Reference Field Options", change the Proxy field to the field you would like or use "select a field.." to leave it blank.
Updating the Dropdown for a Reference Proxy
Another important feature of a reference field or proxy, is the ability to determine what fields and records are displayed in your dropdown. By default Quick Base uses the default record picker for your dropdowns. To locate the fields being used for your default record picker, go to your Parent table > Advanced Settings > Default Record Picker. You can also use the "Preview Record Picker" feature to see what the dropdown will look like.
If you've noticed, with the default record picker we can only display 3 fields in the dropdown. If you would like more fields displayed to make sure you're selecting the correct record, you can modify your form to use a report you've created on the Parent table as well. Not only does this allow you to add more columns to the dropdown, it will also honor any filters you may have added to the report as well. For example, with our Accounts to Contacts relationship, maybe we want to filter to only include Active Accounts in the dropdown. We can create a report on the Accounts table, with a filter for Active and use this as our report for the dropdown.
To update the report for your dropdown, customize your form, locate the element with the reference field/proxy, on the right hand side under the section "When used for data entry", it will say "Use the default record picker". From here you can select your report from the Parent table.
Summary fields allow you to summarize data from your related records and display that information on the Parent record. When creating a summary field you will have two options to summarize,
1. The number of records related to the Parent
2. A summary of a specific field.
Note: Both of these options will also allow you to add "Matching Criteria", think of this as a way to filter the data.
For the first option it is a counting function. If you create a summary field, make no changes to the default and click create you will have the total number of related records. For our example, this would be the # of Contacts.
To take this a step further, let's say we have a field on the Contacts table for Priority(High, Medium and Low). We could add a condition to the above so we count the number of high priority contacts. This would look as follows.
For our second option we can summarize data from a specific field. We have the ability to Total a value, determine the Maximum value, determine the Minimum value, Average a value, find the Standard Deviation of a value, determine a distinct count of a value, and Combine the text of a value. Below I'll list out some common applications for these functions.
Total - typically used to sum a value of a numeric field. For example on an Order with many line items, we could total the price of each line item to come up with an order total.
Average - could be used where we have an Accounts have many Orders relationship. We could then average our Order Total field, to gain an understanding of how much the account typically orders.
Standard Deviation - could be used against our Average Order summary to determine the variability of this value.
Maximum - Can be used with numeric fields or date fields. For a numeric field we could figure out what the Maximum value of orders related to that account. For a Date field we can determine when the most recent order for that account was.
Minimum - Can be used with numeric fields or date fields. For a numeric field we could figure out what the Minimum value of orders related to that account. For a Date field we can determine when the first order for that account was.
Combine Text - This will display the values of a text field across your related records in a multi-select text view. An example of this would be summarizing the Contact name of each order related to that account. It will display each Contact name like a multi select text field.
Distinct Count - Determines the distinct number for the criteria you're summarizing. For example, we could use a distinct count on the Title field for our Contacts. This would give us an idea of how many distinct positions our contacts have at their company.
Now that you've had your crash course in Relationships on Quick Base, you're ready to start building!