Table links—the key for the right query
Table links are fundamental for query building. You will see some different links in this section, but the most common links will be discussed in the next section because there are too many and they are used too often.
To understand table links, you need to know more about table structures.
Primary key
Every table has a primary key. Some of the tables have foreign keys too. All those keys are used for the index. Docentry is a typical primary key to link OXXX with XXXn document tables. For example, Docentry is a common key field to link OPOR with POR1, POR2 to POR12.
A primary key can be one or more fields. For a simple table one key field would be good enough. For a complicated table, two or more fields for primary key are not rare.
A primary key has to be unique within the same table. This key will not allow NULL value—that is, an empty field or a field with no data.
Foreign key
A foreign key is usually used to link to some other table's primary key. This field will be updated whenever the other table record has changed.
Although, you could link any fields between tables, if the field is not NULL, you should try to use key link wherever possible in order to increase the database performance.
Example of table links within SAP Business One
To be clearer about the link, here are a few table link examples:
- OITM-Items table and ITM1-Items Prices table:
These two tables are linked through ItemCode field. Both tables have the same field name to link. It is not one-to-one but one-to-many relationships. One Item Code in item master may have more than one item price associated.
- OITT-Product Tree table and ITT1-Product Tree Child Items:
These two tables are linked through Code field in OITT and Father field in ITT1. These tables are used for Bill of Materials.
- OCRD-Business Partner table and OSLP-Sales Employee table:
These two tables are linked through the same name field SlpCode. In the second table, SlpCode is the primary key for OSLP. On the other hand, it is a foreign key in the first table OCRD.