I am a .NET consultant for a large university and we recently purchased DF 2.3 for use in a project. I have a few questions regarding SQL driven options and recalling forms.

1. Is there a way to have a dynamic options list driven by a controller class? For example, we pull student registration information using a CourseController class from several databases and flat files. I would like to use this controller to populate a dynamic question without the intermediate step of storing it in the database.

2. What happens when a SQL query fails? We have a remote campus that has internet connection problems sometimes (the whole country goes down) and I'm wondering what will happen to the form if they cannot connect to the databases (we link a remote oracle database into the local SQL database).

3. Is there a way to add a static option to a dynamic (SQL driven) question? In cases when the internet connection does go down, I would always like there to be an Other option that then raises a textbox field for manual input.

4. Is there a way to recall a survey after it has been submitted (so it can be modified)? We are planning on allowing receptionists to edit surveys for students, and then let the students re-edit them and confirm the selections.

Thank You,

Ben Floyd

Ben,

Hi. First, thanks for your purchase. Let me see if I can answer your questions, some might not be the response you were hoping for but at least we know where we are working from.

1. I don't really see a way of pulling the field data in out of the box. By changing the code you could pull from other sources but you would still have to pull in the data from some secured method. A possible option might be connecting to a web service to pull in the questiotext/question option?

2. Basically you would want to copy the SQL to the local database using a DTS package or connect to the remote SQL server from the local server using a linked server connection I am assuming? You have the option to pull in the field values from a store procedure and not just SQL so within that stored procedure I suppose you could do a Select Count(*) from linkedserver.dbo.yourtable to first check and verify that the connection is working, maybe include an if statement to pull in a backup table if necessary?

3. Yes, one option for doing this would be to use something like Union All. Here is an example.
Select '< Select Option >' as QuestionOption, '0' as QuestionOptionValue
Union All
Select Text as QuestionOption, Text as QuestionOptionValue from lists where name = 'Country'

For each item you wanted to add on top of the full query you would just add the two columns for QuestionOption/QuestionOptionValue and then add Union All before the next query. You can test this in query analyzer or under Host, SQL as well.

4 Well the field data is stored in relational tables you can always pull in later, there is also access to the 'UniqueCompletionID' via the database tables and also within SQL events. Additionally, keep in mind with SQL Events you can insert/throw the data into literally any table you want to put the data in. There is also a setting within general settings to only allow users to submit the form once, and a place under messages/layout that allows you to specify the message that they will receive if they go back to the page after submitting it once (i.e. Sorry you have already submitted this form). Just an FYI if you are testing out this feature this flag is ignored for admin users.

-Chad