Perhaps this information can be found somewhere else in the forum. I am new to Dynamic Forms and haven’t read the entire forum although I must say that Chad provides a lot of good information.
Step 1: Setting up a Reporting Data Base
Here is a technique which might be of help for others who will need to do a lot of data manipulation on the information collected by their Dynamic Form(s). In this case, I am collecting a large amount of information as part of the registration for an event. To effectively run the event, we need to print nametags, produce security lists, mail information packets and verify that people have all the information required. This is not easy to do using the database structure used in the Dynamic products. By the nature of the product, the data must be stored in a set of tables that describe the form structure and store the data. I will go into the details of this in part two but these tables are not good for reporting. This is why they provide you with the ability to export the data to Excel. This is a great utility unless you change the structure of the form after you have started collecting data. While your customers may know just what they want before you set up the form, mine do not. My data, when exported, does not always have the right headings on the columns nor are the columns in order.
My solution is to store all of the data into an SQL table and use that for reporting. To do this, you must be able to access the database directly. I use Microsoft SQL Server Management Studio Express because it is free and allows me to do most of what I want to do. For some reason, you can’t create Procedures using the product. That must be for the commercial version.
Once you have the table in place, and remember to include a unique key so you can uniquely identify a particular row, you create Form Completion Event.
Alert: The values for Read Only fields are not passed to the SQL statement. If you have calculated read only fields, copy the calculated field read only field to an Hidden Field (thanks Chad).
You define the event as a Dynamic SQL Statement. You can structure your SQL tables however you want to and then enter SQL code to store the data from your form to the tables.
In my case, I have an SQL Insert to store most of the form data into my main reporting table, an SQL Insert to store information about how many people are signed up for a breakout session, and a procedure which stores dates into the database after checking to see if the dates are valid.
Step 2: Moving information from the Dynamic Forms tables to the Reporting Database.
Right after we went production with the Registration Form, the customer asked that several questions be added to the form. This was before we had received any data but between the time that we put the additional questions onto the form and when I remembered to change the Form Completion Event described in Step 1, a fair amount of data had been entered. In addition, I can collect up to 15 birthdates on the form and I hadn’t created my storage procedure for that yet. The result was a reporting database which was missing quite a bit of data. Chad has posted an SQL Procedure for exporting data from the database to an SQL file but this didn’t work for my form. It is probable that I have single quotes in my question names and that “confuses” the procedure. What I needed was a way to move the data from the Dynamic Forms table into my SQL Reporting table(s). First I needed to understand how the tables in Dynamic Forms fit together. The information is stored in two primary tables: DynamicForms_Question and DynamicForms_QuestionResponse.
DynamicForms_Question
This table contains the information which describes the question. Everything you put into the manage questions screen is placed into this table. For the purpose of this example, the DynamicQuestionID and Question columns are the most important. The Question helps you to find the information about the correct question while the DynamicQuestionID is used to link to the data found in the DynamicForms_QuestionResponse table.
I manually looked up the DynamicQuestionID for each question of interest. (Chad’s export Procedure builds the SQL selects from the table information. )
Dynamic_Forms_QuestionResponse
This table contains the data entered into the form. There is one row in this table for each question entered on every instance of the form. That is to say, if you have a form with 10 questions and you enter the information for 20 people, there should be 200 rows in this table. All data entered into Dynamic Forms is stored into a field (Response) which can hold up to 3000 characters. Dates, numbers, and combo boxes are all stored the same way. The UniqueResponseID is actually a field which is unique to each instance of the Form. If you collect FirstName and LastName for a person, both rows in the table will have the same UniqueResponseID. This lets you group all the responses for a given form instance.
SQL Statement:
Update b
set
b.copy = n.response
from dbo.hfdc2008reg b
inner join
dbo.dynamicforms_questionresponse u
on b.email = u.response
inner join
dbo.dynamicforms_questionresponse n
on u.uniqueresponseid = n.uniqueresponseid
where u.dynamicquestionid = 'F37A27AB-EBBF-4668-A4F3-3810E67E6D8C'
and n.dynamicquestionid = 'FD51C439-E130-40E0-8CD6-369EBF06FD22'
and rtrim(n.response) <> '';
update dbo.hfdc2008reg
set driverslic1 = copy;
update dbo.hfdc2008reg
set copy = null;
Being paranoid, I move the data into another column first. Initially, I commented the last two updates when I tried the statement. This allowed me to compare the data entered to that found on the forms. After comparing the data, I removed the commenting and reran the queries.
This technique could also be used to fill in a table by doing an insert for a key value like Email.
If there is confusion, I am unclear. If it is clear, my hand must have been guided. |