Get Custom CiviCRM Data by ContactID This is not a duplicate

Question

This is option group 110 in civicrm_option_value

I am trying to create a custom query for reporting that pulls the actual Custom Data LABELS ('Green Light','Red Light', etc)vs the custom data VALUES (1,2,3 etc) per the CIVI Terminology in Admin.

The following SQL:

SELECT civicrm_contact.id, civicrm_contact.display_name, civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9, civicrm_value_custom__safe_sport_co_9.background_check_7, civicrm_value_custom__safe_sport_co_9.red_light_override_71, civicrm_value_custom__safe_sport_co_9.override_expiration_72, civicrm_value_custom__safe_sport_co_9.background_check_status_73 FROM civicrm_contact INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id WHERE civicrm_contact.id = 1000

1000,Billy Jenkins,2/27/2018,3/16/2018,0,NULL,1

I actually WANT

1000,Billy Jenkins,2/27/2018,3/16/2018,0,NULL,Green Light

This is micro version of what I actually need which is many more custom data columns but its the same situation, I can't simply join the civicrm_option_value.value field because there are dozens of values of '1' I'm not sure how to segment the 110 group only to pull what I need.

What should I do?

Answer 1

I'll try to answer specifically for this question but this effectively is a duplicate of MySQL Query to return all custom values because it's the exact same technique.

First, look in civicrm_custom_field in the "name" column for the Background Check Status field. I'm going to assume it says "Background_Check_Status" there. If not you'll need to change it below to match.

SELECT civicrm_contact.id,
civicrm_contact.display_name,
civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
civicrm_value_custom__safe_sport_co_9.background_check_7,
civicrm_value_custom__safe_sport_co_9.red_light_override_71,
civicrm_value_custom__safe_sport_co_9.override_expiration_72,
ov1.label AS 'Background Check Status'
FROM
civicrm_contact
INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id
LEFT JOIN civicrm_custom_field cf1 ON cf1.name = 'Background_Check_Status'
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 = civicrm_value_custom__safe_sport_co_9.background_check_status_73)
WHERE civicrm_contact.id = 1000

Since you already knew the option_group_id, you could use that like so, but I find the name is easier on the eyes.

SELECT civicrm_contact.id,
civicrm_contact.display_name,
civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
civicrm_value_custom__safe_sport_co_9.background_check_7,
civicrm_value_custom__safe_sport_co_9.red_light_override_71,
civicrm_value_custom__safe_sport_co_9.override_expiration_72,
ov1.label AS 'Background Check Status'
FROM
civicrm_contact
INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id
LEFT JOIN civicrm_option_value ov1 ON (ov1.option_group_id = 110 AND ov1.value = civicrm_value_custom__safe_sport_co_9.background_check_status_73)
WHERE civicrm_contact.id = 1000

Answer 2

You can use public function alterDisplay(&$rows) function of civicrm reports, and add new column, and use civicrm API to populate those columns for custom fields.

Example code:

public function alterDisplay(&$rows) {

  foreach ($rows as $rowNum => $row) {

    $this->_columnHeaders['XYZ_custom_info'] = array(
      'title' => 'XYZ custom info',
      'type' => CRM_Utils_Type::T_STRING
    );

    try{
      //Getting spouse name
      $field_info = civicrm_api3('Contact', 'get', (
        'return' => "custom_field_id",
        'id' => "",
      ));

      $rows[$rowNum]['XYZ_custom_info'] = $field_info;
    }
    catch(Exception $e) {
      echo 'Message: ' .$e->getMessage();
    }
  }
}

Answer 3

Another way to do this type of reporting in general is to write a cron script that uses the api and periodically writes out the values to another table that has a schema of your choosing, then have Crystal Reports pull data from that table. The data wouldn't be 100% realtime because there'd be a delay between until the next run of your script, but reporting doesn't always need to be realtime.