CiviCRM Community Forums (archive)

Hi Erica,

I believe that there's field level import validation, which means:

If your custom field has the following selection in the option group (values)

1. apple
2. banana
3. orange
4. pine apple

Then when you import, you have to make sure that CSV column contain one of the 4 values or otherwise during the preview step CiviCRM will tell you that the value imported does not match the value defined from the custom field.


Hope it helps,


Cheers!

Thanks guys, that makes sense.

So is there any way to import the multiple choice options from a spreadsheet or should I fix myself a strong cup of coffee and start typing?

Yashoda, the data field will only have one value (I'm using AutocompleteSelect type), but that one value will come from a list of over 300 possible values that need to be choices for this particular field.  If what you're saying is correct then this means I will have to manually enter all 300 values into CiviCRM before importing my data.

Is there a way of importing the 300 multiple choice options (perhaps using SQL?) into CiviCRM prior to importing my data (each record will have only one of the 300 possible values for this field) so that I don't have to manually sit and add 300 "new options" within CiviCRM?

What about if you want to use PHP to define your option list?  This is easily done in Drupal CCK: just paste in any code that returns an associative array, and you're done. Is anything like that possible in Civi?

My scenario is that I want a drop-down list when choosing what semester someone attended at our semester program.  The options would be something like: Fall 2009 | Spring 2010 | Fall 2010 etc.  Entering the options manually wouldn't be a big deal, but of course the list needs to keep growing - every 6 months we would have to add a new option (and whoever's doing it would need to be consistent - that's my worry about manually entered data).

Again, this was super easy in Drupal. Here's the code from the CCK field "Semester":
/**
* Generate list of semesters from 2005 to *now*
* Please resist the temptation to limit this list to more recent semesters here,
* as it will break the display of older entries. Use hook_form_alter on the node form instead.
*/

$month = date('m');
$semester = ($month < 9 ? 1 : 2);
$year = date('Y');

$y = 2005;
$s = 1;
$value = array();

while ($y <= $year){

$sem = ($s == 1 ? 'Spring ' : 'Fall ');

$value[10*$y+$s] = $sem.$y;

$s++;
if ($s >= 3){
$s = 1;
$y++;
}
if ($y >= $year && $s > $semester) $y++;
}

return $value ;This returns an associative array like:
20051 | Spring 2005
20052 | Fall 2005
20061 | Spring 2006... all the way up to the present.

So I'm looking for a similar idiot-proof way to do it in Civi. Relying on humans to manually add new options to the list on a regular basis just sounds like it's asking for trouble. Any ideas?

Thanks everyone for your help, and I don't mean to hijack this thread, my question seemed relevant (and maybe even helpful) to the above questions.

The more ho-hum approach might be
- create the Custom Group via the UI
- add one of the Custom Field options via the UI
- via Phpmyadmin, export that row
- replicate the rows with all your options in a spreadsheet
- convert to a csv and import back in to table.

No, there's no API for it, You will have to use the DAO for the database operations for option list and option value.

I personally think what you are trying to do is probably better suited using quick form to generate the form elements since its nature is dynamic.

Unfortunately I don't have a lot of quick form knowledge

In the interests of further sharing, here's the php to generate the .xml file from a 1 column CSV:

<CustomData>

<CustomGroups>
    <CustomGroup>
      <name>test_network_affiliations</name>
      <title>Test Network Affiliations</title>
      <extends>Organization</extends>
      <extends_entity_column_value>Network</extends_entity_column_value>
      <style>Tab</style>
      <collapse_display>0</collapse_display>
      <help_pre></help_pre>
      <help_post></help_post>
      <weight>9</weight>
      <is_active>1</is_active>
      <table_name>civicrm_value_test_network_affiliations</table_name>
      <is_multiple>0</is_multiple>
      <collapse_adv_display>0</collapse_adv_display>
    </CustomGroup>
</CustomGroups>

<CustomFields>
    <CustomField>
      <label>Test LGA</label>
      <data_type>String</data_type>
      <html_type>AdvMulti-Select</html_type>
      <is_required>0</is_required>
      <is_searchable>1</is_searchable>
      <is_search_range>0</is_search_range>
      <weight>50</weight>
      <is_active>1</is_active>
      <is_view>0</is_view>
      <text_length>255</text_length>
      <note_columns>60</note_columns>
      <note_rows>4</note_rows>
      <column_name>test_lga</column_name>
      <option_group_name>test_lga_import_options</option_group_name>
      <custom_group_name>test_network_affiliations</custom_group_name>
    </CustomField>
</CustomFields>

<OptionGroups>
<OptionGroup>
      <name>test_lga_import_options</name>
      <label>Test LGAs</label>
      <is_reserved>0</is_reserved>
      <is_active>1</is_active>
    </OptionGroup>
</OptionGroups>

<OptionValues>
<?php
$row = 1;
if (($handle = fopen("testdata.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle)) !== FALSE) {
        $num = count($data);
$row++
?>
   <OptionValue>
<?php
        for ($c=0; $c < $num; $c++) {
            echo "      <label>" . $data[$c] . "</label>\n";
            echo "      <value>" . strtolower(str_replace(' ', '_', $data[$c])) . "</value>\n";
            echo "      <name>" . strtolower(str_replace(' ', '_', $data[$c])) . "</name>\n";
        }
?>
      <filter>0</filter>
      <is_default>0</is_default>
      <weight><?php echo $row; ?></weight>
      <is_optgroup>0</is_optgroup>
      <is_reserved>0</is_reserved>
      <is_active>1</is_active>
      <option_group_name>test_lga_import_options</option_group_name>
    </OptionValue>

<?php
    }
    fclose($handle);
}

?>
</OptionValues>

</CustomData>


I got help from my (awesome and generous!) significant other with writing the program so can't take all the credit.   It should help out anyone else who wants to do something similar.