3

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?

  • Is it a custom attachments field? – Laryn - CEDC.org Apr 23 '15 at 2:01
  • Not the one I'm starting with. It is the standard activity attachment. – pmoz Apr 23 '15 at 10:09
  • I haven't used this functionality but tried a quick install and ran into the same question... Sorry. – Laryn - CEDC.org Apr 23 '15 at 12:41
3

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.

  • Thanks for the info. I've been anxious to wrap my head around this module, but there really isn't much use case or example type stuff out there for it. I think I'll hold on until 2.0 is released and give it a go. – pmoz Apr 24 '15 at 10:29
  • By the way, is there a use for both the sandbox module to expose Civi tables to Views and CiviCRM Entity or are they sort of one or the other? – pmoz Apr 24 '15 at 10:33
  • I think it just depends on what you need. If you're using Entity 2 then you probably don't need the Sandbox. If you don't need Civi data to have the benefit of Drupal entity characteristics, then I imagine the sandbox module will be sufficient and have less overhead. – Allen Hutchison Apr 24 '15 at 15:44
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

2

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:

Attachments added to Group List

Attachments added to Field list

And this is the resuult: Results

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.

  • I ended up using custom file fields for attachments which were much easier to manipulate in our use case. – pmoz Nov 11 '15 at 14:22
2

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,
  ),
);
  • Good work! You should add the changes to github! – pmoz Apr 13 '16 at 23:41
  • had a go at doing this, not sure if it'll work – Bruce May 6 '16 at 21:04
1

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.

  • I have those tables in the settings.php file, but can't seem to find them within views. – pmoz Apr 23 '15 at 10:10
  • Sorry, not too much Views expertise so no idea why they would not show up :-( – ErikH - CiviCooP Apr 23 '15 at 10:40

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.