CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Drupal Modules (Moderator: Donald Lobo) »
  • Setting up an Activity Report View
Pages: [1]

Author Topic: Setting up an Activity Report View  (Read 840 times)

toddR

  • I’m new here
  • *
  • Posts: 5
  • Karma: 0
  • CiviCRM version: 4.2
  • CMS version: 7
Setting up an Activity Report View
October 25, 2012, 12:25:59 pm
Hello,

I'm trying to build a table View that displays 5 columns: Assignee Contact Name, Target Contact Name, Activity Type, Activity Status, and Scheduled Activity Date. I'm using Civi to record referral calls made to our company, and then schedule follow ups 30 days later that are assigned to a specific employee, with the target being the original household that called in for a referral.

I've got the Civi homepage (dashboard?) set up so that when an employee logs in, they see a list of all Follow Ups with the status of "Scheduled", which is good for them to log in and go down the list. From their they edit the activity, make the call to the household and fill in the custom fields, and change the Status to "Completed", which removes it from their dashboard.

I'd like to make this View so I can see/show a table of all Referrals and Follow Ups, what employee they're assigned to, what household they're related to, and the date. Ideally one of the fields would link back to the actual activity's page in Civi, and there could be totals for each activity type/employee at the bottom.

Before I get to that, though, the problem thus far is that "Activity Assignee" and "Activity Target" are not available fields for the table. They are only available as relationships, which I am having a hard time wrapping my head around.

Is this a limitation of Views/Civi integration or am I missing something? If I didn't explain this well enough please let me know. Still don't have too much experience so any help is appreciated, don't hesitate to call me a noob... : p

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Setting up an Activity Report View
October 25, 2012, 01:19:18 pm
Not clear what is the bit that is tripping you up. Is the View a 'contact' view, or an 'activity' view? If you haven't, i suggest you start from an Activity

here is an example that may help if that is where the problem lies - this of course won't just 'import' unless you change out the 'activity type' i am filtering on and replace with one of yours - but that shouldn't be too tricky to spot in the code
Code: [Select]
$view = new view();
$view->name = 'audit_summary';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'civicrm_activity';
$view->human_name = 'Audit summary';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Audit summary';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'role';
$handler->display->display_options['access']['role'] = array(
  3 => '3',
  8 => '8',
);
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'id' => 'id',
  'display_name_2' => 'display_name_2',
  'display_name' => 'display_name',
  'display_name_3' => 'display_name_3',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'id' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'display_name_2' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'display_name' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'display_name_3' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Relationship: CiviCRM Activities: Source Contact */
$handler->display->display_options['relationships']['source_contact_id']['id'] = 'source_contact_id';
$handler->display->display_options['relationships']['source_contact_id']['table'] = 'civicrm_activity';
$handler->display->display_options['relationships']['source_contact_id']['field'] = 'source_contact_id';
/* Relationship: CiviCRM Activity Assignments: Assignee Contact ID */
$handler->display->display_options['relationships']['assignee_contact_id']['id'] = 'assignee_contact_id';
$handler->display->display_options['relationships']['assignee_contact_id']['table'] = 'civicrm_activity_assignment';
$handler->display->display_options['relationships']['assignee_contact_id']['field'] = 'assignee_contact_id';
/* Relationship: CiviCRM Activity Targets: Target Contact ID */
$handler->display->display_options['relationships']['target_contact_id']['id'] = 'target_contact_id';
$handler->display->display_options['relationships']['target_contact_id']['table'] = 'civicrm_activity_target';
$handler->display->display_options['relationships']['target_contact_id']['field'] = 'target_contact_id';
/* Field: CiviCRM Activities: Activity ID */
$handler->display->display_options['fields']['id']['id'] = 'id';
$handler->display->display_options['fields']['id']['table'] = 'civicrm_activity';
$handler->display->display_options['fields']['id']['field'] = 'id';
$handler->display->display_options['fields']['id']['separator'] = '';
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name_2']['id'] = 'display_name_2';
$handler->display->display_options['fields']['display_name_2']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name_2']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name_2']['relationship'] = 'assignee_contact_id';
$handler->display->display_options['fields']['display_name_2']['label'] = 'Auditor';
$handler->display->display_options['fields']['display_name_2']['link_to_civicrm_contact'] = 0;
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name']['id'] = 'display_name';
$handler->display->display_options['fields']['display_name']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name']['label'] = 'Contact person';
$handler->display->display_options['fields']['display_name']['link_to_civicrm_contact'] = 0;
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name_3']['id'] = 'display_name_3';
$handler->display->display_options['fields']['display_name_3']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name_3']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name_3']['relationship'] = 'target_contact_id';
$handler->display->display_options['fields']['display_name_3']['label'] = 'Company';
$handler->display->display_options['fields']['display_name_3']['link_to_civicrm_contact'] = 1;
/* Filter criterion: CiviCRM Activities: Activity Type */
$handler->display->display_options['filters']['activity_type']['id'] = 'activity_type';
$handler->display->display_options['filters']['activity_type']['table'] = 'civicrm_activity';
$handler->display->display_options['filters']['activity_type']['field'] = 'activity_type';
$handler->display->display_options['filters']['activity_type']['value'] = array(
  33 => '33',
);
/* Filter criterion: CiviCRM Contacts: Display Name */
$handler->display->display_options['filters']['display_name']['id'] = 'display_name';
$handler->display->display_options['filters']['display_name']['table'] = 'civicrm_contact';
$handler->display->display_options['filters']['display_name']['field'] = 'display_name';
$handler->display->display_options['filters']['display_name']['relationship'] = 'assignee_contact_id';
$handler->display->display_options['filters']['display_name']['operator'] = 'contains';
$handler->display->display_options['filters']['display_name']['exposed'] = TRUE;
$handler->display->display_options['filters']['display_name']['expose']['operator_id'] = 'display_name_op';
$handler->display->display_options['filters']['display_name']['expose']['label'] = 'Display Name';
$handler->display->display_options['filters']['display_name']['expose']['operator'] = 'display_name_op';
$handler->display->display_options['filters']['display_name']['expose']['identifier'] = 'display_name';
/* Filter criterion: CiviCRM Contacts: Contact Type */
$handler->display->display_options['filters']['contact_type']['id'] = 'contact_type';
$handler->display->display_options['filters']['contact_type']['table'] = 'civicrm_contact';
$handler->display->display_options['filters']['contact_type']['field'] = 'contact_type';
$handler->display->display_options['filters']['contact_type']['relationship'] = 'target_contact_id';
$handler->display->display_options['filters']['contact_type']['value'] = array(
  'Organization' => 'Organization',
);
/* Filter criterion: CiviCRM Activities: Activity Status */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'civicrm_activity';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = array(
  7 => '7',
);
$handler->display->display_options['filters']['status']['exposed'] = TRUE;
$handler->display->display_options['filters']['status']['expose']['operator_id'] = 'status_op';
$handler->display->display_options['filters']['status']['expose']['label'] = 'Activity Status';
$handler->display->display_options['filters']['status']['expose']['operator'] = 'status_op';
$handler->display->display_options['filters']['status']['expose']['identifier'] = 'status';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'audit-summary';
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Setting up an Activity Report View
October 25, 2012, 02:45:56 pm

this also might be helpful to you

http://forum.civicrm.org/index.php/topic,26547.msg112631.html#msg112631

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

toddR

  • I’m new here
  • *
  • Posts: 5
  • Karma: 0
  • CiviCRM version: 4.2
  • CMS version: 7
Re: Setting up an Activity Report View
October 26, 2012, 01:57:28 pm
Thanks for the help you guys. Pete, it is an Activity View. I imported the view code you provided and tweaked it to my activity types, and it's darn close to what I was looking for! Very helpful. There are two things I'd like to add, though.

The first and more important feature is that I'd like to output the Activity Date field as a link to the actual activity page it represents in Civi. I've done this before in a Drupal only View using a hidden Path field, but this time I'm unsure how that would work. Secondly, I see the option for a "Global: Math expression" field and I'm wondering if I could use that to display totals. For example, if I filter the table to Follow Ups: Completed, could I get it to display a numerical value for how many of those exist? Thanks again for your time and assistance.

Here is the code for the View as of now:

Code: [Select]
$view = new view();
$view->name = 'activities';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'civicrm_activity';
$view->human_name = 'Activities';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Activities';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'role';
$handler->display->display_options['access']['role'] = array(
  3 => '3',
  8 => '8',
);
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'id' => 'id',
  'display_name_2' => 'display_name_2',
  'display_name' => 'display_name',
  'display_name_3' => 'display_name_3',
  'activity_date_time' => 'activity_date_time',
  'status' => 'status',
  'activity_type' => 'activity_type',
);
$handler->display->display_options['style_options']['default'] = 'activity_date_time';
$handler->display->display_options['style_options']['info'] = array(
  'id' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'display_name_2' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'display_name' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'display_name_3' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'activity_date_time' => array(
    'sortable' => 1,
    'default_sort_order' => 'desc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'status' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'activity_type' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Relationship: CiviCRM Activities: Source Contact */
$handler->display->display_options['relationships']['source_contact_id']['id'] = 'source_contact_id';
$handler->display->display_options['relationships']['source_contact_id']['table'] = 'civicrm_activity';
$handler->display->display_options['relationships']['source_contact_id']['field'] = 'source_contact_id';
/* Relationship: CiviCRM Activity Assignments: Assignee Contact ID */
$handler->display->display_options['relationships']['assignee_contact_id']['id'] = 'assignee_contact_id';
$handler->display->display_options['relationships']['assignee_contact_id']['table'] = 'civicrm_activity_assignment';
$handler->display->display_options['relationships']['assignee_contact_id']['field'] = 'assignee_contact_id';
/* Relationship: CiviCRM Activity Targets: Target Contact ID */
$handler->display->display_options['relationships']['target_contact_id']['id'] = 'target_contact_id';
$handler->display->display_options['relationships']['target_contact_id']['table'] = 'civicrm_activity_target';
$handler->display->display_options['relationships']['target_contact_id']['field'] = 'target_contact_id';
/* Field: CiviCRM Activities: Activity ID */
$handler->display->display_options['fields']['id']['id'] = 'id';
$handler->display->display_options['fields']['id']['table'] = 'civicrm_activity';
$handler->display->display_options['fields']['id']['field'] = 'id';
$handler->display->display_options['fields']['id']['exclude'] = TRUE;
$handler->display->display_options['fields']['id']['separator'] = '';
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name_2']['id'] = 'display_name_2';
$handler->display->display_options['fields']['display_name_2']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name_2']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name_2']['relationship'] = 'assignee_contact_id';
$handler->display->display_options['fields']['display_name_2']['label'] = 'Counselor';
$handler->display->display_options['fields']['display_name_2']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['display_name_2']['hide_empty'] = TRUE;
$handler->display->display_options['fields']['display_name_2']['link_to_civicrm_contact'] = 1;
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name']['id'] = 'display_name';
$handler->display->display_options['fields']['display_name']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name']['label'] = 'Contact person';
$handler->display->display_options['fields']['display_name']['exclude'] = TRUE;
$handler->display->display_options['fields']['display_name']['link_to_civicrm_contact'] = 0;
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name_3']['id'] = 'display_name_3';
$handler->display->display_options['fields']['display_name_3']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name_3']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name_3']['relationship'] = 'target_contact_id';
$handler->display->display_options['fields']['display_name_3']['label'] = 'Household';
$handler->display->display_options['fields']['display_name_3']['hide_empty'] = TRUE;
$handler->display->display_options['fields']['display_name_3']['link_to_civicrm_contact'] = 1;
/* Field: CiviCRM Activities: Scheduled Activity Date */
$handler->display->display_options['fields']['activity_date_time']['id'] = 'activity_date_time';
$handler->display->display_options['fields']['activity_date_time']['table'] = 'civicrm_activity';
$handler->display->display_options['fields']['activity_date_time']['field'] = 'activity_date_time';
$handler->display->display_options['fields']['activity_date_time']['label'] = 'Activity Date';
$handler->display->display_options['fields']['activity_date_time']['date_format'] = 'custom';
$handler->display->display_options['fields']['activity_date_time']['custom_date_format'] = 'm/d/y';
/* Field: CiviCRM Activities: Activity Status */
$handler->display->display_options['fields']['status']['id'] = 'status';
$handler->display->display_options['fields']['status']['table'] = 'civicrm_activity';
$handler->display->display_options['fields']['status']['field'] = 'status';
/* Field: CiviCRM Activities: Activity Type */
$handler->display->display_options['fields']['activity_type']['id'] = 'activity_type';
$handler->display->display_options['fields']['activity_type']['table'] = 'civicrm_activity';
$handler->display->display_options['fields']['activity_type']['field'] = 'activity_type';
$handler->display->display_options['fields']['activity_type']['hide_empty'] = TRUE;
/* Field: Global: Custom text */
$handler->display->display_options['fields']['nothing']['id'] = 'nothing';
$handler->display->display_options['fields']['nothing']['table'] = 'views';
$handler->display->display_options['fields']['nothing']['field'] = 'nothing';
/* Filter criterion: CiviCRM Activities: Activity Type */
$handler->display->display_options['filters']['activity_type']['id'] = 'activity_type';
$handler->display->display_options['filters']['activity_type']['table'] = 'civicrm_activity';
$handler->display->display_options['filters']['activity_type']['field'] = 'activity_type';
$handler->display->display_options['filters']['activity_type']['value'] = array(
  14 => '14',
  52 => '52',
);
$handler->display->display_options['filters']['activity_type']['exposed'] = TRUE;
$handler->display->display_options['filters']['activity_type']['expose']['operator_id'] = 'activity_type_op';
$handler->display->display_options['filters']['activity_type']['expose']['label'] = 'Activity Type';
$handler->display->display_options['filters']['activity_type']['expose']['operator'] = 'activity_type_op';
$handler->display->display_options['filters']['activity_type']['expose']['identifier'] = 'activity_type';
$handler->display->display_options['filters']['activity_type']['expose']['multiple'] = TRUE;
$handler->display->display_options['filters']['activity_type']['expose']['remember_roles'] = array(
  2 => '2',
  3 => '3',
  1 => 0,
);
$handler->display->display_options['filters']['activity_type']['expose']['reduce'] = TRUE;
/* Filter criterion: CiviCRM Contacts: Display Name */
$handler->display->display_options['filters']['display_name']['id'] = 'display_name';
$handler->display->display_options['filters']['display_name']['table'] = 'civicrm_contact';
$handler->display->display_options['filters']['display_name']['field'] = 'display_name';
$handler->display->display_options['filters']['display_name']['relationship'] = 'assignee_contact_id';
$handler->display->display_options['filters']['display_name']['operator'] = 'contains';
$handler->display->display_options['filters']['display_name']['exposed'] = TRUE;
$handler->display->display_options['filters']['display_name']['expose']['operator_id'] = 'display_name_op';
$handler->display->display_options['filters']['display_name']['expose']['label'] = 'Display Name';
$handler->display->display_options['filters']['display_name']['expose']['operator'] = 'display_name_op';
$handler->display->display_options['filters']['display_name']['expose']['identifier'] = 'display_name';
$handler->display->display_options['filters']['display_name']['expose']['remember_roles'] = array(
  2 => '2',
  1 => 0,
  3 => 0,
);
/* Filter criterion: CiviCRM Activities: Activity Status */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'civicrm_activity';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = array(
  1 => '1',
  2 => '2',
);
$handler->display->display_options['filters']['status']['exposed'] = TRUE;
$handler->display->display_options['filters']['status']['expose']['operator_id'] = 'status_op';
$handler->display->display_options['filters']['status']['expose']['label'] = 'Activity Status';
$handler->display->display_options['filters']['status']['expose']['use_operator'] = TRUE;
$handler->display->display_options['filters']['status']['expose']['operator'] = 'status_op';
$handler->display->display_options['filters']['status']['expose']['identifier'] = 'status';
$handler->display->display_options['filters']['status']['expose']['multiple'] = TRUE;
$handler->display->display_options['filters']['status']['expose']['remember_roles'] = array(
  2 => '2',
  1 => 0,
  3 => 0,
);
$handler->display->display_options['filters']['status']['expose']['reduce'] = TRUE;

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'activities';
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Drupal Modules (Moderator: Donald Lobo) »
  • Setting up an Activity Report View

This forum was archived on 2017-11-26.