Relationship Therapy: Deeper Connections for your QuickBase Apps - [PDF Document] (2024)

Relationship Therapy

Deeper Connections for Your QuickBase Apps

Adam Hoover

Sales Engineer, Intuit

#EMPOWER2015

A QuickBase Sales Engineer since 2013, Adam is a passionate supporter of the

Intuit philosophy of making complex processes more accessible. Having worked for

Homeland Security, providing automation support to an organization that is incredibly

large, and also at a startup with less than 50 employees, Adam loves to use his

experience, spanning the full spectrum of industry size, and is always trying to gain and

provide insights to an increasingly diverse community of QuickBase users.

About Me

Adam HooverSales Engineer

#EMPOWER2015

① Back to Basics: The One to Many Relationship

② When Basics Aren’t Enough: Many to Many Relationships

③ Talking Back: Creating Reverse Relationships

④ Help from an Outsider: Using Report Links

⑤ Keeping the Connection Alive: Synchronizing Data

Relationship Therapy

Back to Basics: The One to Many Relationship1

#EMPOWER2015

Relationships in QuickBase are connections between tables of data, allowing those tables to

share information

• The problem of having stand-alone tables comes up when you have information in one

that you know is connected to information in the other

• A person may intuitively understand this connection, but software, like QuickBase, cannot

logically make that connection without being provided with some context

• A relationship allows us to tell software that the data is connected, which in turn helps the

application to give us access to all of the information we need, in the right place

Why do we need relationships?

#EMPOWER2015

Solving the problem of Stand-alone Data

• Here we can see a Project Record which

has plenty of information about a specific

Project and it’s properties

• Even with all the details on this Record,

and the ability to add additional Fields to

track more data, we cannot see any

information from another Record here

What problem does a Relationship Solve?

#EMPOWER2015

The problem can be seen from both tables

• Here we can see a list of people on a

Resources Table that is unrelated to the

Project we just looked at

• These Resources are all available to be

assigned to Projects, but when looking at a

specific Resource, notice that there is no

Project information available

What problem does a Relationship Solve?

#EMPOWER2015

QuickBase uses the concept of a One to Many Relationship

• This is a connection between a Parent table and a Child table, in this case “Projects” and

“Resources”

• Determining which table becomes the Parent, and which the Child, is done when we make

our selection from two possible directions for the relationship

Using One to Many Relationships

#EMPOWER2015

When planning out an application, it is

helpful to create a diagram of the Tables

and their Relationships:

① Add your Tables

② Choose the direction that data will

flow, by placing the Parent on top

③ Draw the Relationship between

tables

Diagraming a One to Many Relationship

Projects

Resources

#EMPOWER2015

① On the Settings Page, find “Table-to-

Table Relationships”

② When creating a new relationship,

select the other table to relate to

③ Choose the Parent and Child

④ Review the results and select the

fields you want to carry over from

the Parent to the child

Actually Adding a Relationship

#EMPOWER2015

The Parent Table will now have a section to view and add any Child Records

Viewing the Results

#EMPOWER2015

The Child Table will also contain detailed information being looked up from the Parent Table

Viewing the Results

#EMPOWER2015

Relationships in QuickBase are clearly capable of solving the issue of connecting data, but

there are still a few problems that a One to Many relationship does not solve

① A relationship does not provide a way to relate Many records to Many other Records

② There is no function to lookup data that exists on a Child Record, from the Parent Record

③ Any unrelated data is not reportable using the fields created by a Relationship

④ Data from external sources or outside applications cannot be aggregated or synchronized

through the use of a One to Many relationship

The Limitations of One to Many

#EMPOWER2015

In order to solve some of the limitations, it is

important to know how Relationships Work

• A field on the Parent table is used as the

“Key Field”

• A “Related” field is created on the Child

table to store the Key Field value

* The Record ID # is used as the default Key, as it is

always a unique value

How do Relationships Work?

When the Basics Aren’t Enough: Many to Many Relationships2

#EMPOWER2015

We can’t relate a Child to many Parent Records

• In the example with Projects and Resources,

Projects can currently have as many

Resources as necessary

• On the Resources table, a new Record is

created each time we assign a person to the

parent Project Record

• This will lead to duplications

The Limitation of a One to Many

#EMPOWER2015

A Many to Many Relationship is a third table

that helps connect data from the two tables

you are already trying to connect

① Add a Third Table to the App, in this

case we can create “Assignments”

② Then create a One to Many relationship

between each of the two tables we are

trying to connect, and the new Table

Diagramming a Many to Many Relationship

Assignments

ResourcesProjects

Talking Back: Creating Reverse Relationships3

#EMPOWER2015

We can’t report on the details of a Child

Record from the Parent Record

• There is a report that shows Child Records

embedded on the Parent Record form

• The report only displays the child records, it

does not carry any of their data over

• A lookup field, which is reportable, can only

be created on the Child Table

The Limitation of a One to Many

#EMPOWER2015

In this Example we will add a Child Table on

which we need to see the “Current” Record

① Add a table and name it “Status”

② Projects will have many Statuses in this

example, as the user may want to track

the history of a projects status, or the

average time spent in a status

Diagramming a Child Lookup

Assignments

ResourcesProjects

Status

#EMPOWER2015

To solve the issue of not being able to see the

“Current Status” at the Project Level, we will

create a Reverse Relationship

① Create a Summary of the Max Record ID

② Create a second relationship between the

Parent and Child table, this time going in

the opposite direction

③ Use the Summary as the Reference Field

Finalizing the Reverse Relationship

Assignments

ResourcesProjects

Status

Summary

Help from an Outsider: Using Report Links4

#EMPOWER2015

There are some circ*mstances where there will be two tables which are handled separately,

but users may still want to be able to look in one place for all the information they need.

• The Embedded report that we saw created automatically on the Projects table when adding

a new relationship, is actually a tool that we can use for this type of situation as well

• While using the Projects table we were working with earlier, if we add a list of Companies,

and a table of Contacts, we may want to see the Contacts for each Company on a Project

• In order to do that, we can create a new Report Link field on the Projects table, which can

display the correct data, even without a relationship

The Limitation of a One to Many

#EMPOWER2015

We will need to match at least one field on

Projects, to a field with the same value on

Contacts, using a third table may help

① Add a Companies Table

② Add a relationship from Companies to

both Projects and Contacts to get the

Company lookup field added to both

③ We can then match those Company fields

Diagramming Report Links

Assignments

ContactsProjects

Status

Companies

Resources

Keeping the Connection Alive: Synchronizing Data5

#EMPOWER2015

When there is data on a table in another application, or even on another platform, there are

situations where we need to be able to take that data and consolidate it in QuickBase

• This allows us to make QuickBase a front end for data, regardless of the origin of that

information

• Whether it is from separate QuickBase applications or from external tools, we can pull that

data into a single QuickBase table

• Once QuickBase has access to bring that data in, it is possible to build reports, dashboards,

workflow, and other Business Intelligence using that information

Why do we Import or Synchronize Data?

#EMPOWER2015

The first way to Import Data, is using a manual Import

① Using the Import/Export wizard in QuickBase, you can Import many records at once by

simply uploading a spreadsheet, or finding another table in QuickBase

② The wizard will ask you for the source data, and then give you an option of where in your

table you would like to put that new data

③ If you are using a Table to Table Import, to grab data from another QuickBase application,

it will store the Import for you, so that you can run it again as necessary

Table to Table Import

#EMPOWER2015

Another strategy to keep data synchronized is to use our new “Sync” tool, which will keep a

“Connected Table” up to date with data from another source

Sync

• Bill.com

• Intacct

• Netsuite

• Salesforce.com

• Zendesk

– More on this in

• Zuora

• Intuit QuickBooks

• Intuit QuickBase

– e on this in the

Sync

presentation

* For more information about Sync, please

join us for the Sync Presentation

#EMPOWER2015

Q&A

Relationship Therapy

THANK YOU