0

Lables of Custom Values 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?

  • Possible duplicate of MySQL Query to return all custom values – Demerit Aug 4 '18 at 15:24
  • I would like to delete that question and use this one, i was unable to edit the other one and it seemed like I had too many comments can you help with that? – David Richmond Aug 4 '18 at 19:58
  • How could i get the results using this exact SQL query, the proposed duplicate question does not answer this – David Richmond Aug 5 '18 at 23:10
  • 1
    Why do you want to do this in SQL? As you are finding, the relationships between some of the tables are complex. The recommended way to get data out of Civi is to use the API. Maybe step back a bit and tell us what the overall aim is and we can guide you to another solution. – Aidan Aug 6 '18 at 10:12
  • Aim is to produce a SQL query for Crystal reporting to match to another data source, I would like to have all custom fields added to the report in a left join to display their actual labels vs value fields. If I could get the SQL to generate the label vs the value above i could extrapolate all of the fields myself. – David Richmond Aug 6 '18 at 13:56
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
0

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();
    }
  }
}
  • I need to know how to craft the SQL natively I am connecting to Crystal Reports which is another complete system and cannot use the API – David Richmond Aug 6 '18 at 15:33
  • Can you help with native SQL? – David Richmond Aug 14 '18 at 4:44
0

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.

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.