Amy Casey
going with the flow
Posts:54
 |
| 09-02-2010 05:43 PM |
|
I have created a list in host > lists. I enabled sort order so I could change order of typical states list. I need to add a few countries to the bottom of list and I added -Select- to top of list so that the combo box wouldn't appear with the first option already selected.
Then in my form I used code I found here for an SQL driven combo box (subbing in my list name)
select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'HelpDecideAT'
This pulls my list, but sorts it in alpha order, not my custom order, so -select- in middle and the countries aren't at bottom.
Is there anything I can add to the SQL that query that will pull the list in based on my order?
I have very little SQL experience, so any help would be great!!!!
Thanks,
Amy |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-03-2010 09:35 AM |
|
Hello Amy,
Yes, this is very easy to accomplish. Here are the steps:
1.) Delete the "-Select-" from your list.
2.) Go to your combo box which is pulling in the list via SQL.
3.) Place this code above the SQL you already have:
Select '-Select-' As QuestionOption, '-1' As QuestionOptionValue
Union All
4.) Now you should the default value above your list items.
Now as for the order you wish to have your states/countries in, how would you like it organized? States then Countries? Please let me know so I can offer a solution.
If you have any questions please let me know.
Thanks,
Ryan
|
|
|
|
|
Candace
river guide
Posts:2431
|
| 09-03-2010 10:33 AM |
|
Hi Amy,
You will need to do a "Union All" SQL statement similar to this:
Select ‘ – Please Select A State –‘ as questionoption,’ –1‘ as questionoptionvalue
Union all
select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'HelpDecideAT'
Let me know how that works out. Thanks!
Candace |
|
|
|
|
Amy Casey
going with the flow
Posts:54
|
| 09-12-2010 04:09 PM |
|
I wanted to come back and say thanks for the Union all statement, to get the select into the list. Works perfectly.
The list I wanted to make would have states first and then just three additional items at end (canada, international, and virgin islands). I'm pulling them in fine, but the are alphabetical mixed in with states...is there anything to force an order?
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
|
Amy Casey
going with the flow
Posts:54
|
| 09-14-2010 01:38 PM |
|
Thanks Ryan! i had manually built a list to fix in mean time, but I have another form to build tomorrow that could use this, and I'll report back how it goes! |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-14-2010 01:42 PM |
|
Hello Amy,
Yes please let me know how this goes for you, in the meantime if you have any questions please let us know so we can be of assistance to you.
Thanks,
Ryan |
|
|
|
|
Amy Casey
going with the flow
Posts:54
|
| 09-16-2010 06:50 AM |
|
As an update,
Ryan your script works great to add things to end of the standard state list. As a note for anyone else who might come here, I did have to add the:
and ParentID = 221
After Where ListName='Region'
So that it only pulled states
THANKS!
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-16-2010 08:49 AM |
|
Hello Amy,
I'm glad that everything is working great for you. Wow, you're becoming a pro at SQL 
If you have any questions please let us know so we can be of assistance.
Thanks,
Ryan |
|
|
|
|
Amy Casey
going with the flow
Posts:54
|
| 09-20-2010 12:34 PM |
|
Hey guys, me again.
I'm in process of building a whole bunch of order forms on our site, and it would make my life easy to create lists in host > lists and then pull them into the forms as needed.
The problem is, when I create a list of prices, say:
50 = $5.00
100 = $10.00
200 = $20.00
And then use the script above to pull that list in it sorts the 50 after 200 (because 5 comes after 2). Is there any code I can add that would sort this list properly?
Thanks
Amy |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-20-2010 02:50 PM |
|
Hello Amy,
You can do:
select ....... from.... tableName
order by QuestionOptionValue ASC
See if this will work for you.
Please let me know.
Thanks,
Ryan |
|
|
|
|
Amy Casey
going with the flow
Posts:54
|
| 09-21-2010 05:41 AM |
|
I set up the script like this, and it didn't work, 50 still fell below 200, and now Select was at bottom too. Did I do it wrong?
Select '-Select Quantity-' As QuestionOption, '-select-' As QuestionOptionValue
Union All
select Text As QuestionOption, Value As QuestionOptionValue from Lists Where ListName='35.13'
order by QuestionOptionValue ASC
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-21-2010 01:30 PM |
|
Hello Amy,
I'm sorry but I'm having trouble visualizing this issue that you're having. If I had a visual representation of how this was working I would be able to offer better advice. Can you please go to your list and take a screen shot and paste it into this forum thread so I can see what you're working with?
If you have any questions please let me know.
Thanks,
Ryan
|
|
|
|
|
Chad
new to the springs
Posts:3
|
| 09-22-2010 05:40 AM |
|
Here's a great SQL script for pulling US States:
SELECT '-- Select --' as QuestionOption, '-1' as QuestionOptionValue UNION ALL select Text as QuestionOption, Value as QuestionOptionValue from Lists where ListName= 'Region' AND ParentID = ( select EntryID from Lists where value = 'US' ) |
|
|
|
|
Chad Nash
Posts:5260
|
| 09-22-2010 08:33 AM |
|
Thanks for the post Chad (and great name!) -Chad |
|
|
|
|
Amy Casey
going with the flow
Posts:54
|
| 09-22-2010 09:16 AM |
|
Ryan,
Here is list I tested, I didn't build it beyond 3 items, as I knew the 50 would be possible problem. Ultimately the list needs to include 1,000, 2,000, 3,000, 4,000 and 5,000 too, but those sort wrong as well
Then if I use the script above with the "order by QuestionOptionValue ASC" This is what happens: (I fixed the select on bottom thing by using -1 as question option value, but 50 is still at bottom):
 |
|
|
|
|
Limno
skipping stones
Posts:5
|
| 09-22-2010 09:28 AM |
|
Use 50, 100, 200 in your Value field. (a number which can be CASTed to an Integer)
And use the following query:
SELECT '-- Select --' as QuestionOption, '-1' as QuestionOptionValue UNION ALL
SELECT Text As QuestionOption, CASE WHEN ISNUMERIC(Value)=1 THEN CAST (Value as int) END As QuestionOptionValue
FROM dbo.Lists WHERE listName='yourListNamer'
ORDER BY QuestionOptionValue ASC
--DESC |
|
|
|
|
Chad Nash
Posts:5260
|
| 09-22-2010 11:39 AM |
|
Thanks for the useful post!
-Chad
|
|
|
|
|