Form SQL

Many times you want to display data from the database on a form, but the data you want to show is not a standard Form Control in the new control to add drop-down list in the Form Designer.

Put another way, we have been asked: "Is there a way to create a control that is populated from an SQL statement or stored procedure? So what I would like to do is put a label on the form. When the form is called, some sql will run and the values returned from that SQL will populate the label. I didn't see any mention of being able to do that on the help page. Would it be possible to do that?"

The first three sections below show you three ways to do this. The fourth approach is also a possibility but should be used only in certain cases and could be problematic from a performance perspective.

Add Profile Detail to Profile Page

If the form on which you want to display SQL data is a Profile Form, you can add an additional Profile Detail to the form as a Designed List Menu. A designed list menu can run any SQL statement and then output the results in HTML that you define. See main article for step by step instructions.

XslGenerator Profile Detail

You can add a Profile Detail using the new XslGenerator (iWeb) control. This is the most feature rich way to display any data (from a View or any other SQL tables or a SP) on a profile page.

XslGenerator on Form

If you are on a data entry form (that is, not a Profile Page) and you want to display data from a View (or anyother SQL tables or a SP), you can use XslGenerator (iWeb) as a Form Extension. This is the only way to display any data (from a View or any other SQL tables) on a non-profile page, unless you write custom .NET programming code.

Data Object for Dynamic Facade Objects based on View

Note: This option is powerful but could have severe performance issues if your View is not extremely fast. We generally do not recommend doing this as EVERY TIME the Object loads, then the every column of the view will be selected. This will force a lot of unnecessary processing on the system for a marginal gain.

If you want to show columns from a View on a form, then this case study could work (but please heed the warning above).

If the Object of the Form is not a Static Facade Object, then you can add a Data Object to the Object, point the data object's table to the View, and then design the form and add view columns as Form Controls on the form. If the form's object is a static facade object (such as Individual and Organization), then this option is not possible.

Form Extension

This technique uses a Form Extension to run a database stored procedure to calculate a value, and then that value is rendered onto the designed form. A scalar-based UDF also could be used, or inline SQL for simpler calculations. This technique can be used for data that is not in an ordinary field but must be calculated or retrieved from other parts of netFORUM.

Finished View

The finished view shows the fields balance due and mbr balance. These fields are calculated by database stored procedures and are rendered onto the designed form using a Form Extension.

Create Form Extension

First, add a Form Extension to the Form.

Change the Description and Control ID as appropriate.

Design Form

In the Form Designer, place the extension on the form using appropriate styles

Put your mouse focus on the Extension form control. In the Advanced section of the form control, enter the following. The Value Column and Text Column and Value Where need to be from a SQL SP or UDF that you crate.

Here's an example of the SP: