First off, I really do like Dynamic Forms so please take this as constructive criticism!

For several weeks I've been buying and trying to configure various form/list/mod modules in an attempt to do some very simple (yet tedious) forms and result lists.  I have an existing table with about 80+ records/50+ columns that I need to pull records from and write back to.  Pretty straightforward, except, no tool to do the whole job with. 

Without naming the other products I'll just say this; Product 1 stored all of it's data as XML strings in the database making parsing the results and/or using that data to populate another form was a real pain.  Product 2 stored the form data as multiple tables which was nice but assumed that you were an SQL query expert and the 'builder' simply gave you some web panels to enter in your lengthy SQL queries and the extensive code necessary to display the data.  Also, another pain...

Now, in using Dynamic Forms, the form creator is very nice but again, I see no easy way to either create a new table or refer to an existing table and subsequently build a form around it with the extended options available in Dynamic Forms, which again, I like very much.

Since DataSprings develops for both DNN and Sharepoint, you'll know what I'm talking about when I say that doing something like this in Sharepoint takes about 10 minutes.  I create a new list by importing Excel data, modify the columns, add new columns, select data from another list if necessary and I'm basically done; it ain't real purdy (IMHO) but it works. 

Each time I've raised similar issues with other product vendors, the answer was usually something like "Oh, that's simple. Just write an SQL query to pull the records you need..." or "Oh that's easy, just cut and paste the following [10 pages of] code into the forms header section, modify your xxxxx file with..., execute the attached script from the console of the server... then..."   To some folks, I'm sure those answers are helpful, even acceptable, but I'm trying to get away from the extended development necessary to create a custom form solution else I'd build the whole site in Visual Studio and be done with it.

Dynamic Forms seems to be about 90% there in being able to do what I need but I had hoped that each unique form that I created would get it's own table(s) rather than it store the data in shared tables.

If there was one more configuration section that allowed me to specify the db and/or the table(s) I want to use and have it return a list of the columns available for use for the new form, that would be enormously helpful.  I don't see a way at this point to do this as all the Dynamic form data is stored in it own set of tables and I don't think it's possible to do an import of my existing data into those tables OR to make Dynamic Forms use existing tables.

If there is... let me know.  I really don't want to have to write custom SQL procedures to post the data as an event.  Also, am I correct in assuming that there is no control over the 'view results' and that a custom query has to be written to display that data as well???

Any feedback or advice would be appreciated...

I am in the same boat, Andrew. While Chad has been very, very helpful in answering most of my questions, I am at a standstill, unable to figure out the SQL to get the data out and display the data in any way. Having all the responses go into one column exacerbates my problem, again, knowing now SQL whatsoever.

I like the module a lot, but can't wait for yet another add-on to get the data out, so I am now looking at said product 2 as a solution, given the reporting features and straight forward table design.

Try the DNN forums. I just posted a few threads seeking help to go about getting the data out and posting now in said product 2 forums to see if it would be easier down the road....food for thought.

If you know your SQL, this would be 99% finished, but for the novice who would like to use the data that goes in, it's 75% finished...and the learning curve to get the data out to the newbie is steeper than the learning curve of said Product 2.

Hi guys,

I thought I would post some feedback on this and also continue to get your feedback as we are knee-deep in the next release.

Regarding SharePoint/MOSS VS DotNetNuke  - yes MOSS offers some easier abilities to quickly create lists and add as many columns to those lists as you would like etc. This does have some advantages as they are easy to create but from a development standpoint they are a nightmare (keep in mind some applications require very extensive stored procedures and database tables to function, these must all be created as hidden lists within MOSS and accessing/querying these lists can be very cumbersome to say the least). Believe me, we are in the process of building Dynamic Forms for SharePoint and although there are some clear advantages to this methodology and development there is a reason why you don't see as many 3rd party add-ons to build on top of MOSS as you do DotNetNuke.

Regarding Dynamic Forms  - Keep in mind that you do have access to view the details of each form and you can also export those results to Excel. Additionally there are a number of tables you can query if you were advanced SQL guru that store the data directly in the database, ill try and have some SQL Queries posted so that you all you have to plug in is the moduleID into any reports module (like the standard DNN Reports or one such as Report Grid advertised on Snowcovered which can handle not only pulling in the date but paging, export to excel, export to XML, searching based on specific columns etc...). Finally, there is a 3rd option of using Dynamic SQL Events to literally put the data into any table you would want to (or pass to any stored procedure, etc...).

Now, I do understand that not everyone is a SQL Guru here. We will research possibly adding a feature within the form build that automatically builds/alters a table based on the Dynamic Fields that are added and maintained in Dynamic Forms. This way you would always have a table automatically created such as DynamicForms_ModuleID_XXX etc... Again, the downside here is that its still forcing the table data into its own table and not a custom table if you wanted it to update a custom table you already have.  This would help users out though that simply wanted to pull from that table with a 3rd party reporting module. We could also release a reporting module but its kind of like reinventing the wheel as a few others out there already have very robust features.

Ok, as I keep rambling here I meant to come up with an easy solution that doesn't involve much SQL knowledge.

My suggestions (at least in the current version) if you are wanting to save data and pull data out you follow the following steps. Yes - there is SQL involved here but its minimal and after you get it setup you shouldn't have to worry too much about it.  For the example below we will just have a form with 2 columns (and therefore will create a table with just 2 columns in the database).

Step 1: Make sure you create a table or already have a table with 2 columns in it.

a. If your table already exists you can go to Step 2, the Dynamic SQL Event
b. If your table does not exist you can probably use a 3rd party module to easily create/manage your SQL tables or you can use some basic SQL Query to create the table. Again, keep in mind you don't have to be an expert at SQL and once this is created you can always use the table so its only needed 1 time.
 
This example would create a table with just two columns (FirstName, LastName). It will also add an ID column but you don't need to worry about that one. Keep in mind that you could easily add a new line for each column you want similar to the firstname, lastname columns.

CREATE TABLE mycustomtable
(
    mytable_id  smallint
        IDENTITY(1,1)
        PRIMARY KEY CLUSTERED,
    FirstName        varchar(300)   NULL,
    LastName        varchar(300)   NULL,
)

Step 2: Create a Dynamic SQL Event - This event can be based conditionally on a fields response or always fire on form submission.

a. Within the SQL Event, lets assume that you want to add the field data into the table you just created called mycustomtable. Keep in mind that $(FNAME) and $(LNAME) are just the tokens you can use that represent the short field names within your Dynamic Form.

The SQL would look like this:

Insert Into MyCustomTable (FirstName, LastName) Values('$(FNAME)', '$(LNAME)')

Step 3: Retrieve the data - I would suggest either the standard DNN Reports module or a 3rd party reports module such as the one I mentioned above.

Retrieving the data is just one quick query:
Select * from MyCustomTable

So - In summary here keep in mind that these steps are only needed if you are wanting to pull the data out without using Dynamic Forms. Dynamic Forms already saves the data to the database and allows the ability to view form results and also export that data to excel. We will research other possibilities such as another add on module to display results outside of the forms themselves and possibly the ability to automatically create a table (optionally) and have field data stored in that custom table (maybe one you define?). I believe this will help many users but there are still going to be many other users who either want to store data in their own tables that exist before Dynamic Forms etc... There will always be exceptions but we do want to continue to make it as easy as possible for as many users as we can. Additionally there are many benefits to using 3rd party modules to later retrieve and access your data. This can include reporting modules, charting modules, etc... Why? These modules already handle features such as the ability to search that table, paging, sorting, exporting to many different formats, etc...

My biggest suggestion I guess would just be to keep your SQL as simple as you needed, inserting data into a table and retrieving data into a table should be easy by reviewing a quick example or two.

We would appreciate any feedback on this subject and what you would like to see added to the product (the more specific the better, would my comments about having it optionally auto create a table and store the data for you there work for your implementations? etc...). If we receive enough users running into this I would also be willing to setup a go to meeting or webex to maybe include a training session on my example above with a Q & A at the end.

Ok, time for more coffee now

-Chad

You are my hero, Chad; thank you, thank you, thank you. I am now motivated to go back in and give it another go.

I got your dynamic SQL in the other thread that could be used as a stored procedure, but having never created a sp, I didn't know exactly how to do this correctly (thought I attempted a few times).

Is there a reason the data go into one column? Is this to make it more of a one-size-fits-all practice and people can then manipulate the data to serve their needs?

I could envision a series of checkboxes and text fields in a "Data Storage" section within the module general settings:

• Would you like these data to go into their own table? y/n
• Enter Table Name
• Label columns using shortname fields used? y/n
• If no, enter column name for $(field1): ----------, etc

I have no idea what is involved to do anything like this (obviously), but something of a similar look and feel of all the other intuitive setting in the DF module that handles data storage making it easier to get the data out would add tremendous value to the non-SQL types.

I still envision that add-on module that is a powerful report toy that manipulates the data stored by DF and allows for grids and searches and filters and charting... I'll up my ante and say that somethng like that has $49.95 written all over it and a must-have module for any non-SQL user who purchases DF...would bring in a lot of '2-fers' I woudl imagine.

If they data go in clean with each respective field having its own column, I have been able to use the reports module (very limit use, but got it to work eventually) to do a simple grid...think it's the all-in-one column and the way the keys ties it all together that is too complex for me to understand....like 3 dimensional math to me...cast this and right inner join cube data that...way over my head.

...going to digest your last post and play with it this week and see what I can do.  Thanks again.

Yes, as with my last post to you somewhere else, I'd love to see some examples. Thanks!

Absolutely, thanks! I, too, planned on using theTresselworks module, as I found it relatively easy for even me to understand...

I posted a thread in the DNN forums last week about having a pinned thread for each project area and use the thread to showcase the power and unique uses of each respective module. The same general idea might serve these forums well, too, Chad....food for thought.  Would even make for good PR/sales when someone sees everything that the module can do that they never thought of, much like Product #2 does on their site (everyone posts their code for forms they have made and apps that are driven by Product#2)  

I posted an example in the DNN thread of how I used DF to create my links page; I created a drop-drown with the 4 categories of links I had, then created a dynamic event that would hide or display the links the user wanted to see based on the value of the dropdown. I am sure there's a more efficient way to do this, but I thought it was a unique way to use the DF module....clean 3x3 table with logos of affiliate sites that change genre by the drop-down and the site stays perfectly still..just the logos flash real quick when the dynamic event fires. 

I would love seeing what others are doing and what the module is capable of in advanced settings...and how exactly you go about doing it! 

John,

Hi. Yes this is a great idea! We are working to create more demonstrations once the next release is rolled out (will be rolled out as 2.5) in our Dynamic Forms Demonstrations area. We are also interested in anyone who wants to get paid to add more demonstrations to this page, if you are interested let me know).

As far as sharing resources, again great idea and I have started a page where users can easily share resources. This includes localization files, IPORTABLE form templates, etc... Once users have added several resources I will include the page in an upcoming newsletter and announcement etc.. 

So far now...
Dynamic Forms Demonstrations (looking for anyone interested in getting paid to add new helpful demonstrations outlining Dynamic Forms features and capabilities)

Module Resources Page - New page to upload module resources to help other users, a great area to share localization files or form templates.  Once a few users have added resources we will announce the new page and add it in the next newsletter. Additionally, we will try and outline one demonstration/resource added in future demonstrations.

Thanks!

-Chad

It seems I struck a chord with this thread initially... that's good, at least for me; I'm only a dev guy wannabe and while I have really good conceptual designs for certain tasks in my head, I'm not a true programmer and am seriously frustrated by the tools I have available to me to accomplish my tasks... the entire task.

(Grab a couple more Tylenol here...)

Over the past couple of weeks I've moved well beyond 'select * from table' and the answers I've gotten from the Datasprings folks have helped immensely, but it's not that I'm lazy (I am) but I just want to build the solution quickly (input AND output) and send an invoice. To make DNN actually useful beyond the static core modules and a handful of more 'buy this module now solutions' which only met about half the requirements, I've had to focus on two primary products, Dynamic Forms for data entry, prepopulating fields with known data, inserting data into new tables, etc.) and dare I say, ListX for data retrieval, display and modification. These two complement each other fairly nicely and I've been able to build out a fairly complex 'job request' system for yet another product (RepliWeb, which has zero user interface) that 'hands off' job requests from the user, to the manager for approval and finally onto the server admin who needs to perform the actual job configuration.

Still, at the final stages of building out the whole process, I can't seem to manipulate some fairly straightforward multiple item checklists (yet another Andrew post) and haven't been able to get an answer here yet. I use only my own and DNN's tables to modify user info or insert the proprietary info; I haven't found any use for the Dynamic forms tables as of yet. Given that, these two tools put or get the info I need pretty nicely but when using custom or existing tables, certain Dynamic form 'fields' don't seem to be usable; namely multiple selection lists. One question, one answer seems to work fine but I have about 4 multi-selects that don't get any data inserted.

I suppose from a future needs list, what I would really like to see is a table/column selector where I could specify the table I want to use and have it present a list of the available columns that I want to manipulate and in turn have those added to the list of $varnames shown when creating a form event. Everything I need to do has to happen with a SQL form event and I'll bet I'm not alone. Obviously showing my ignorance here, I was stumped when it came to working with data elements outside of the form itself and yes, these forums helped quite a bit.

I started the thread fully intending to get some feedback and that it did but I'm still a novice seeking a form builder/report generator that will do all the work for me... don't misunderstand tho, my time is valuable and I'd rather pay a premium for a magic tool than to spend a month learning primary keys, table linkage and 'joining'. These are all wonderful skills but I'd much rather be using a product to augment DNN (because the core modules are great but well... not much more useful (and in some cases less useful if I can nest a comment) than Frontpage form wizards) and billing the clients for a working solution rather than banging my head against the wall trying to figure out how to build my own complex SQL queries.

Again, love the product, and I will continue to use it for part A, but think about us 'select * from table where' folks when considering new features for future versions.

I do have a couple comments relating to what he said here.  First of all now I can see how to create a table and I do understand those SQL statement because had dealt with them before.  The reason I got dynamic forms was so I didn't have to create everything myself .

Please don't take to the wrong way, but what is the use of a dynamic forms if it only stores information that can be used in different ways.  For instance your step three.  I understand the SQL statement in your step three but when you do this where the information stored when you run this.  And also can you make something display immediately when someone goes to your form .  How do you connect to the DNN report or other display modules.

It is good that you can as an administrator see, but the information should be able to be used on your web site.  For instance showing what people entered for a guestbook or a shopping cart.  Right now I just sees like dynamic forms and is just for retrieving information unlike explained on your module description.  I'm not sure what the purpose of getting information without being able to use it would be.

Tim,

Hi. Thanks for your comments. We are working on methods to provide further integration and more flexibility with build applications and true process workflows. Each project has different requirements and each implementation is different from each other. Just a few things to clarify a few things.

1. Information from Dynamic Forms is stored in the database and can be retrieved and exported to Excel

2. All data stored can be distributed or sent to end users or administration users with email events. All fields and data can be included within the email and in a custom template.

3. All data can be passes to other modules or pages/applications/sites via silent HTTP Posts

3. All data stored or retrieved can be stored in a custom table or passes to a custom stored procedure (using Dynamic SQL Events). This should really be used by advanced users or advanced implementations when you need to push data to other applications, provide further validation, or other advanced features. For example, maybe you want to populate the announcement modules tables directly from Dynamic Forms? Or maybe you want to add data or edit data directly in a table that has been in production for years you are wanting to integrate with.

The module can handle advanced implementations through SQL Events, and however we typically recommend having an expert handling these or having someone on our staff handling these using Premium Support hours (you can find out whats covered in standard supports VS Premium Support at http://www.datasprings.com/productsupport). This is not to say that you must use these advanced features to take advantage of Dynamic Forms, only that they are advanced features and require additional skillsets to implement them.

A few things we are doing to make it easier for end users:

We are adding additional demonstrations:

http://www.datasprings.com/Products...fault.aspx">Demonstration #13 was recently added and shows implementation with DNN Reports module, similar to your question? You should be able to download the IPORTABLE file and follow the tutorial.

http://www.datasprings.com/Products...fault.aspx">Demonstration #14 was recently added (not yet posted but the link is valid still). We are working on updating the tutorial process with the steps necessary to implement this, however you do have the ability now to create full workflow applications with insert/edit/delete capability as used in this demonstration.

New enhancements - We are in the process of adding several new features to Dynamic Forms 2.6.x updated patch/hot fix that provide several new features. These include initial data SQL binding to a table, ability to retrieve results easier using querystring parameters and SQL binding, 'auto generate' links within SQL Events including auto generated Create Table scripts, auto generated insert, update, and delete scripts, etc... These are currently in testing and you should be able to test them on Beta Springs.

-Chad

I thought I would also add a few screenshots that go along with the enhancements mentioned above. There is also a link related to the new 'Assistance' area in the Dynamic SQL Events.

Screenshot #1 represents the SQL Binding feature

Screenshot #2 represents the Assistance quick links that will be in the SQL completion events.

Screenshot #2

I entered the following syntax into SQL under the host menu and it keeps saying there is an error. 

I followed the syntax you gave on Step #1 one in this forum thread I assume it should be like this exceptI erased the last Comma I didn't think it should be there   

Creating a table DAWNArtCommTable
(
    mytable_id  smallint
        IDENTITY(1,1)
        PRIMARY KEY CLUSTERED,
    ArticleID        varchar(300)   NULL,
    DisplayName        varchar(300)   NULL,
    EmailA        varchar(300)   NULL,
    City        varchar(300)   NULL,
    ZipCode        varchar(300)   NULL,
    DAWNArtComm        varchar(300)   NULL,
)

When this gets executed it should create a table like the following, correct ? 

 ArticleID is brought into the DynamicForms form through a querystring and it does get put into the correct place.