Relationality allows you to create relationships between pieces of data and to link data from different database tables together.

A relational database is for example often used for invoicing, a system to write invoices.

Creating Relations

Relations are created between fields in multiple tables that are related to each other within the database. A new table can be created for a database in the menu “Table” under “Change/Add Tables…”

A relation between two database tables can be created in the “Relations” tab of the “Field Properties…” dialog (“Database” menu).

Click on “New Relation…” to create a link between two database tables. This will open the “Create New Relation” dialog.

First give the new relation a name (“Name of the relation” field). Then select which table you want to link to from the “Primary table (source)” drop-down list. The data will be pulled from this table into your current record via the relation.

Under “Deletion of a linked primary record:” you need to decide what will happen when referenced data that is linked to another record is changed or deleted, because the table that is supposed to use this data will then be blank in this spot.

“Not allowed” means that it will not be possible to delete data that is used in a relation. The other options are: “Automatically turn relational fields into fixed values,” “Remove relational link” and “Delete relational record.” There is also a checkbox for whether you require a prompt for this action.

Lastly, you can decide what will happen when a record is double-clicked in the primary table view:

Sometimes it makes sense to tell Papyrus Base to open the entry form of the primary record one more time so that you can check to make sure everything is correct. If, however, you are sure and do not want to see the entry form again, you can simply choose “Assigns the link.”

There is also a checkbox that you can select if you would like to receive a prompt before modifying a primary record with a relational link to other records.

Back on the “Relations” tab of “Field Properties,” under “Locate a primary record,” you can set whether Papyrus Base should search “Initially in primary field only” (meaning the field that you have “created” for the relation) or whether it should carry out a “full text search” in all fields of the whole record.

Then you can decide whether the user should be allowed to “Overwrite the result,” whether the relations should be dynamic and whether Papyrus Base should automatically turn relational fields into fixed values. “Forbidden” means that it will not be possible to delete data that is used in a relation. A good alternative here is the “Only by choosing ‘Overwrite relation‘” which will overwrite the data used for the relation in the record that has been using it before it is deleted. The other alternative is “Always, relations must be created explicitly.“

Finally, decide the “Link type:” Do you want it to be “Dynamic: Adapt to changes of the primary field” or “Static: Store fixed value after setting the relational link“?

Relations With Explicit Key Fields in Papyrus Base

Papyrus Base creates relational links, or relations for short, with hidden internal pointers to the primary record. When working with relations in Papyrus Base, you will not be using any alphanumeric key numbers (visible) like you would with most other database software.

This is not the case, however, if you import an existing database that already uses relations with explicit key fields. If you are working with such a database, you will need to use those key fields for relational links in Papyrus Base.

Under “New Relation” (“Field Properties” in the “Relations” tab) you can set whether you would like the relations to be hidden, or whether they should use explicit key fields. If you choose the second option, you will need to select the key fields in the primary table and in the table you are currently working on.

If you want to import a database and continue working on it only in Papyrus Base, you can change the visible relations to hidden ‘Papyrus Base relations’ and get rid of the explicit key fields. The steps to do this are as follows:

Import the relational linked tables, one after another, into the same database (DBase tables do not contain any direct information about relations).

In the Papyrus Base table, click the “New Relation” button to open the “Create New Relation” dialog (“Database” → “Field Properties” → “Relations”) and define the relation with the appropriate key fields.

Save the changes by hitting the “OK” button.

Then you can delete the key field in the Papyrus Base table. You will see a prompt telling you that the relation will be changed to a “Hidden” relation. Lastly, click “OK” (this will not delete your relations).

After that you can change the key field in the primary table, since you do not need it anymore.

If you would like, you can keep the explicit relations, but then it is best to make the key field in the primary table unique. This option can be found in “Field Properties” under the “Checks” tab. Later, if you try to enter a value that already exists, you will receive a warning.

The “Checks” tab also contains the option “Automatically generated key field” which will create a new key field for every new record. Automatically generated key fields cannot be edited.