To create a table, open the Object Designer and switch to the Table view; press the Table button in the left pane of the designer window. In the Table Designer, we create the new table by describing its fields line by line with the following metadata values:
- Enabled: Indicates whether this field is created in the corresponding SQL database table. If the field is disabled, it is not mapped to the database. The default value of this option is true, and we are not going to change this.
- Field No: As with any other object in NAV, fields are identified by their numbers, along with names. This is an internal identifier that does not affect data storage. The only requirement is that this ID must be unique within the table.
- Field Name: Human-readable field name that clearly reflects its purpose and is used in C/AL code as the field identifier.
- Data Type: Type of data that will be stored in this field. We will familiarize ourselves with the most essential data types in this chapter.
- Length: Applicable for text data types (text and code) and binary data. The maximum length of the string that can be stored in this field. Maximum possible length is 250.
- Description: A text comment describing the field.
- Field Class: This is an option that can take one of three values: Normal, FlowField, or FlowFilter. Only Normal fields are stored in the database, and these are mapped to table fields in SQL. The two other classes are special types of fields supporting dynamic data aggregation. We will learn how to use these fields later in this chapter.
This is not a complete list of field properties, but only the values shown in the table designer interface with default settings. To access other properties, select the field and choose the View | Properties menu action, or press Shift + F4.
Let's create the first of the tables required for the leasing payments solution. Propertied Enabled and Field Class always receive default values, and other properties should be filled in as shown in the table:
Table 50500 Lease Contract Header:
Field no. | Field name | Data type | Length |
1 | No. | Code | 20 |
2 | Customer No. | Code | 20 |
3 | Starting Date | Date | |
4 | Ending Date | Date |
When you are done defining the table structure, save the table.
The second table, Lease Contract Line, will store document lines. It is defined the same way as the header table: create a new table and the following fields to the table design:
Field no. | Field name | Data type | Length |
1 | Contract No. | Code | 20 |
2 | Line No. | Integer | |
3 | Item No. | Code | 20 |
4 | Description | Text | 50 |
5 | Amount | Decimal |
The first field of the Contract No. table will refer to the contract header. Further on in this chapter, we will see how to set up this relation. Line No. identifies the particular line of the contract, and Item No. is the item in the lease.
Besides the contract header and its lines, we will store the log of customer payments associated with contracts. For this purpose, we are going to create one more table, Customer Payment. In the table designer, create a table object with fields listed as follows:
Field no. | Field name | Data type | Length |
1 | Entry No. | Integer | |
2 | Contract No. | Code | 20 |
3 | Contract Line No. | Integer | |
4 | Payment Date | Date | |
5 | Amount | Decimal | |
6 | Invoice Created | Boolean | |
7 | Global Dimension 1 Code | Code | 20 |
8 | Global Dimension 2 Code | Code | 20 |
9 | Customer Group Code | Code | 20 |
10 | Salesperson Code | Code | 20 |