This demonstration illustrates advanced workflow functionality within Dynamic Forms to create custom applications, custom work flow scenarios, and integration with existing tables where you can insert form data, update form record, and edit/delete the record.

This demonstration also includes a few optional features such as an Advanced CAPTCHA field, the feature to enable the enter key to submit the form, and a javascript message after submission notifying the user of the submission.

Things to Consider

1) You do not have to pull results from a 3rd party module. There is a 'View Results' feature within Dynamic Forms where you can delete results, view a specific set of results, or export the results to Excel.  Please see Demo 13 for more details on the View Results feature.

2) All data is stored within relational database tables, however if you would like to store the data in your own tables you can easily do this with Dynamic SQL Events.  Within this demonstration we are assuming that we would like the data stored in a flat table (and we cover the steps to create this flat table) however you might also use this as an example to manipulate other tables. For example you could utilize the module to update the FAQ'S table, announcements table, or any application table that you might want to use. You might be wanting to keep the data structure in an existing application, but upgrade the interface to maintain that interface using Dynamic Forms.

3) This demonstration is assuming you will either be replacing an existing application, or creating a new application, to store phone book entries. Within this demonstration we will cover the process to setup the form, the necessary steps to integrate an existing database table (or create a new table) for storing the data, and necessary functionality for adding records, editing records, and deleting records to this table. Although this demonstration covers only a small number of fields, the steps necessary and features covered should allow you to easily build complex and integrated form / data solutions.

4) The validation within this form is custom and uses client side events to show/hide required field icons on the page. You do not need to setup validation this complicated if you do not wish too, we are just outlining how you can have more custom notifications that fields need to be required. If you choose to turn on client side validation it will automatically add a red * next to required fields for you.

Form Configuration / Setup

1) The form above was created with very standard field types for some common fields and setup

• Within  the module configuration, general settings,  an option was turned on hide the help icons
• Within module configuration, clear link/button, the option to enable the clear button is enabled and set to be a text link (other options include image link) and the text was set to 'Clear results'. This feature was also enabled to ask the user if they would like to clear the results first.
• Setup Dynamic Fields Fields
  • Within the popular fields for first name, last name, and phone, we used specific short field names that are Browser 'Auto-Fill' friendly. These included the text of firstname, lastname, and phone directly in the short field name. Depending on your browser, these fields might appear differently and you should be able to click on the IE or Google Auto Fill which will fill in these forms for you.
  • You will want to create a new field called  'PhonebookID'. This field should have a field type of 'Hidden' and the short field name should be called DSParam1. Within advanced field settings you should also select 'Retrieve values from querystring variable for this question'. This field will be used to keep track of the current record if you are wanting to edit or delete the record. - view screenshot
  • You will want to create a new field called 'SQLType'. This field should have a field type of 'Hidden' and should have a default value of 'Add'. Under Advanced field settings you should also choose to 'Retrieve value for this question from the querystring'. We will be using this field to determine if the form should be ready to add a new record, edit an existing record, or delete a record. Based on the value of this field we will by linking different completion events to process the form. - view screenshot.
  • Within this demonstration we also added a field that was a text/HTML field type and included the token $(DSParam2). This will allow us to display to the user a message such as 'You are editing this record' or 'You are deleting this record'.
  • Example text was initially setup to include HTML for a SPAN tag. This allows us to use client side events to change the content of that area dynamically (from javascript).
  • Within this demonstration we also added an Advanced CAPTCHA field to verify a security code before the user can continue.  
• Under Module Configuration, General Settings, we enabled the feature to 'enter key forces submission.
• Client side events were setup to either clear span tag, or to populate it with a required field image. This javascript is listed below for the 'First Name' field, and similar javascript was added for the other two remaining required fields. 

if ($(FirstNameonkeyup) =='')
{
document.getElementById('spanFirstName').innerHTML='< src="/images/required.gif" />';
}
else
{
document.getElementById('spanFirstName').innerHTML='';
}

Note: The short field name for the textbox field also included 'onkeyup' - adding this text to the short field name adds the client side event whenever you press a key, otherwise its only added when you tab off of the textbox (onblur javascript event).

• The client side event functions were also added to the 'Initial Javascript' setting under Module Configuration, Custom Javascript File, Initial Javascript property. This will force the same javascript functions / code to be rendered after postback. This is necessary if your form posts back and you need to execute the javascript (such as question events, or server side validation).
• Under Module Configuration, Validation Configuration, the following settings were setup:
  • Client side validation was turned off (we do not need this as we are using our own custom javascript function to show or hide the required field image)
  • Server side validation is turned on (we still need to show the validation summary and force the module to check the required fields for proper requirements and business logic)
  • The configuration was selected to force the user to the top of the page and set focus on the first field in error
  • The setting was checked to display a validation summary message. After checking the box to enable this setting, you can define it within the area under Module Configuration, Header / Footer / System Messages, and selecting the option 'Validation Error Message' from within the dropdownlist.

2)  This demonstration covers creating a unique flat table to store the data in, and unique links and SQL completion events for editing the data, updating the data, and deleting the data. The steps below cover each topic and explain how to set these up within your Dynamic Forms implementation.

• Create Flat Table - First, we will cover the topics of creating a flat table to store your data in. This table should be created after all of your form fields have been created. This flat table will represent each field you have setup within your form, and there will be a column for each field.
  • To create this table you should first go into the completion events area, and select to create a new completion event. Select the completion event type of 'Dynamic SQL Event'
  • After selecting to create a SQL completion event, you will see some 'Assistance Links' thare were recently added to the 2.7 release.  These quick links will help you in creating your work flow and dynamic application.
  • You should select 'Generate Create Table SQL (One time only execution)'. This will generate a SQL script based on your form. You should open the file in notepad and review it to get a better understanding of your table structure.  view the script for this form
  • You will want to execute this script one time. You might consider renaming the first part of the script where you see something like 'DynamicForms_CustomTable_1197'. This will be your flat table name, so you might want to call is something like 'DynamicForms_PhoneBookApplication'.
  • To execute this script, navigate to Host, SQL, within your DNN installation. Copy/Paste the script and check the box to Run as Script. As an alternative you could save the file to your desktop and choose to upload the file rather then copy and paste it. view screenshot
  • Your table should now be created!
• Insert Form Data - the next step within this implementation is to insert the data from your form into this new table that you just created.  In this step, you will actually create your first completion event (noticed before you went to the quick assistance links within the completion event however you never actually created an event)
  • To create the insert event navigate into the completion events area, and select to create a new completion event. Select the completion event type of 'Dynamic SQL Event'. Name this event something that will describe this event, such as 'InsertData'
  • Within the quick assistance links you should click the link 'Generate Insert SQL Query'. This will generate a SQL script which you can use as the SQL Completion Event. You should review this script to get a better understanding of what SQL will be executing. If you previouslly changed the first script to generate another table name, you should also rename this to the correct table name within the SQL script.  view the script for this form
  • Copy and paste the SQL script within the 'SQL Statement' area of the completion event.
  • Under 'Dynamic Field' choose 'SQLType' or whichever hidden field you created. For the Dynamic Field response choose 'Add'. Since the hidden field default value is 'Add' then each time you navigate to the form you will automatically be setting the form to execute this event for the submission. This is the step that will force your event to only fire for new submissions, and not insert duplicate records when you are wanting to edit or delete the entry. 
  • Save the completion event by clicking on the 'Update Event' link button.
  • Note: For the SQL Statement, it is recommended once you have the script working as you would like you convert the script to a Stored Procedure. Although precautions are added to the software to prevent SQL Injection attacks, it is always best and recommended to use stored procedures for your Dynamic SQL Events.
• Update Form Record- the next step within this implementation is to update the data from your form whenever the form is in update mode.
  • To create the update event navigate into the completion events area, and select to create a new completion event. Select the completion event type of 'Dynamic SQL Event'. Name this event something that will describe this event, such as 'UpdateData'
  • Within the quick assistance links you should click the link 'Generate Update SQL Query'. This will generate a SQL script which you can use as the SQL Completion Event. You should review this script to get a better understanding of what SQL will be executing. If you previouslly changed the first script to generate another table name, you should also rename this to the correct table name within the SQL script.  view the script for this form
  • Copy and paste the SQL script within the 'SQL Statement' area of the completion event.
  • Under 'Dynamic Field' choose 'SQLType' or whichever hidden field you created. For the Dynamic Field response choose 'Update'.
  • Save the completion event by clicking on the 'Update Event' link button.
  • Note: For the SQL Statement, it is recommended once you have the script working as you would like you convert the script to a Stored Procedure. Although precautions are added to the software to prevent SQL Injection attacks, it is always best and recommended to use stored procedures for your Dynamic SQL Events
• Delete Form Record- the next step within this implementation is to delete the data from your form whenever the form is in delete mode.
  • To create the delete event navigate into the completion events area, and select to create a new completion event. Select the completion event type of 'Dynamic SQL Event'. Name this event something that will describe this event, such as 'DeleteData'
  • Within the quick assistance links you should click the link 'Generate Delete SQL Query'. This will generate a SQL script which you can use as the SQL Completion Event. You should review this script to get a better understanding of what SQL will be executing. If you previouslly changed the first script to generate another table name, you should also rename this to the correct table name within the SQL script.  view the script for this form
  • Copy and paste the SQL script within the 'SQL Statement' area of the completion event.
  • Under 'Dynamic Field' choose 'SQLType' or whichever hidden field you created. For the Dynamic Field response choose 'Delete'.
  • Save the completion event by clicking on the 'Update Event' link button.
  • Note: For the SQL Statement, it is recommended once you have the script working as you would like you convert the script to a Stored Procedure. Although precautions are added to the software to prevent SQL Injection attacks, it is always best and recommended to use stored procedures for your Dynamic SQL Events.
• Load / Edit Form Record- the next step within this implementation is to load the data from your form whenever the form is in view/edit mode.
  • Unlike SQL Completion Events, the ability to initially 'bind' data to a table is a unique feature within the application called 'Initial SQL Rendering/Bind'. To find this configuration you should navigate within module configuration, Initial SQL Rendering/Bind'.
    • You should check the box to 'Enable initial SQL data bind'.
    • For this demonstration you should choose to 'Only enable when querystring variable is present' and you should choose a querystring variable of 'DBUpdate'. This means that we are only going to attempt to bind data to this form whenever we pass 'DBUpdate=True' via the querystring/url. We will be passing this value within the edit/delete links that we generate for the reports module
    • For this demonstration we created a stored procedure to retrieve the data, you could also easily load the data with a simple select statement though. Such as " 'Where UniqueCompletionID = '$(DSParam1)' " - view screenshot