How to populate a CB Field with a Country List

So you want to create a drop-down type CB field with a list of countries, but you really dont want to input each country name in the allowable field values through the CB backend interface. We can easily do this by using phpmyadmin to make some additions to the related comprofiler tables. 

Before following the procedure we need to backup our database! Always! Never take unnecessary risks!

First of all you need to create the CB field in the Field Management CB backend. Let's assume that this newly created field is of type 'Drop Down Single Select' and is named cb_countrylist. Also, we will need to manually type in at least one allowable field value (the CB backend interface won't let us save otherwise). At this point it doesn't really matter what the value will be (because we will actually later on delete it) so lets just add the value 'TBD' (stands for to be deleted).

This activity has created an extra row in two of the community builder tables in our Joomla site database. These tables are: the 'jos_comprofiler_fields' table and the 'jos_comprofiler_field_values' table. Using phpmyadmin we can easily see these additions. The second one of these tables, the 'jos_comprofiler_field_values' table is used by the community builder component to store the allowable values of list fields. We can easily find the last entry in this table which should contain five table field values looking something like this:

			fieldvalueid	fieldid	fieldtitle	ordering	sys
			251	69	TBD	1	0

We will actually delete this table row, by clicking on the red X, but first we will write down (or remember) the fieldid value (in this sample case its 69, it will most likely be something different in other database instances) because we will use it later on. Once we have memorized this fieldid value for this 'TBD' row we can delete this row all together. This is done by clicking on the red X and confirming our intentions via the pop-up confirmation window. Lets name this memorized value: YOURFIELDIDVALUE (for later reference).

Now, we need to create a semicolon delimited importable text file of the following form (assuming we are creating a country list of 5 countries):

69;Afghanistan;1
69;Aland;2
69;Albania;3
69;Algeria;4
69;American Samoa;5

Lets name this file 'country_list.txt' and save it on our local hard disk.

Now if we click on the 'jos_comprofiler_field_values' table name in the left column of out phpmyadmin screen, we should see at the very bottom of our browser window (might have to scroll a little) the link:

Insert data from a text file into the table

Clicking on this link will bring us to a form with a number of parameter settings that need to be configured. The only items we need to complete are the 'Location of the text file' parameter which is completed using the browse button (that appears to the right of the parameter) and locating the 'country_list.txt' file we saved earlier on our local hard drive. The other parameter that must be completed is the 'Column names' parameter which should be populated with the value:

fieldid,fieldtitle,ordering

After populating these two parameter fields we click on the Submit button. That's it! We have just populated our countrylist field with 5 allowable values.

A 'full' country_list.txt file is provided in the Joomlapolis download area. All you need to do is replace all instances of fieldid with the YOURFIELDIDVALUE you memorized earlier on.

You can of course use this methodology with other lists (example: US states, currencies, etc).