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)
CHAR(1)
, and can use CONCAT() to combine with other strings.