Dynamic Forms & Dynamic Registration require that you return at least two columns for a SQL Driven questions: QuestionOption and QuestionOptionValue
QuestionOption = Text User will see
QuestionOptionValue = Value behind the Item that User will see
For Dynamic Views, you can have SQL Driven Combo Boxes and Radio Buttons for Search Filters. This requires that you return at least two columns named SearchOption and SearchOptionValue.
SearchOption = Text User will see
SearchOptionValue = Value behind the Item that User will see
This means that you can use the queries below in Dynamic Forms / Dynamic Registration or Dynamic Views by simply changing the alias of the columns being returned.
Example:
Query for Dynamic Forms / Dynamic Registration:
SELECT RoleName AS QuestionOption, RoleId AS QuestionOptionValue FROM Roles
Query for Dynamic Views:
SELECT RoleName AS SearchOption, RoleId AS SearchOptionValue FROM Roles
Below are queries that I use often:
1.) Get States
---------------------------------------------------------------------------------------------------
SELECT '-- Select State --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue, Text AS SortOrder
FROM Lists
WHERE ListName = 'Region' AND ParentID = 221
ORDER BY SortOrder
---------------------------------------------------------------------------------------------------
2.) Get States and Territories:
---------------------------------------------------------------------------------------------------
SELECT '-- Select State --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue, Text AS SortOrder
FROM Lists
WHERE ListName = 'Region'
ORDER BY SortOrder
---------------------------------------------------------------------------------------------------
3.) Get Countries with United States at the top:
---------------------------------------------------------------------------------------------------
SELECT '-- Select Country --' AS QuestionOption, '-1' AS QuestionOptionValue
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue
FROM Lists
WHERE ListName = 'Country' AND Text = 'United States'
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue
FROM Lists
WHERE ListName = 'Country' AND Text <> 'United States'
---------------------------------------------------------------------------------------------------
4.) All Users by “Last Name, First Name” including Admin Accounts
---------------------------------------------------------------------------------------------------
SELECT '—Select Person --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT LastName + ', ' + FirstName AS QuestionOption, UserID AS QuestionOptionValue, LastName + ', ' + FirstName As SortOrder
FROM Users
ORDER BY SortOrder
---------------------------------------------------------------------------------------------------
5.) All Users by “Last Name, First Name” without Admin Accounts:
---------------------------------------------------------------------------------------------------
SELECT '—Select Person --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT top 10 A.LastName + ', ' + A.FirstName AS QuestionOption, A.UserID AS QuestionOptionValue, A.LastName + ', ' + A.FirstName As SortOrder
FROM Users A
INNER JOIN UserRoles B ON A.UserID = B.UserID
INNER JOIN Roles C ON B.RoleID = C.RoleID
WHERE B.UserID NOT IN(Select Z.UserID from UserRoles Z INNER JOIN Roles X ON Z.RoleID = X.RoleID WHERE Z.UserID = B.UserID AND X.RoleName LIKE '%Administrators%')
ORDER BY SortOrder
---------------------------------------------------------------------------------------------------
6.) All Users by “Last Name, First Name” who have logged into their account the past 30 days:
---------------------------------------------------------------------------------------------------
SELECT '—Select Person --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT top 10 A.LastName + ',' + A.FirstName AS QuestionOptionValue, A.UserID AS QuestionOptionValue, A.LastName + ',' + A.FirstName As SortOrder
FROM Users A
INNER JOIN ASPNET_Users B on A.UserName = B.UserName
INNER JOIN ASPNET_Membership C ON B.UserID = C.UserID
WHERE CONVERT(DATE,C.LastLoginDate,101) > DATEADD(DD, -30, CONVERT(DATE, GETDATE(), 101))
---------------------------------------------------------------------------------------------------
These are among the most used SQL Queries for Combo Boxes that I’ve come across.
Please let us know if you have any questions or if this blog post was helpful.