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