Database connection

Database connections are the most important part of your portal. This is how you allow portal users to view and edit data from your project's database.

Prerequisite

You must have at least 1 table in your project's database with a defined data schema (i.e. that table must have columns).

How to build a database connection

This is a multi-step process.

First, from the portal page editor, select which database table you want to create a database connection for:

Next, complete the following steps:

1. Edit the database connection settings, including choosing a layout style (tile, single record, or classic table);
2. Create a filter that determines when data is displayed to the portal user; and
3. Build the page itself. Pages are broken into components, including the list page, detail page, and (if applicable) relational page.

Under the settings panel of your database connection, you can edit the following:

Database view label

This is used to label your database connection in the portal (this is optional):

Include app toolbar

The app toolbar is the easiest way to allow portal users to use your apps with their data. Learn more.

Allow user to delete database records

This allows portal users to delete data from your database.

View type

There are 3 different database connection view types: tile, single record, and classic table.

Clean, minimal, easy-to-scan table where the user is required to select an entire row to view more details (vs. being able to edit individual cells):

Displays only one record's worth of data. Great for building profile pages:

The single record layout does not include a List Page.

Special filter rules

The filter for a single record layout must be constructed off of the key column of your database table:

This guarantees that only a single record from your database is displayed through this database connection.

Good for displaying lots of data at once:

The database connection filter determines what data is displayed to the portal user.

You do not have to give every portal user access to all data. In fact, this is rarely the best approach. Instead, you should design filters that result in a portal user viewing only their data.

A database connection filter has three parts:

1. A column from your database
2. An operator
3. A value

Any records from your database that meet the database connection filter's criteria will be displayed to the portal user. For example, here is a filter returns all records where the column 'Full name' equals "Thomas Officer":

What value should you use in your filter?

This is the essential question when creating a database connection; the value by which you filter your data will determine whether a portal user sees everyone's data or just their own. Continue reading to learn how to build a filter that results in a portal user only viewing their own data.

Every portal user has a unique ID represented by the portal client ID variable. You can use this variable to create filters that result in a portal user viewing only their own data.

The trick to building such a filter is recognizing that it means the records in your database table must be associated with the portal client ID variable. To put it another way, if you're not storing the portal client ID variable anywhere in your database table, you can't build a filter that uses it.

Step 1. Create a column for portal client ID in your database table

Add a plaintext data type column to your database table. This column will store the portal client ID variable, so you may as well label it "Portal client ID":

This is the column you will reference in your database connection filter.

Step 2: When sending data to this table, populate the portal client ID column with the portal client ID variable

This requires editing whatever app(s) send data to your database table. In the send data function for those apps, make sure you're populating the portal client ID column with the portal client ID variable.

The portal client ID variable is available by default in all your apps. However, it will only have a value if the end-user runs the app from within a portal. Meaning, you must add the app(s) from this Step 2 to your portal, likely as standalone apps.

Step 3: Filter your database connection using portal client ID

This will be a simple filter with what looks like only two parts, but note that the operator ("equals") includes reference to a value ("portal client id"):

All set 🎉

Now, when the user runs the app(s) you edited at Step 2 above, they will create database records that are associated with their unique portal client ID. These records will then be displayed to the portal user via the database connection.

Most database connections include both a list page and detail page (the single record layout does not include a list page):

• List page: Displayed all records that match the database connection filter
• Detail page: If the portal user clicks on a record from the list page, they'll open the detail page where they can view / edit data only for that record.

The detail page is made up of sections, each consisting of a label (optional) and data organized in either a single column or two column layout:

You can only build the list page after you've built the detail page.

The list page is a summary of records, whereas the detail page is, as the name suggests, a detailed view of just one record. The list page can only include data that is also included in the detail page, that is why you can't build it until you've built the detail page.

After you've built the detail page (read below), follow these instructions for building the list page. There are different instructions for building the list page for a tile layout vs. a classic table layout. The single record layout does not include a list page.

List page for tile layout

You can only display 5 pieces of data on the list page for a database connection with a tile layout (excluding the tile image):

For the tile image, you can use any file data type column from the database table associated with this database connection. Just be sure that image files are stored inside this column (instead of document files).

List page for classic table layout

You do not need to do anything to build the list page for a classic table layout; the list page is created automatically from the data you use to build the detail page.

Start building the detail page by adding a section. You can give that section a label (optional), and choose between single column or two-column layout:

Add data to your section. For each piece of data, select the database table column it should be pulled from and whether the portal user has the permission to view only or view and edit:

You can also choose to show / hide the database column label from this piece of data.

A relational database connection is automatically created when you add the following type of data to the detail page of a database connection:

• The data is from a has many or belongs to data type column; and
• You give the portal user the permission to view and edit that data.

If you do this ^, you will notice a new database connection is automatically created:

You will need to add sections to this relational database connection, following the same procedure as for building the detail page:

If this is checked, any changes made to your database connection will be applied to all existing users.