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 Lis