Answers.Body: <p>For the text separator you can use it in sql as CHAR(1), and can use CONCAT() to combine with other strings.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-03-08T13:32:12.900Z
Answers.LastActivityDate: 2021-03-08T13:32:12.900Z
Answers.UserDisplayName: Demerit
Body: <p>looking for some suggestions on best practice implementation for synching external data to CiviCRM custom data.</p>
  <p>I'm using the Joomla 2 CiviCRM User Sync plugin to sync my Joomla 3.9.24 user registration data from Community Builder to CiviCRM 5.33.2.  That works great for basic name/username/email, but I have a couple of custom Community Builder fields I'd also like to sync to the CiviCRM records.</p>
  <p>Community Builder includes a plugin to execute an SQL query via CB action hooks, and that works great for updating CiviCRM fields with simple data types (varchar, integer).  But I have one multi-select CB custom field stored as a text field with separators, which needs to go into a Civi multi-select custom field.  I know CiviCRM has a special &quot;TEXT_SEPARATOR&quot; variable that it uses storing multi-value text strings in its database, but I can't figure out 1) what that character is and 2) how I can write it into my SQL.</p>
  <p>So:  is it even possible to write external SQL to do this? Or is there an API call or other programmatic way I should go about doing these Civi custom field updates?</p>
  <p>Thanks!</p>
  <p>System deets:  Joomla 3.9.24, Community Builder 2.5.0, CiviCRM 5.33.2, Joomla 2 CiviCRM User Sync 2.1.0  (<a href="https://civicrm.org/extensions/joomla-2-civicrm-user-synchronization" rel="nofollow noreferrer">https://civicrm.org/extensions/joomla-2-civicrm-user-synchronization</a>)</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-06T15:02:39.643Z
Comments.Text: For the text separator you can use it in sql as `CHAR(1)`, and can use CONCAT() to combine with other strings.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-08T10:39:12.467Z
Comments.Text: THANK YOU THANK YOU! That was EXACTLY the clue I needed.  Now my CB and Civi are happily sharing data.  (FYI for anyone else who comes across this in future -- Civi's use of separators requires CHAR(1) at the beginning and end of the multi-value text string, not just as delimiters inside the string.)
Comments.UserDisplayName: minbori
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-08T10:42:20.993Z
Comments.Text: Demerit, I don't think I can award a bounty to a comment -- would you mind putting that into an answer so I can credit you properly?
Comments.UserDisplayName: minbori
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-08T13:32:46.057Z
Comments.Text: Ok have put it as an answer.
Comments.UserDisplayName: Demerit
Tags: <custom-fields><joomla><sync>
Title: Joomla 2 CiviCRM User Sync: synching Community Builder multi-value custom fields?
ThreadText: # Joomla 2 CiviCRM User Sync: synching Community Builder multi-value custom fields?
  
  (Asked by: username_0 on 2021-02-25)
  
  looking for some suggestions on best practice implementation for synching external data to CiviCRM custom data.
  
  I'm using the Joomla 2 CiviCRM User Sync plugin to sync my Joomla 3.9.24 user registration data from Community Builder to CiviCRM 5.33.2. That works great for basic name/username/email, but I have a couple of custom Community Builder fields I'd also like to sync to the CiviCRM records.
  
  Community Builder includes a plugin to execute an SQL query via CB action hooks, and that works great for updating CiviCRM fields with simple data types (varchar, integer). But I have one multi-select CB custom field stored as a text field with separators, which needs to go into a Civi multi-select custom field. I know CiviCRM has a special "TEXT\_SEPARATOR" variable that it uses storing multi-value text strings in its database, but I can't figure out 1) what that character is and 2) how I can write it into my SQL.
  
  So: is it even possible to write external SQL to do this? Or is there an API call or other programmatic way I should go about doing these Civi custom field updates?
  
  Thanks!
  
  System deets: Joomla 3.9.24, Community Builder 2.5.0, CiviCRM 5.33.2, Joomla 2 CiviCRM User Sync 2.1.0 (<https://civicrm.org/extensions/joomla-2-civicrm-user-synchronization>)
  
  **Tags:** custom-fields, joomla, sync
  
  > **Comments:**
  > **username_1**: For the text separator you can use it in sql as `CHAR(1)`, and can use CONCAT() to combine with other strings.  
  > **username_0**: THANK YOU THANK YOU! That was EXACTLY the clue I needed.  Now my CB and Civi are happily sharing data.  (FYI for anyone else who comes across this in future -- Civi's use of separators requires CHAR(1) at the beginning and end of the multi-value text string, not just as delimiters inside the string.)  
  > **username_0**: username_1, I don't think I can award a bounty to a comment -- would you mind putting that into an answer so I can credit you properly?  
  > **username_1**: Ok have put it as an answer.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-03-08. Score: 1 | Accepted answer
  
  For the text separator you can use it in sql as CHAR(1), and can use CONCAT() to combine with other strings.
  
  
  ---
  
  