Feature Request: Populate all fields from single SQL statement > DotNetNuke Module Product Forums > Data Springs Inc.

Unanswered Active Topics

Forums Search

Forums > Product Discussion - DotNetNuke Modules > Dynamic Forms

Subject: Feature Request: Populate all fields from single SQL statement

Email me when someone replies to this thread.

Prev Next

You are not authorized to post a reply.

Author

Messages

Mark
Posts:3

01/17/2008 11:41 PM
I'd like to do a simple UPDATE screen, where I read in the values from a record in the database, populate form fields, allow the user to edit them, then write them back to the database with a customized sql update command. While this is possible, it seems, from Dynamic Forms, it doesn't seem like there's an efficient way to do it. I understand I can populate each field from SQL individually, but if I have 20 fields on a form, that'd be 20 distinct SQL calls -- too inefficient. I could also populate from session variables or from the querystring, but those too would require an intermediate step and an external process of some sort. What I'd like to do is have a single SQL statement that drives the entire form, then just call pieces of data out of the SQL cursor. This might also be considered a "Form Load Event" for Dynamic Forms. For example: select FirstName, LastName, City, State, Zip from MyTable where id = $(variable) Now, I'd like an option for each field to populate from the SQL cursor, or even a field where I could type $(firstname) or $SQL(firstname) or something like that. If I could get this feature, then I could do complete application and workflow processing (insert, update, delete) in a pretty quick and tidy way all using Dynamic Forms!

Chad
Posts:2040

river guide

01/23/2008 2:06 PM
Mark, Hi. Well yes... There were a few features that were on the Dynamic Forms 2.5 Roadmap that never made it and I think one of those is specific to what you are wanting (please confirm though as we are coding it for the next release so we are interested in your feedback). Basically right now there is already a column added into the database called 'UniqueCompletionID'. This is a unique identifier (a GUID) which represents each forms unique response. This is later used in the View Results area or if you designed a report to query the DynamicForms_QuestionResponse table. So... The idea going forward is that you could (and the module would support) the ability within View Results to also edit a forms submisison, and / or you could email a link within a dynamic email event that would allow to edit the form... Actually this would go along with another feature that would allow the user to 'Save form for later' etc... We are still in the design phase of this implementation because there are a few options here but this is where we are heading... So basically when editing a form you would be using a unique ID within the querystring that would load the form and allow you to re-save it etc... Is this what you are needing? -Chad

Chad
Posts:2040

river guide

01/23/2008 2:09 PM
Also... This technically could already be completed (as you mention) if the form fields were textbox/hidden fields only and you used a SQL Query to pull in the default value (the query would include the unique responseID which could be a querystring variable) but as you also said its not very effecient.

Jeff
Posts:3

06/20/2008 7:28 AM
Is this possible now? I would really love this ability...

Bamse
Posts:70

river guide

06/24/2008 9:23 AM
We needed the same thing and we created a function with a redirect to the form. So the function would get a lot of values from a db and save it as session variables and redirect to the form. Then in the form just set the questions short name to the same as the session variables and check “get values from session names” (or what it is called don’t remember exactly). Requires coding but is a simple thing to code. Ask a coder to assist you coding this, 1h work maybe. I was thinking suggest a feature request for this, but because the values can differ so completely from where you will get them, I don’t know how to best do it “generic” as possible. But saving it as sessions would be one way to go.

Bamse
Posts:70

river guide

06/24/2008 9:33 AM
ahh reading your post the second time I see that you already mention using session variables, so sorry for posting the obvious. Performance this is ok solution compared with making a lot of individual sql requests. But again you need some custom coding outside dynamic module as you pointed out.

Chad
Posts:2040

river guide

06/24/2008 9:56 AM
Well.... Yes, this will require some research to determine exactly if it can be possible and feasible. We love to add features (trust me, I get all kiddy inside ) but the logistics on exactly how it would be done are not clear. Right now we can reference fields via client side events, and we can set default values via SQL default (for hidden/textbox fields), querystring, session, cookie, DNN user defaults, but what you are wanting to do is to 'bind' the data for fields to its own query? So I would be assuming this would be a one row table, and each column name would represent the fields short field name? So, something like: Select TOP 1 FName, LName, MyEmail from YourTable And then when the form rendered it would check this Table to set the default value. If a column existed in the table called FName it would render the content from within the column/row? -Chad

Chad
Posts:2040

river guide

06/24/2008 10:06 AM
Just thinking this one out more in my head... Specifically, it wouldn't be all too difficult to add a feature where you could specify a SQL statement to bring in values from a SQL query (globally). My questions are: This would be a single solution for the form, not a 'load' event so that you could setup as many load events as you would like. The reason is performance and the fact that it could check the table when rednering the form and if a row/column existed it would load that data, otherwise it would not? Any thoughts on this solution? Or... Would you prefer a checkbox for each field that says 'Use the temporary SQL query/table for this field'? -Chad

Bamse
Posts:70

river guide

06/24/2008 10:36 AM
Yes a new “Form load event” It gives you a option to insert a SQL statement that pulls values when the form loads. (Maybe also a URL get function?). It pulls data and the Form load event GUI presents a mapping option for the pulled fields against your already created form fields based on form field names. So a admin creates all fields, then in the “Form load event” adds a sql statement and maps the values to the existing fields. (with a preview function) A field default value and session/cookie value should override the form load event value. (not sure about this however). EDIT i see no reason that the form should be limited to only one "form load event", if you have several form load events the mapping tool in the gui gives you the option to select how the fields should be populated (maybe with prio option)

Chad
Posts:2040

river guide

07/15/2008 5:24 PM
Hi. I wanted to let you guys know that this feature is being added right now, and will be released with an incremental patch/hot fix within the 2.6 release. For performance reasons... We are not creating this as an actual completion event. If you are wanting to load specific data for a field individually, you can use the 'SQL Default' instead. The setup will be 'Initial SQL Binding' that will be a setting within the module configuration area. The setting will allow you to enable an initial SQL query/table, which, when rendered will automatically bind to your form fields based on the fields short field name. So, if you had a field with two fields called FirstName, and LastName as the short field names, your query would look like: Select FirstName, LastName from YourTable Keep in mind the new parameters such as $(DSParam), $(DSParam2) etc... This would allow you to load an initial record based on the querystring parameter. We would also suggest when also using parameters actually use a stored procedure to avoid SQL injection risks (however these parameters are parsed for SQL injection routines and so forth) So.... there is always discussion when implementing default values as to which default should take precedent over others. At this time, the list of precedence will be: All based on if enabled.... * SQL data base (new feature) * Default Value (either text or SQL default) * Querystring variable * Session variable * Cookie variable This means if you have a firstname field, that is both setup to 'Retrieve value from querystring', and you have also enabled the initial SQL data bind, it will take the value from querystring if its avaliable. Hope this helps! -Chad

You are not authorized to post a reply.

Forums > Product Discussion - DotNetNuke Modules > Dynamic Forms > Feature Request: Populate all fields from single SQL statement

ActiveForums 3.7