Dropdown for City, State and Country

[SVydelingum]

Hi Everyone,

Really need help here. We have already imported around 5000 records in Sugar and made some field customisation and layout change. We now realise it will be better and for us to have the address both shipping and billing to become dropdown (city, state and Country)

Please can you help toward pointing me on how we can get thsi done using the existing values in the corresponding fields. and the ability to add more value in the future.

Thanks
Savyn

[Billwobo]

I had the same question and someone pointed me there:
http://www.sugarcrm.com/wiki/index.p...s_to_dropdowns

But I'm not sure what to make of it, I didn't try it. Let me know how it turns out if you do.

[ptemplin7]

The Wiki idea will work IFF (If and only if) The fields in the current DB will match up exactly with the new dropdown values.

This looks more like a script to correct illegal values will be needed.
Go ahead and steal the en_us_etc... to get the country list, State list, etc... (If you need the us list let me know I already have it.

Is your data consistent? Note the WIki is for 4.5.1 not 5.0 so make sure you know the differences.

Let us/me know if you could use some help?

Paris

[nhollands]

The Wiki idea will work IFF (If and only if) The fields in the current DB will match up exactly with the new dropdown values.

This looks more like a script to correct illegal values will be needed.
Go ahead and steal the en_us_etc... to get the country list, State list, etc... (If you need the us list let me know I already have it.

Is your data consistent? Note the WIki is for 4.5.1 not 5.0 so make sure you know the differences.

Let us/me know if you could use some help?

Paris

HI Paris,

Thank you for offering to help. The list I have is UK cities etc. I am looking at the wiki and getting lost but will try it anyway. I was also thinking of creating a seperate table for city, state etc and next to the city field to have a search icon to do a select from the table, and on select the necessary state or city to populate the field. I think this will be easier and also less changes to the core Sugar code.

Many thanks
--
Savyn

[DragonflyMaster]

Hi all,
here we made it our way:

1. built separate db tables for Country, State/Region, Province, City
2. built 4 dropdowns, each one reading the db and showing one of the above data
3. linked the dropdowns together so that if we select UK as Country, the other dropdowns show british regions, provinces and cities only
4. wrote javascript that transpose the selection of each dropdown to billing or shipping address fields.

So basically we left untouched data in OOB address fields, giving users the chance to select data from dropdown in editview and showing them in the same OOB address fields in detailview.

[SVydelingum]

Hi all,
here we made it our way:

1. built separate db tables for Country, State/Region, Province, City
2. built 4 dropdowns, each one reading the db and showing one of the above data
3. linked the dropdowns together so that if we select UK as Country, the other dropdowns show british regions, provinces and cities only
4. wrote javascript that transpose the selection of each dropdown to billing or shipping address fields.

So basically we left untouched data in OOB address fields, giving users the chance to select data from dropdown in editview and showing them in the same OOB address fields in detailview.

Hi DragonflyMaster,

This is nice any chance of getting the code etc. or some information, e.g how you group the address field when in detailview and making the custom dropdown calling the mysql table.. Any help appreciated.

Thank you in advance
Savyn

[DragonflyMaster]

Hi DragonflyMaster,

This is nice any chance of getting the code etc. or some information, e.g how you group the address field when in detailview and making the custom dropdown calling the mysql table.. Any help appreciated.

Thank you in advance
Savyn

Hi,
look at these two threads of mine:
HowTo build related dropdowns in Sugar 5
Link Dropdown-DB table
Combining infos from the two, you should be able to create dynamic dropdown linked to DB tables.
Don't hesitate to ask if you need help.

[SVydelingum]

Hi,
look at these two threads of mine:
HowTo build related dropdowns in Sugar 5
Link Dropdown-DB table
Combining infos from the two, you should be able to create dynamic dropdown linked to DB tables.
Don't hesitate to ask if you need help.

Hi,

I followed the thread on Link Dropdown DB and created a table with cities, and drop down called city_drop

I place the following code at the end of the en_us_lang.php, but unfortunately the whole sugar system is giving page cannot be displayed in IE or Firefox.

Any help appreciated.
Thanks
Savyn

Ps using Sugarcrm 5.0e with Mysql

$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass)

$dbname = 'sugarcrm';
mysql_select_db($dbname);

$myQuery = "select * from sugar_city";
$myResult=mysql_query($myQuery);
while ($myRow = mysql_fetch_array($myResult)) {
$myArray [] = $myRow['city_name'] ;
}
$GLOBALS['app_list_strings']['city_drop']= $myArray

[DragonflyMaster]

Hi,

I followed the thread on Link Dropdown DB and created a table with cities, and drop down called city_drop

I place the following code at the end of the en_us_lang.php, but unfortunately the whole sugar system is giving page cannot be displayed in IE or Firefox.

Did you place it in <sugar-root>/custom/include/language/en_us.lang.php ?

$conn = mysql_connect($dbhost, $dbuser, $dbpass)

a ; is missing at the end of this line.

Regards

[SVydelingum]

Did you place it in <sugar-root>/custom/include/language/en_us.lang.php ?a ; is missing at the end of this line.

Regards

Hi,

Thank you, thank you and thank you. An outside view to the code made the solution. Perfect. Now I need to get the existing data to work or do some sql migrate for the core city field. Any suggestion?

Regards
--
Savyn