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!

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

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 efficient.

Is this possible now? I would really love this ability...

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 rendering 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

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  prior option)

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 available.


Hope this helps!


-Chad