database

Relational data

Updated: June 17, 2021

Your Afterpattern database can easily become "relational." 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 database. Next, simply link one table to another by adding a "has many" or "belongs to" datatype column.

Send relational data from an App

You can send data from an app to your relational database. This will automatically populate both the 'has many' and 'belongs to' tables.

In most scenarios, your app is simultaneously populating both the 'has many' and 'belongs to' table. For example, you may have an app that collects information about a parent and their children.

In this scenario, your app will have two send data functions, one for each table it is populating:

Setting up the 'send data' function for the 'has many' table is no different than sending data to a regular, non-relational database. The 'send data' function for the 'belongs to' table requires special attention; specifically, you need to select the correct variable to send to the 'belongs to' column.

You have two options:

  1. The key value for the record that this record belongs to; or
  2. The variable for the 'send data' function that creates the "belongs to" record.

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-record.

The video above shows you exactly how to do this.

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. It's important to start with 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 database toolbar of Table 1 (this allows you to "look up" a row from Table 1 by clicking on that row directly from the database): 

Step 2

Now you can "look up" a row from Table 2, the "belongs to" table, 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 database 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.