database

Relational data

Updated: October 22, 2020

Your Afterpattern Workbase tables can easily become a relational database. Just connect two tables with a "has many" or "belongs to" datatype column.

How to create a relational database

In order to create a relational database, you must have 2+ tables in a Workbase. Next, simply link one table to another by adding a "has many" or "belongs to" datatype column.

This brief video provides an overview: 

Send relational data from an App

Sending relational data means that your App creates two rows across two Workbase tables, and each row is linked to the other via "has many" and "belongs to" columns.

Requirements:

  1. You must have already set up a relational database (i.e. a Workbase with two tables that are linked with "has many" and "belongs to" columns); and
  2. You have two 'send data' functions in the App you want to send data from, one for each row you want to create.

You can only send relational data from the 'send data' function that creates the row that "belongs to" the other.

For example, imagine a 'send data' function that creates a row in a Children table. Each row in the Children table "belongs to" a row in a Clients table. The key issue is determining what variable to send over to the "belongs to" column:

You have two options:

  1. The key column value from the "has many" row (in this example, this would be the key column in the Clients table); or
  2. The variable for the 'send data' function that creates the "belongs to" row (in this example, this is the 'send data' function that adds a row to the Children table).

The 'send data' function variable is found here: 

Note: like all variables, you should give this variable a meaningful name. In this example, the 'send data' function creates a client; so, I might call this variable creates-client.

Pull relational data into an app

As always, the first step when pulling data into your app is adding a data source.

A data source is equal to 1 table. To pull relational data into your app, you will need at least 2 data sources, one for each table that relate to each other:

Now that you have 2 data sources, here is the most common way to pull relational data from them:

Step 1

Add to your app the ability to "look up" a row from Table 1, the "has many" table.

There are four methods for "looking up" rows in a table (review the four methods). For example, you could add your app to the Workbase toolbar of Table 1 (this eventually allows you to "look up" a row from Table 1 by clicking on that row directly from the Workbase): 

Step 2

Now you can "look up" a row from Table 2 using the Key Column of Table 1.

The most common method of looking up data in this situation is a Query Block (watch demo of Query Blocks): 

The Query Block creates a variable (`list of related children`) that is equal to a list of all rows from your Table 2, the "has many" table.

The Query Block creates this list by performing a filter on your table using the expression you create. This expression instructs the app to find the "belongs to" column (in above example, that column is named 'Child of') and filter for those rows where that column equals the Key column from Table 1.

Step 3

Now you have a variable that is equal to a list of data. Learn how to use lists.

Modeling many-to-many relationships

Want to duplicate the example Workbase and apps used in the video above?

Click here to create you own copy

Contact us for support

Talk to a real person, quickly. We're available 9am - 5pm EST.