Form Data SQL View Tab

You have the ability to create other database views from the Form Definition's Form Data SQL View tab.

When you create a Form definition in Process Director, there is a tendency to think of the Form instances as being stored in a table, with each Form field as a column, and each row containing a Form instance. In reality, though, the database schema is far more complicated than that. Yet, there are many times when you wish to use tabular data from a Form to use elsewhere in Process Director. Creating a View from the Form definition will return the Form's data in a tabular format, and enables you to choose the specific fields you wish to return in a dataset.

The Create new or modify an existing SQL view section enables you to select whether to create a new view, or to select an existing view to modify or, by clicking the red "X" icon, delete. This section was added in Process Director v5.44.900, to enable you to more easily create and manage multiple views from the same form. For Process Director v6.0.100 and higher, views listed in this section are presented alphabetically, by name.

Views that you created in Process Director prior to upgrading to v5.44.900 will appear in a separate section, labeled as Legacy SQL Views, and will display those views separately. If no views were created, this section won't appear at all.

You can create the view you desire by checking the field names in the Fields to use in view section of the screen, specifying the View Name, then clicking the Re-Generate View button. to create the appropriate SQL syntax for the view you desire.

The Use NO LOCK on the SQL View Generated property, when checked, will prevent locking on the form or Case instances. This may result in partially saved form or case data being returned when selecting data from the view.

The Only Include Forms that have been submitted checkbox will, when checked, return only data for forms that have been submitted. If this property is unchecked, all form instance data, including data contained in forms that have been saved, but not yet submitted, will be returned.

Once you click the Re-generate View button, the appropriate SQL syntax that creates the view is automatically written into the SQL Create View Command section of the screen.

You can customize the SQL syntax as you desire, or you can simply accept the default SQL command that was created when you clicked the Re-generate View button. When the SQL syntax creates the desired view you wish to create, click the Create View button to automatically create the view in the Process Director internal database.

When the view is created, an editable SQL delete command is created in the SQL Delete View Command section. You can run this command by clicking the Remove View button, which will delete it from the Process Director internal database.

Once you've created the view, it is accessible in Process Director through the database connector. This means that you can use the views to create dropdown fills from the database, or use the view in reports.

For users of Process Director v4.51 and higher, you have the ability to use the cached data for a form, if the form is configured to use the Form Data Caching feature. The View will return only the cached data, not the most recent data, but may run significantly faster than running it against the tblFormData table. To implement this, check the Use cached tblFormData table for SQL VIEW? check box. For more information, please see the Form Data Caching topic.

As of Process Director v4.45, the SQL view that is generated for a Form definition casts the field type for integer or checkbox fields to the "int" data type.