Ajax Question - text field spawns listbox

Applications Forms
#1

I have two tables.

1:zipcode with fields zipID (primary key),Zipcode,cityID
2:cityname with fields cityID (primary key),cityname

I wish to have a text field on my form that allows for entry of zipcode and either returns ‘not found’ or returns a selectable listbox of matching cities for the zipcode.
I know how to obtain the city names into an array using sc_lookup and the variable returned by the text field but do not know how to present the listbox on screen.

-------------------->>>>>>

ajax event zipcode ‘On Change’

sc_lookup(citydata,“SELECT zipCode,cityName FROM us_Zipcodes,us_Cities WHERE us_Cities.cityID = us_zipCodes.cityID AND zipCode=’{zipCode}’)”);
if ({citydata} === false)
{
echo “Access error. Message=”. {my_data_erro} ." Call Administrator" ;
}
elseif (empty({citydata}))
{
echo “No such zipcode”;
}
else
{
// how do I place results from cityData array into a listbox and place on the form.
// !!!This is where I need help - need to create a listbox for selection of city names if more than 1 found
;
}

#2

Re: Ajax Question - text field spawns listbox

same here…i need to adjust the select field results OnLoad and with Ajax Event

#3

Re: Ajax Question - text field spawns listbox

You’re over complicating it.
Just setup your zipID field and check the box on the field options that says
“Use AJAX to reload other fields of type Select, Double select, Radio and Checkbox.”
Then select the CityID field from the list that will pull down.
Now go to your CityID field and set it to a select type. In the autolookup box put in the appropriate SQL
(ex: SELECT CityID FROM tbl_Locations WHERE zipID = ‘{zipID}’ )
this will tell Scriptcase to show only a list of citys that match the zipID field in the database table.

#4

Re: Ajax Question - text field spawns listbox

and if I want to do a different select statement in case there is no result? I have to change the SQL standard select statement with this select field.

OnLoad or OnAjaxChange
{field_of_type_select} = sc_lookup(array, "SELECT…);

probably something like this:
{field_of_type_select} = {array};

I am not sure how to get the new array result into the field with type select in order to override the SQL standard select statement…

#5

Re: Ajax Question - text field spawns listbox

I also tried something like this with my field of type select, but that doesn’t work at all…no results at all and AjaxReload stops working with my other select fields:

IF EXISTS (SELECT…)
BEGIN
SELECT…
END
ELSE
BEGIN
SELECT…
END