Enterprise Forms

The complete list of Enterprise Forms tokens

Fri, 07 Nov 2008 09:33:12 GMT

How to: Manage Database Queries

Mon, 25 Aug 2008 04:52:00 GMT

Continuing from previous article, Managing External Database Connections, this article describes the process of Managing Database Queries.

What Is a Database Query?

In Enterprise Forms a Database Query is basically a way of providing data interactions between forms and database objects (tables, stored procedures and raw sql) as defined through supported external database connections. Once defined, they are used in forms as specialized tokens with its own namespace. For example, say we have a database table called “Users” and wish to retrieve data from a column called “username” we could define the Database Query such that when used it is accessed as simple token “[USERS:username]”.

A Database Query is specific to each form template, thus a database query created in one form template cannot be accessed from another form template. A form template, however, can have as many database queries as required provided the namespaces defined in each query is unique.

How to create a Database Query

Unlike creating Database Connections, creating Database Query does not require Host or Super Users access to the system. As long as the user has Form Creator access to the form template, the user is able to create database queries.

Using the above example we wish to create a database query that accesses data from “Users” table located the Current DNN portal database. This also assumes we have already created a Database Connection for the Current Database.

In the Form Template Details, click on Database Queries icon from the top navigation bar to open the Edit Database Query view in Add New mode
Select a Database Connection from list of available External Database Connections. Refer to here for more information on how to define External Database Connections
Provide a Token Namespace that is unique to this database query. A token namespace is a way of defining the database query as well as how it could be access when used. Thus it is possible provide any text (Alphanumeric only) in this field property, the Token Namespace have to be a unique name for the form template. Thus you cannot define two queries with “USERS” as the Token Namespace. Any violation if this rule results in an error message being generated. In our particular example enter “USERS” as the Token Namespace property.
Select a SQL Type from the list of available types available. Currently there are 3 available supported SQL Types; Table, Stored Procedure and Raw SQL. Depending on the selected Database Connection and security access granted, Stored Procedure and Raw SQL may not be available. In our particular example, select “Table” as the SQL Type.
Enter the SQL Text associate with the selected SQL Type. The SQL Text depends on the selected SQL Type. If the selected SQL Type is
- Table: SQL Text must contain the name of a database table (or view) available in the database. E.g “Users”
- Stored Procedure: SQL Text must contain the name of the Stored Procedure and any associated parameters of the stored procedure separated by space. E.g. “myStoredProcedure ‘param1’, ‘param2’, ‘[QUERY:tabid]’”
- Raw SQL: SQL Text can contain any valid SQL syntax supported by the database.
NOTE: An important note is that SQL Text supports Dynamic Tokens within the text. For example you want to pass in the current userid to a stored procedure called “myStoredProcedure”, the SQL Text would be something along this line “myStoredProcedure ‘[USER:USERID]’” (without the outer double quotes). When executed, the system will translate the userid token to the id of the current logged user. This provides a very powerful way dynamically access data.
To Preview data from the provided Query, click on Preview Sample Data link.
Click on Update to apply changes. Note that before changes could be updated, the Query is literally validated for errors, thus you will need to ensure that the database must be online and database query are valid before the system will create the Database Query definition.
Once the Database Query has been created is can be used in anywhere in the form that support dynamic tokens.

How to use Database Query tokens

The real advantage of Database Query is how easily it can be accessed and used in forms. Like any other supported tokens, Database Query is accessed via the standard token format, tokenspace and property name such as [NAMESPACE:PropertyName]. “NAMESPACE” is the Token Namespace defined when creating the Database Query, and “PropertyName” is the column name from the return dataset of the query. In our example to access the “username” column we simply call the query as “[USERS:username]”.
NOTE: By default the token accesses only the very first row of the returned dataset. Since version 4.1 it is possible to access data other than the first row using a more advanced syntax.

[NAMESPACE:XXX|YYY]
Where XXX is the row index of the returned dataset starting from 1
and YYY is the columnname or column index of the returned datset.

For example

[USERS:2|username] accesses the username column from the second data data row
[USERS:1|username] is equivalent to [USERS:username]

How to: Manage External Database Connections

Wed, 23 Jul 2008 14:25:00 GMT

Enterprise Forms 4.1 introduces a new feature called External Database Connections (EDC), which essentially provides the ability to connect to external databases other than the default portal database. Not only it is possible to connect to other Microsoft SQL databases, EDC also support other database servers such as Oracle, MySQL, VistaDB, PostgresSQL and of course Microsoft Access.

This article discusses the process of enabling and configuring External Database Connections in Enterprise Forms. To setup External Database Connection you will need to

Activate External Database Connection feature for the portal
Add database connections and associate with form templates

Activate External Database Connection feature

To use EDC the feature needs to be enabled via a configuration settings which only a Host or SuperUser can have access to. The EDC feature is a per portal setting which needs to be enabled or disabled for each portal Enterprise Forms is being used. By default EDC is disabled and is required to be enabled by a Host user.

To enable EDC for the current portal

Log in as Host or SuperUser
Go to a portal page where an instance of the Form Template Manager module resides
From the module's menu select "Advanced Portal Features" menu item. Advanced Portal Features menu item is only visible/accessible in Page Edit mode
In Advanced Portal Features view, expand the Database Connections and Queries section and check the External Database Connection property.
Click on Update to enable changes and enable External Database Connection for the current portal

NOTE: By default external database connection only support access to data via Direct Table or Stored Procedure. If acces to Raw SQL is required, you will need to individually enable Direct SQL Support for each of the external database. These Direct SQL Support options are only visible if the EDC feature is enabled.

Add/Edit Database Connections

Once External Data Connection has been enabled, the next step is to create new external database connections and assign to a form template. A database connection can be assigned to a single form template or all form templates in the system. Similar to enabling External Database Connections feature, editing database connections can only manage by Host or a SuperUser.

To Create an External Database Connection

Login as Host or a SuperUser
Goto a portal page containing an instance of the Form Template Manager module
Select Database Connections from the module's menu. Note the menu item is only visible when page is in Edit module and External Database Connections has been enabled
To start creating a database connection, in the Template Access drop down list, select a Form Template or (All Form Template) to assign or grant access to the database connection
In the Database Server drop down list, select the type of the Database Server of the target database
Provide a Name for the Connection Name.
Provide a Connection String for the connection. An example Connection String is automatically generated when a Database Server is selected. Simply copy the sample connection string to the Connection String property and replace the sections within the curly brackets {} with parameters relavent to your environment. For additional information on database connection strings, click on the Click here link to obtain more information on database connection strings.
Click Update to create the database connection. Upon creating the database connection, the system validates the connection string by connecting to database server. If the connection fails the system will return an error message and the connection will not be created. Thus make sure the database server is online and active before creating the connection.

NOTE: Once a database connection is created, the assigned form template will be able to use the database connection to create Queries Tokens

Continue to Part 2, Manage Database Query

How to: use Paypal Payment Gateway task

Tue, 01 Apr 2008 08:05:00 GMT

Paypal Workflow Task is an Advanced Workflow Processing task providing a mechanism to integrate form submission and Paypal payment processing.

To use the Paypal Workflow Task, simply select the PayPal Payment Gateway item from the list of available workflow processing tasks. Note that the Paypal Payment Gateway must be enabled via Advanced Portal Features before it could be used.

More...

How to: use Conditional evaluation

Tue, 25 Mar 2008 08:31:00 GMT

Enterprise Forms has a mechanism called Conditional evaluation in which a number of powerful features derive from.

Conditional Databind: the ability to bind data to form fields based on a condition
Conditional Visibility: the ability to hide/show as well as enable disable form fields based on a condition
Conditional Workflow: the ability to move a form to different workflow states based on a certain condition
Conditional Processing Task: the ability to execute advanced workflow processing task based on a certain condition.

More...

Lite Mode vs Standard Mode

Mon, 24 Mar 2008 09:02:00 GMT

The Form Template Manager module come with two designing modes, Standard and Lite mode. The Standard mode provides a full set of features for creating a form template. This of course include the form layout itself and associated workflow meta data (localization, states, access control and workflow). The Lite mode on the other hand only provide the ability to edit the form layout. However there are a number of advantages of the Lite mode over the Standard design mode.

Ready made new template - when creatine a new form template in Lite mode, the system automatically creates a simply form with pre-defined workflow meta data giving beginners access to quickly creating the required form and publish for usage. The pre-defined workflow meta data is a simple 2 states workflow that allow registered users to submit a form from a New to a Submitted state.
Quick approval - In Standard mode, publishing a form template requires that the form template go through a series of approval steps. The steps go from In Development to Pending Approval to Pending Publish before reaching the Available state (published state). When in Lite mode, the form template approval process is simplified to just going from In Development straight to Available without going through the other intermittent steps.

Of course it is possible to switch between the two modes anytime during the form template creation process. To switch betwen the modes, in the Form Template Details screen, select the mode from the module's menu to activate the mode.

How to: Access Data outside of DNN database

Sun, 23 Mar 2008 09:07:00 GMT

Enterprise Forms has the ability to access data (read data via data binding and write data via SQL workflow processing task) from a datasource such as a database table or stored procedure. By default the data source is located within the same database as where the DNN portal resides. However it is posible to access data sources located outside the DNN database provided the database user (used to access DNN database) also have access to the other databases. To access data sources from outside of DNN database, fully qualified name must be specified.

[databasename].[owner].[objectname]

where

databasename: name of the database where the data object resides

owner: name of the owner of the data objects

objectname: name of the database object. Object can be datatable/view or stored procedure

Enterprise Forms 4.0: Instant Publish & Edit of Form Templates

Sat, 22 Mar 2008 08:59:00 GMT

Enterprise Forms 4.0 introduces a new feature where it is now possible to instantly publish a form template without going through the standard 3-step approval proccess.

Previously when creating a form template before it could be used by the other modules such as Data Collection Manager, Workflow Manager and Report Manager modules, the form template needs to be published by going through a standard approval process. The process starts with a state called In Development at which form creators could create and modify the form template. Once the form template design is completed and properly validated, it is moved to the next state of the approval process, Pending Approval. At Pending Approval, only users with Form Approver access can either move it back to In Development or progress it to the next state Pending Publish. At Pending Publish, users with access to Form Publisher role can publish the form template and make it Available for use in other Enterprise Forms modules. Feedback from customers have found that this process can be unecessary for situations where there is only 1 user with all access to create and publish form templates.

Therefore we have introduce the Instant Publish feature in version 4.0, whereby a user with access to Form Creator, Form Approver and Form Publisher could instantly publish a validated form template without going through the standard approval process. In additional, the user could also instantly move a published form template back to In Development at a click of a button. The Instant Publish and Edit is available from the buttons bar located from top of the Form Template Details screen. The Instance Publish button is only visible if the form template is not locked by any user.

Instant Publish button available on the Navigation bar in Form Template Details screen (In Development state)

Instant Development button available on the Navigation bar in Form Template Details screen (in Available state)

Website Upgraded to DNN 4.8.2

Fri, 21 Mar 2008 08:39:00 GMT

Enterprise Forms demo website has sucessfully been upgraded to DotNetNuke version 4.8.2.

How to: use Announcement Workflow Processing Task

Fri, 21 Mar 2008 07:40:00 GMT

The Announcement Workflow Processing Task provides the ability to add an approval process to the core Announcement module.

More...