Lognormal

This chapter explores form design further, including sections on forms related to multiple tables, on creating menus and on writing C/AL code in triggers.

· Main Forms and Subforms

· Looking Up Values and Validating Entries

· Drilling Down to the Underlying Transactions

· Launching Another Form

· Designing Menu Buttons

· Form and Control Triggers

· Form Types and Characteristics

Chapter 11. Extending the Functionality of Your Forms

186

11.1 M

F

S

A well-designed database does not store redundant information, but has a number of relationships between tables. The typical relationship is a one-to-many relationship.

For example, if you are designing an application that handles sales orders, there can be many items on one single sales order, but one specific item can only be part of one sales order. Some of the information on a sales order, such as the address of the customer, is per order, while other information, for example the item number, is per item. In a well-designed database, with no redundant information, this means that the information on a sales order is stored in two tables: one, a header table, with the general order information, the other, a lines table, with the information about each item. There is a one-to-many relationship between the tables.

However, users of the application need to view information from both tables at the same time.

The header information together with the lines could look like this:

Although this looks like a normal form, it is actually two forms.

The main form is the one side of the one-to-many relationship. In this example, it is based on the Sales Order Header table. The subform is the many side of the relationship and is based on the Sales Order Line table. When you select a sales order header in the main form, the subform is updated to display only sales order lines that are related to this sales order header. There is a link between the main form and the subform to keep the information synchronized.

11.1 Main Forms and Subforms

187 Designing the Main Form

There are no special procedures involved in designing the main form in a main form/subform relationship.

To create a subform, you add a subform control. The subform control establishes the link between the main form and the subform, but it is not itself a form. You can, however, display any form in the subform control.

If you are going to use an existing form as the subform, follow the procedure

described here. If you are going to create a new form to use as the subform, it may be more convenient to create the subform first. This is described in the next subsection.

To create the main form in a main form/subform relationship:

1 Open the existing form in the Form Designer.

2 Open the Toolbox and select the Subform tool.

3 Click and drag in the design area to create the subform control.

4 Open the Properties window (SHIFT+F4) of the subform control.

5 Enter the name of the form that the subform is based on as the SubFormID property (or use the lookup button to select the form from a list).

6 Enter the expression that links the two tables (for example the field that is common for the tables) as the SubFormLink property. There is an assist-edit function available to help you (click the AssistButton k to open the assist-edit window).

Select the field name from the many side of the relationship (the subform table) as the Field. Then select FIELD as the Type of the relationship. Finally, select the field from the one side of the relation (the main form table) as the Value.

7 In the SubFormView property, you can specify the key, sort order and table filter to apply to the table when it is displayed in the subform. (You do not have to enter anything.)

EXAMPLE

In the SubFormLink property, apart from FIELD you can select other types of link. If you choose CONST, Value must be a constant expression that selects records where the Field matches this expression. If you choose FILTER, Value must be a filter expression, for example, 10|30..40.

Designing the Subform

There are no special requirements for a subform. However, you must remember that the form is going to be used to display the many side of a one-to-many relationship, and that all forms are not suitable for this task.

Typically a subform is a tabular form, that is, a form with a table box. The table box should fill out the form completely, and the HorzGlue and VertGlue properties of both the table box on the subform and the subform control on the main form should be set to Both. This ensures that the subform and the table box are resized when the main form is resized.

Chapter 11. Extending the Functionality of Your Forms

188

Hints and Advice

Although creating a form with a subform is no different than creating controls on forms in general, you may have to experiment before you find the best way to do it.

Here are some hints and advice to help you along:

· It can be difficult to get the sizing of the subform control on the main form and the size of the subform right. You should finish the design of the subform first. Get the values for the width and the height of the form from the Properties window of the subform. Then, in the main form, click and drag a subform control of any size. In the Properties window, insert the width and height of the subform as the width and height of the subform control.

· Generally, if the subform is a tabular form, it looks better if you let the table box completely fill out the form vertically. This ensures that there is no extra space around the table inside the subform control. Set the HorzGlue and VertGlue properties to Both.

· If the subform is a tabular form, you should set the size of the form so that it only displays a few records at a time. Then, in the main form, set the VertGlue and HorzGlue properties of the subform control to Both. You can resize the main form vertically and horizontally, and the subform is resized along with it so that either more or less records and fields are displayed.