I've created a "region" combo box using the following sql:

select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'Region'

except I can not set this to be required while allowing someone to choose any option (unable to pick the first option that is shown).  I don't want a default value because that value is not an allowed selection when the region field is required. 

I've also tried to add "--- please select ---" as an option in the list, however, am not allowed to put this as the default option (as unchecking the default option doesn't force someone to make a selection and doesn't respect the "required" option)

This seems to be a catch 22 with using this.  Anyone have any advice on how to use this correctly? 

thanks,

thanks for the help on this!

I tried searching for 'Union All' in the Dynamic Forms forum and got zero results. I know this is incorrect because at least this thread has that phrase.

Can you please point out some examples? I can't get a combo box that I'm using SQL driven options with a union all query to validate correctly when I set it as a required field. No matter what I choose, it always gives the required field error and resets to the default value.

Hi Ray--

True, a search with a space seems to give you 0 results.  I tried searching for just "union" and didn't really see anything.

I had used the exact code that Chad provided and it worked perfectly for me. 

Can you try adding a new/test combo box and use the code from above (make sure you remove the space between the words question & option ). My settings are as follows:

1. Combo Box
2. question option > sql driven options
3. sql: select '-- Select a Country --' as questionoption, '-- Select a Country --' as questionoptionvalue union all
   select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'Region'
4. click test to make sure the sql is correct (if you have an error here this could be the issue. fresh dnn install returns 65 results
5. advanced field options > no default value IS checked.
6. scroll down to question validation and check the box "required field" and put something in the "required field error" .  this will help show if it's working or not.
7. i've also gone to module config > validation config and checked "Display * next to field label"

i'd test this out first to make sure it works and then work backwards from there.  If you can, also post your sql.

I should als note that I'm running the latest version of dynamic forms (3.3.5) (top of df shows 3.3.40.0 but host > modules shows 3.3.5) on the latest version of DNN (5.4.1 (26))

Curtis

What I'm trying to do is make a combo box that contains the list of users on the site with the DisplayName as the QuestionOption and UserID as the QuestionValue. I also want to make the combo box required, so I need to add "-- Select Coach --" as the first option. I also need to account for the possibility of the assigned coach not yet having a user account. So, I need to have "(No User Account)" as the second option, with the users listed after the first two options, ordered by display name. Here is the SQL code:

(SELECT '-- Select Coach --' AS QuestionOption, -3 AS QuestionValue)
UNION ALL
(SELECT '(No User Account)' AS QuestionOption, -2 AS QuestionValue)
UNION ALL
(SELECT DisplayName as QuestionOption, UserID AS QuestionValue
	FROM Users
	WHERE UserID <> 1 AND UserID <> 2)
ORDER BY QuestionOption

When I use this and click Test SQL, it populates the options list as expected and in the correct order. I have tried this with and without "No default value" selected and the behavior is always the same. When I make the field required, it always gets flagged with the Required Field validation error when I try to save the form and resets the combo box to the first option (with or without setting a default value).

This is running Dynamic Forms version 3.3.40.0 on DotNetNuke version 5.1.4.

Thanks,
Ray

Hi Ray--

You need to use "questionoptionvalue" and not questionvalue.  i tested this and it works.

(

SELECT '-- Select Coach --' AS QuestionOption, -3 AS QuestionOptionValue)

UNION ALL

Wow, am I embarrassed. I guess that's what I get for working on this after midnight.

Changing QuestionValue to QuestionOptionValue fixed it. Thanks, Curtis!