Given that there isn't a formal report feature that allows us to display the form records in, say, a grid view, how do we get the data out?

I was going to try SQLGridSelectedView, a 3rd-party module (free) to display the data and allow members to query the data, filter, etc.

I also heard I could do the same with the 'Reports' module bundled with the core.

Therefore, I am wondering:

1.) Best way to get the data out and displayed, allowing members to query/search/filter.

2.) Where are the data stored? In the dnn db or elsewhere?

3.) Do I need a different connection string to tap the form data?

4.) Some basic SQL parameters/commands/whatever they are called to pull the data for the report modules. 

5.) Are there other modules people use that make all this easier. I am willing to try anything to finish this project up! 

Any help would be greatly appreciated.

John,

Hi. We plan to add more out of the box functionality for Dynamic Forms searchable results and so forth but each users implementation is different so more than likely this post will help many other users as well. I know we have worked on implementation for clients using the standard reports module but any 3rd party module that uses SQL can work.

A few comments on your questions.
1. One would be to query the data directly, all data is stored in relational tables for DynamicForms_Question and DynamicForms_QuestionResponse there are a few keys linking these two fields and you would need to add parameters to the query such as the moduleID to only pull in results for that form.

One comments: Ill dig up the query but depending on your exact implementation it can be a hefty query, it creates a temporary table of all questions/responses, shouldn't be a big load but debugging it could be a pain. One suggestion I would make would possibly be to create your own table and use a Dynamic SQL Event within the completion events to insert the data into that table.

For example you might create a table with First Name, Last Name, Email and then execute a SQL query such as:

Insert Into YourTable(FirstName, LastNAme, Email) Values('$(FName)', '$(LName)', '$(Email)')

etc...

2. I think I answered this in #1 but basically

DynamicForms_Question and DynamicForms_QuestionResponse tables, still within the CORE database but their own tables.

3. No

4. Ill dig up the query you can use for the core, you still might consider posting to your own table depending on your requirements though, this can provide easier ways to query the data later.

-Chad

Thanks a lot, Chad! Knowing "nothing about nothing," I've stared at this code for a week now and think I understand it. 

I went into the db and looked at those two respective tables - DynamicForms_Question and DynamicForms_QuestionResponse.

And then the code made sense; all of the responses are in 1 column! I also say the unique keys, so I see what you are doing....pairing all the responses together for the form based on similar keys...more or less? 

After reading you response and never having created a stored procedure, I think you suggestion about creating my own table and using a Dynamic SQL Event within the completion events to insert the data into that table would be my best bet.

So I would, using my 3 basic forms for hotels, flights, rental cars, create 3 tables named, say, hotels, flights, cars.

Then create a column for each question (e.g., check_in, car_class, depart_air, city, state, et al)?

Then create an SQL event that writes each field to the respective column? Something like (using your example):

Insert Into Hotels(Name, City, State) Values('$(HName)', '$(HCity)', '$(HState)') ?

What I can't visualize is how the rows/columns would be setup so that when a user selects, say, hotels in New York, it will return all hotels submitted for New York with the respective price, hotel name, etc.. I am assuming that each row would need a unique key and then pull all the data for that key?

If I could get the data into a clean table with individual columns for each field, I am confident I could get the gridview done on my own...but with all the responses in one column, I am totally clueless, as you can tell.