Quickbase Analytics: Summary Tables
Quickbase Analytics: Summary Tables
This article is the third in the Quickbase Analytics series. These posts outline different approaches builders can use to improve the analytical insights they gain from their Quickbase apps. These posts are geared towards experienced builders as some more advanced concepts are used.
Quickbase’s summary reports are a great tool to aggregate data and gain insights into your business. However, what if you wanted to use summarized data at a record level? For example, we want to compare the company level profit margin % to each individual branch profit margin %. If our Branches table has no parent table, the only way to find the company level profit margin % would be a summary report. The problem is that this number is stuck in the summary report, and we have no way to use the value in formulas at the record level.
The solution to this is a Summary table. In this example, a table that acts as the parent table to the branches table. This will allow summary fields to be created, then brought back down to the child table as a lookup field. Once we have the values as lookup fields they can be used in formula fields on that table. This is a bit of an abstract concept, so let's build out an example.
Building a Summary Table in Quickbase
We are going to use a dataset of movies from IMDB found on Kaggle. This is a simple list of movies featuring some values such as movie title, genre, runtime, revenue, and rating.
The first few steps in implementing a Summary table will depend on if we are starting a brand new app and importing data, or if we have an existing app filled with data. We will walk through both scenarios.
Starting with a new app
The first thing we will do is create our tables (Summary and Movies) and create a relationship (Summary is the parent table). No need to worry about fields at this point.
Next, we are going to add a record to the summary table. Since there are no fields to fill out, we simply click add record then save.
Now are going to update our .csv file of movies and add a column called Related Summary. For every single row we want to put a 1 in this column. If using Excel or Google Sheets , you can do this simply by entering 1 for the first few rows, highlighting those rows, then double clicking the small square in the bottom right corner of the highlighted box. This should autofill a 1 for the rest of the rows.
After this we are ready to import the .csv into our Movies table. Be sure that every single column is set to Create New Field, except for Related Summary which should be set to To Existing Field.
Click import.
Starting with an existing app
If our app already exists and is filled with data (in our case the movies dataset has already been uploaded), our first step is to create a new field in our Movies table with the type Formula – Numeric and label Related Summary. For the formula aspect of this field, simply type in 1 and click save.
Then open the field settings right back up and change the field type to Numeric (not formula, just ordinary numeric). This little trick allows us to update the value of Related Summary for all of our records at once, then changing back to a field type we can use in our relationship (Quickbase restricts the use of formula reference fields).
Go ahead and create the Summary table (no fields needed) and start to create the relationship between Summary and Movies (Summary is the parent).
When we are creating the relationship, Quickbase will ask us to select a reference field. The default option is going to be called Related Summary, but this option will create a new field called Related Summary and not use our existing field. Be sure to click the dropdown option and select the Related Summary option in the list of other fields.
Click Next then Create Relationship.
Navigate to the Summary table and Add a record. Since there are no field you can simply click add then save.
Using our Summary Table
If set everything up correctly, you should be able to view our single record in the Summary table and see that all of the movies in our movies table are displaying as related records.
We are now ready to create some summary fields. I'm interested in seeing how each individual movie compares to the average movie, so I created average fields for Revenue, Runtime, and Rating.
Next we are going to take those summary fields and add all of them as lookups.
This lets us see the average Revenue, Runtime, and Rating calculated by all the movies in our table and use them to compare each individual movie. To do so, we’ll create a few Formula – Numeric fields on our Movies table.
The formulas will all simply be the Record’s Revenue/Runtime/Rating minus the summary average.
Now we can see how each movie compares to the average movie in our dataset!
An important piece to note is that we will need all future Movies to automatically relate to our Summary record. We can do this easily with a Pipeline where every time a record is added, modify the same record so that Related Summary is 1.
Once we’ve set this up, our summary fields will dynamically recalculate. Meaning as more and more movies are added to our dataset, the average revenue/runtime/rating (and whatever other summary fields we create) will automatically recalculate with the new information.
