0

Any MySQL Civi Gurus out there that know how I can create a query to return all custom value fields with their value per contact id?

I know how to get the occasional custom value, but i need the values not the key field numbers.

My problem is the custom values pick lists are all stored in the same table and so "1" maybe 5 different values and you have to pick the qualifier of each custom custom pick list so that throws off my SQL.

Thank you very much!

  • What you mean with all custom values? Is it the values from all custom data sets? are those sets with multiple records per contact? etc... – Jaap Jansma - CiviCooP Aug 1 '18 at 6:59
  • I am also unsure if you mean custom fields or option values? – ErikH - CiviCooP Aug 1 '18 at 7:46
  • for instance i have a custom field called license type, the values are Ref A, Ref B, Ref C, In the Form 1000,'Referee A' But i'd like that for All custom fields that exist in the database – David Richmond Aug 1 '18 at 18:10
2

This would be difficult to do in pure SQL in a generic way. I assume this is for some type of reporting system you are connecting to CiviCRM and any scripting is not an option.

For each field where it needs a lookup you'd have to do a join, something like:

SELECT c.id, cf1.label AS field1_title, ov1.label AS field1_label, cf2.label AS field2_title, ov2.label AS field2_label FROM civicrm_contact c LEFT JOIN civicrm_value_XXX cv1 ON cv1.entity_id = c.id LEFT JOIN civicrm_custom_field cf1 ON cf1.name = 'the field you want #1' LEFT JOIN civicrm_option_group og1 ON cf1.option_group_id = og1.id LEFT JOIN civicrm_option_value ov1 ON (ov1.option_group_id = og1.id AND ov1.value = cv1._the_database_field_name) LEFT JOIN civicrm_value_YYY cv2 ON cv2.entity_id = c.id LEFT JOIN civicrm_custom_field cf2 ON cf2.name = 'the field you want #2' LEFT JOIN civicrm_option_group og2 ON cf2.option_group_id = og2.id LEFT JOIN civicrm_option_value ov2 ON (ov2.option_group_id = og2.id AND ov2.value = cv2._the_database_field_name) ... more joins for each field...

XXX depends on the actual table name where the custom field is stored, _the_database_field_name is the fieldname as it appears in that table.

I use left joins because depending on how contacts get into the system sometimes they are missing stuff, but you'd still want the contact to be output.

  • Understood, and I have this done in SQL similar to this, but the problem I have is when i need to filter the option group in the WHERE it only gives me people who have every custom field, should I be joining on the value and the options table? "LEFT JOIN civicrm_option_value civicrm_option_value7 ON civicrm_option_value7.value = civicrm_value_usa_custom__referee_data_1.license_68 [...] WHERE civicrm_option_value.option_group_id = 101 " – David Richmond Aug 2 '18 at 15:10
  • And yes this is for a reporting solution to connect up with Civi and scripts are not an option. I need to pull each contact with their associated custom field data but the option_group_id filtering is road blocking me on the where statements – David Richmond Aug 2 '18 at 15:17
  • Do you mean something like "only show me contacts that have a non-blank value entered for license"? – Demerit Aug 2 '18 at 15:24
  • Almost I want to see All Contacts and I want to see all non blank values for all custom fields – David Richmond Aug 3 '18 at 15:18
  • Do you mean then potentially different numbers of columns for each contact? SQL won't do that. Can you maybe edit your original question with a sketch of the output you'd like? – Demerit Aug 3 '18 at 16:28

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.