1

I need to import addresses using the API. Some addresses go over the CiviCRM limit. For instance street_address has a limit of 96 characters. I would like to know where are these limits defined in CiviCRM or where are they documented? I don't want to use the trial and error method to find out.

1

These are defined in MySQL. Adjusting the length of those tables should cover this.

With some data entry, making the changes through the interface can increase the length of the tables in the system.

John

  • Ok, I did ALTER TABLE civicrm_contact MODIFY external_identifier VARCHAR(128); but I still get the error Value for external_identifier is 67 characters - This field has a maxlength of 64 characters. – ñull Nov 8 '16 at 10:42
  • Yep ditto. needed to import Case Subjects that were > 128 so just altered the VarChar length in db but the import still rejects the longer ones. – petednz - fuzion Oct 7 '17 at 6:49
  • Do you have logging turned on, and did you clear caches? – JohnFF Oct 7 '17 at 13:19
1

That field is defined at https://github.com/civicrm/civicrm-core/blob/master/xml/schema/Core/Address.xml#L92. It is run through GenCode.php to create the definition of the field and table used in the database installation. This also creates the definition in DAO file (CRM/Core/DAO/Address.php):

'street_address' => array(
      'name' => 'street_address',
      'type' => CRM_Utils_Type::T_STRING,
      'title' => ts('Street Address') ,
      'description' => 'Concatenation of all routable street address components (prefix, street number, street name, suffix, unit
  number OR P.O. Box). Apps should be able to determine physical location with this data (for mapping, mail
  delivery, etc.).
',
      'maxlength' => 96,
      'size' => CRM_Utils_Type::HUGE,
      'import' => true,
      'where' => 'civicrm_address.street_address',
      'headerPattern' => '/(street|address)/i',
      'dataPattern' => '/^(\d{1,5}( [0-9A-Za-z]+)+)$|^(P\.?O\.\? Box \d{1,5})$/i',
      'export' => true,
      'table_name' => 'civicrm_address',
      'entity' => 'Address',
      'bao' => 'CRM_Core_BAO_Address',
      'html' => array(
        'type' => 'Text',
      ) ,

I bet that the generic import code truncates varchars to the maxlength of the field. So you could override this file to change that behaviour.

FWIW, on a buildkit development site you can set things up to re-run the GenCode so that change the xml for schema will be reflected in the regenerated DAO file.

If you want to propose a change to the schema in core, you will need to include in the PR an ALTER TABLE query in the appropriate file for the release in https://github.com/civicrm/civicrm-core/tree/master/CRM/Upgrade/Incremental/sql, eg 4.7.28.mysql.tpl. Increasing the field length doesn't involve loss of data or any need to convert the existing data, so it would be a straightforward change.

However, I'm not sure whether proposing a change to a field that has been unchanged since version 1.1 will get the community approval ideally required for a schema change. FWIW, I'm mildly supportive and this has caused us grief over the years. I'd also try to review what impact a longer field might have on layout onscreen, in address labels in core, and in html message templates, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.