One of the useful applications of the form model is to build forms intended to maintain database tables. This process generally involves three steps:

1. The first step is to use the db_object_model to build a class which encapsulates the database updates and validation for the SQL tables you wish to maintain.
2. The next step is to arrange the db_object field values on one or more forms.
3. The third step involves linking a form_handler to your form to serve as the interface between the form and the db_object. In many cases, you can use a generic form_handler that is supplied with the framework.

If you have not yet created a db_object class, please follow this link to learn how to create a db_object before continuing with the current help.

A typical maintenance form looks something like this one:

In fact, this maintenance form is likely more advanced than most since it involves an object which includes secondary tables. When using secondary tables, it is necessary to use the db_object_child model to configure the secondary tables and the flexgrid model to allow these to be maintained within the form.

Defining DB Object Maintenance Buttons

Notice the set of buttons shown along the bottom of the page. You are free to define these buttons however you wish but in most cases it will save time to use a standard button form named db_object_maintenance_buttons that is included with the framework. This form is defined within system so it can be shared by any site. Here we can see a screenshot of the form elements.

Notice that none of these fields have any definitions associated with them. This tells us that they are predefined using the html_field model. For example, here is the definition for the display button:

Notice that some of the buttons are implemented using simple field references while others are using dollar functions. Let's break down the use of these functions:

:$if($qsparm(insert),=,false,!,db_object_insert)

We can see that the definition starts with a colon. That is standard for all field references on forms. The difference is that this reference is generated dynamically using nested dollar functions. The nested function in this example is $qsparm(insert). This resolves to the value of the passed parameter named insert. We will see in a moment that this parameter can be assigned with we insert the db_object_maintenance_buttons subpage into our maintenance form. The outer function is $if. This function says:

if the insert parameter resolves to false include !, otherwise include db_object_insert

After this $if function executes there could be two possible outcomes:

1. The cell will contain :db_object_insert
2. The cell will contain :!

The first outcome would be to simply include the field name db_object_insert (which happens to be predefined as a button).

The second outcome uses a feature of the form model whereby "fields" defined as :! are ignored and resolve to empty cells.

The benefit of defining the button form in this way is that it allows this same form to be used in cases where all seven buttons are needed as well as situations that don't call for certain optional buttons. For example, perhaps your maintenance function does not support insert or delete. You could still use this same form by including the button subpage using:

 $page(db_object_maintenance_buttons,
insert=false&delete=false)

Without the use of conditional buttons we would need to create nine separate forms to handle all of the combinations of buttons offered on this single form.

Adding Fields to your Form

Here we see the form model definition of the maintenance form shown above:

Please make note of the following:

The form fields are not required to be defined using the html_field model because we reference the generated db_object film which supplies the form with the necessary field definitions. To see these definitions you would need to edit the film db_object. Here we show the columns that are germane to how the primary table fields will be rendered on the form:

Of course the UI Control column determines the HTML control that will be used. The next two columns are combined to determine the formatting and rules that will be applied to the fields.

These default definitions can be overridden on a field-by-field basis as required. For example, we can see that, by default, the rental_duration is implemented using a number control which allows any number from 0 to 999. Suppose, for this particular form, we want this to be a select control offering a specific number of days. This could be done just by changing the control definition on the form to something like 

:rental_duration,type:select,
*select:valid_rental_days

It should be noted that the change made in this example will only affect the client validation (form validation within the browser). The server side validation rules will still be controlled by the db_object field definitions. For this reason, it is always best to change the Shared Parms settings within the db_object definition where ever possible since these parameters serve to influence both the client-side and server-side validation.

Notice that the repeating rows referenced on the form definition do not appear on the screenshot above. That's because the db_object definition has a separate area where child definitions are defined. We see this here:

Notice that the first column refers to the field name. This is what must be placed on your maintenance form (preceded by :).

The last column indicates the name of the flexgrid that will be used to implement the field on the form. Here we see part of the definition for the film_actor_flexgrid.

It is not possible to override these flexgrid column definition within the form itself.

Adding a Form Handler

While the form is capable of presenting the object fields to the user and the db_object is capable of performing much of the validation and all of the database updates we still need something to tie these components together. That's where a form handler comes in. In many cases, you can simply reference a generic form handler that is supplied with the framework named db_object_form_handler. As you can see here, the custom code in this form handler is quite simple with just two methods so if you decide to write your own form_handler from scratch this is usually fairly easy to do.