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.
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 table…”
A relation between two database tables can be created in the “Relations” tab of the “Field Properties…” dialog (found in the “Database” drop-down window).
Click on “New Relation…” to create a link between two database tables.
First give the new relation a name and then enter under “Primary Record” from which table you would like to pull data into your current record via the relation.
Then 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.
You have a few options here: “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. It is also possible to delete the relation or the record completely, if you choose.
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 should automatically turn relational fields into fixed values.
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 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 “assign 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.
Relations With Explicit Key Fields in Papyrus
Papyrus creates relational links, or relations for short, with hidden internal pointers to the primary record. When working with relations in Papyrus, 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.
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, you can also change the visible relations to hidden “Papyrus relations” and get rid of the explicit key fields. The steps to do this are as follows:
Import the relationally linked tables, one after another, into the same database (DBase tables do not contain any direct information about relations).
In the Papyrus table, open the “New Relation” dialog (“Field Properties” under the “Relations” tab) 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 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.