Once you start building database-focused apps, you will soon realise that you might need to cross-reference records between tables. While some apps can be built in a way that all their tables are independent from one another, it is more common to have a relationship between them. In this brief post, I’ll show you a quick and easy way to deal with relational data that will be good enough for most use cases.
When the data in one table depends on the data from another table to make sense, you have a relational data set. A good example of is a simple task manager that groups tasks per project. Even though you could in theory do it all in a single table by having the “project” be a key in the table that is filled with the name of a project. Such designs are cumbersome to manage long term. Soon you’ll need to rename projects or add more fields to them and you’ll end up having to rework your data to fit a badly designed spec.
It is better to split the design into multiple tables, one for projects and one for tasks. This article is about how to link those two tables and how to work with such linked data sets.
Designing tables for relational data
Find out which table should be the container for another table. In our example, each “task” belongs to a “project”. So in the “tasks” table, we add a “project” key. Each record in Appli has its own unique identifier (called cdbRecordID in the data modeler). The trick is adding the unique id from a project in the “projects” table to that field in the “tasks” table when creating a new record. Then if we need to find which project a task belongs to, we can simply use that id to get that record from the other table.
Keep a reference to the parent data
The easiest way to work with these references is to make sure your user is creating or selecting the parent record before they work on the related data. For the project manager, the user needs to create or select a project before they can move to work on tasks.
When either creation or selection happens, you enter the cdbRecordID of the parent record into a variable. Whenever the user needs to create a new relational record in a table, you have an easy way to figure out what the parent should be by using the value from that variable.
For example, a variable called “selected_project” set to the cdbRecordID of a project.
How to populate a layout
Using no-code to configure a layout to show multiple records from a table will show all records from that table. That is not what we want when dealing with relational data. We’ll need to populate the layout by ourselves using low-code.
This is easier if the layout is on its own screen and that screen is not the same one that selects the parent record. Once you have your cdbRecordID for the parent record. You can use low-code and the OpenScreen event for that screen to create a small script that executes a query and populates the layout.
How to work with forms
You can keep working with forms just like you do with any other table. Just remember to add a field for the cdbRecordID and link it using no-code. After that, make that field invisible so that your users don’t have a way of breaking the link by typing something there.
If the form is used for editing, that field will be filled with a value from the record. If it is a form for a new record, you will need to remember to fill that value in the OpenScreen event just like with the layout.
There you have it, a very simple way to work with relational data.
