0

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)

Improve this question
4
  • 1
    For the text separator you can use it in sql as CHAR(1), and can use CONCAT() to combine with other strings.
    – Demerit
    Mar 6 '21 at 15:02
  • 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.)
    – minbori
    Mar 8 '21 at 10:39
  • 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?
    – minbori
    Mar 8 '21 at 10:42
  • Ok have put it as an answer.
    – Demerit
    Mar 8 '21 at 13:32
1
+50

For the text separator you can use it in sql as CHAR(1), and can use CONCAT() to combine with other strings.

Improve this answer

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.