How do I add CiviCRM Activity attachment to View

Question

Trying to set up an activity view that displays attachments uploaded to activities.

I can't seem to find the field to add.

Any ideas?

Answer 1

I attended a session yesterday at CiviCon (https://denver2015.civicrm.org/sessions/civicrmdrupal-integration-showcase; the slides are up already) about exposing more Civi tables to Views.

CiviCRM Entity 1.x might display the table you need. CiviCRM Entity 2.x-dev most definitely will. (https://www.drupal.org/project/civicrm_entity) The session slides have lots of details on installation and use.

A sandbox module is also available to expose the Civi tables to Views (https://www.drupal.org/sandbox/markusa/2474103).

The inherent (edited) difference is the sandbox module is just exposing the data to Views. The CiviCRM Entity module will make Civi data available to all of Drupal as entities.

Answer 2

For a project I have build a Drupal CiviCRM views module which extends the Drupal views module with missing data, such as attachments. You can find this module, which is quite project specific, at https://github.com/PUMNL/pum_civicrm_views

Answer 3

Partial answer.

I tried CiviCRM Entities and the Expose Civi tables to Views projects. Entities doesn't seem to fit my needs and did not appear to add the tables I needed to Views.

Expose tables to Drupal Views would have worked but was throwing errors and still required me to write a small custom module.

I think this stuff should be in core anyway, so I started down that route.

In the civicrm.core.inc file I started to play around with the civicrm_file table.

I told Views about the table and defined all pertinent columns: id, entity_id, uploaded date, description and mime type.

/*
     * Add support for CiviCRM attachments
     */
     /*
     * Add CiviCRM file table
     */  
      $data['civicrm_file']['table']['group'] = t('CiviCRM Files');

      $data['civicrm_file']['table']['base'] = array(
        // 
        'field' => 'id',
        'title' => t('CiviCRM Files'),
        'help' => t("View displays CiviCRM Attachments"),
      );
      //Numeric File ID
      $data['civicrm_file']['id'] = array(
        'title' => t('File ID'),
        'help' => t('The numeric ID of the Attachment'),
        'field' => array(
          'handler' => 'views_handler_field_numeric',
          'click sortable' => TRUE,
        ),
        'argument' => array(
          'handler' => 'views_handler_argument_numeric',
          'numeric' => TRUE,
        ),
        'filter' => array(
          'handler' => 'views_handler_filter_numeric',
        ),
        'sort' => array(
          'handler' => 'views_handler_sort',
        ),
      );
        //URI
      $data['civicrm_file']['uri'] = array(
        'title' => t('URI'),
        'help' => t('The URI of the Attachment'),
        'field' => array(
          'handler' => 'views_handler_field_file',
          'click sortable' => TRUE,
        ),
      );
      //File Upload Date
      $data['civicrm_file']['upload_date'] = array(
        'title' => t('File Upload Date'),
        'help' => t('The date the Attachment was uploaded'),
        'field' => array(
          'handler' => 'civicrm_handler_field_datetime',
          'click sortable' => TRUE,
        ),
        'filter' => array(
          'handler' => 'civicrm_handler_filter_datetime',
        ),
        'sort' => array(
          'handler' => 'civicrm_handler_sort_date',
        ),
      );
      //File Description
      $data['civicrm_file']['description'] = array(
        'title' => t('Description'),
        'help' => t('The description of the Attachment'),
        'field' => array(
          'handler' => 'civicrm_handler_field',
          'click sortable' => TRUE,
        ),
        'argument' => array(
          'handler' => 'views_handler_argument_string',
          'numeric' => TRUE,
        ),
        'filter' => array(
          'handler' => 'views_handler_filter_string',
        ),
        'sort' => array(
          'handler' => 'views_handler_sort',
        ),
      );
      //File Mime Type
      $data['civicrm_file']['mime_type'] = array(
        'title' => t('Mime Type'),
        'help' => t('The mime type of the Attachment'),
        'field' => array(
        'handler' => 'views_handler_field_file_filemime',
        'click sortable' => TRUE,
       ),
     );

Then added the join in the Activities section.

 /*
  //TABLE JOINS FOR CIVICRM ACTIVITIES GO HERE!
    /*
     * Add joins for CiviCRM attachments
     */
  $data['civicrm_file']['table']['join']['civicrm_activity'] = array(
    'left_table' => 'civicrm_entity_file',
    'left_field' => 'file_id',
    'field' => 'id',
  );

  $data['civicrm_entity_file']['table']['join']['civicrm_activity'] = array(
    // Directly links to file table
    'left_field' => 'id',
    'field' => 'entity_id',
    'extra' => array(
      array(
        'field' => 'entity_table',
        'value' => 'civicrm_activity',
        'numeric' => FALSE,
      ),
    ),
  );

I'm sure my code can use some clean up as that's not my bag, but when I create a View of Activities, I now get these options:

And this is the resuult:

Struggling to form the url correctly.

I'd like to use the same handler that Civi uses for custom file fields (civicrm_handler_field_custom_file or the non-custom alternative, civicrm_handler_field_file) but when I try either of those, I get a broken handler error.

Those handlers pass the entity id as well as the file id into the url. So the url is formed like:

localhost/civicrm/file?id=[file_id]&eid=[entity_id]#

The error has to do with the entity_id column referenced in those handlers.

Answer 4

Update of Partial Answer

Following on from the partial answer i added the following table definition for the entity_file table. This section of code was added just after the definition for the file table (the first bit of the partial answer). The joins have already been taken care of in the original answer (second bit), this just gives the fields to use to build a working link to download the file

/*
 * Add CiviCRM entity file table
 */  
$data['civicrm_entity_file']['table']['group'] = t('CiviCRM Entity Files');

$data['civicrm_entity_file']['table']['base'] = array(
  // 
  'field' => 'id',
  'title' => t('CiviCRM Entity Files'),
  'help' => t("View displays CiviCRM Attachments create link to download"),
);
//Numeric File ID
$data['civicrm_entity_file']['id'] = array(
  'title' => t('Entity File ID'),
  'help' => t('The numeric ID entity file record of the Attachment'),
  'field' => array(
    'handler' => 'views_handler_field_numeric',
    'click sortable' => TRUE,
  ),
  'argument' => array(
    'handler' => 'views_handler_argument_numeric',
    'numeric' => TRUE,
  ),
  'filter' => array(
    'handler' => 'views_handler_filter_numeric',
  ),
  'sort' => array(
    'handler' => 'views_handler_sort',
  ),
);
  //EntityTable
$data['civicrm_entity_file']['entity_table'] = array(
  'title' => t('Entity Table'),
  'help' => t('The content type associated with the attachment'),
  'field' => array(
    'handler' => 'civicrm_handler_field',
    'click sortable' => TRUE,
  ),
);
//Entity ID
$data['civicrm_entity_file']['file_id'] = array(
  'title' => t('File ID'),
  'help' => t('FK to the ID of the file in civicrm_file table'),
  'field' => array(
    'handler' => 'views_handler_field_numeric',
    'click sortable' => TRUE,
  ),
);
//Entity ID
$data['civicrm_entity_file']['file_id'] = array(
  'title' => t('File ID'),
  'help' => t('FK to the ID of the file in civicrm_file table'),
  'field' => array(
    'handler' => 'views_handler_field_numeric',
    'click sortable' => TRUE,
  ),
);

Answer 5

The link is in the civicrm_entity_file table, which links the activity and the attachment. The attachment itself is in the civicrm_file table.