Add new fields to display for Activities Report

[robertinventor]

Hi there,

This is for the report you get to if you click on a contact and then go to the Activities tab.

We've added a new custom field using Administer >> Customise >> Custom Data.

If you click on the edit link for one of the activities then it shows the custom field. So the custom field is working fine.

However, I need to show the new custom data in the Activities page itself - along with the other entries in the table - with a new column header - and sortable, like the other columns there.

There doesn't seem to be any way to do that in the interface itself, so looks as if some coding is needed.

We have discovered the Activity.tpl and Selector.tpl files. But I can't see how to modify the templates to do what is required. Also no idea how to add hooks to add a new column to the table.

Found several forum posts which might be relevant, but none of them had any actual code samples to look at, and without that, I can't understand the instructions.

I am reasonably techy - I program in low level Windows C - so complex code isn't a problem at all. But I don't have the background of understanding in this area to know where to get started.
 
I don't know how Smarty templates work, don't have any experience of using javascript to work with databases etc. etc.

Also we need a quick solution so I can't spend weeks learning what I need to solve it. Need to try to get it done in the next few days - otherwise we probably will have to say we can't do it and pass up on the contract.

Can anyone help? Like - which files need to be edited, and what actual code needs to be put into them, that sort of thing?

Best of all, any actual code samples?

Or is this something which is just too complicated for a beginner in CiviCRM to do? If so that also is useful to know.

Thanks,

[Donald Lobo]

unfortunately i dont think there is an easy way to do this in current versions of CiviCRM

the base reason being there is a fairly gnarly SQL generation to get all the activities and the info, and there is no hook to modify the query generated (including the sort etc)

There might be other ways of doing it, but they will be semi-hackish AND will involve using hooks and understanding a fair bit of CiviCRM and PHP i.e. intercept the output going to the template (not sure how to do this, and potentially will require a new hook in CiviCRM), find all the activity ids, do a query to get the custom values, add them to the template and display them in a modified template. I'd consider this a fairly advanced project, but if u need help getting started, ping us on IRC

lobo

[robertinventor]

Hi lobo,

Thanks that is useful to know, very helpful.

If we do go with this project then I'll need to learn how CiviCRM works on the job. So, I can imagine might well be able to do stuff like that a bit way down the line, but seems a bit too advanced to do right away from your description.

Also sounds like the sort of thing that - if you don't do it quite exactly right for some reason - might cause problems with upgrading to new versions of CiviCRM. I mean - if you do it without enough understanding of how it all works - which I don't have at this stage.

I wonder if there might be another way to achieve the thing we want to do?

To backtrack a bit - we discovered after taking on the job that there are two groups of users of the site, I think two branches of the company or some such, I'm not well up on the details there - but anyway what it amounts to is that they want an easy way to see which of the activities belong to which of the types of user.

I wonder if perhaps there is some field already built in for instance which we could override in some way to make a distinction which will show up in the Activities page?

Or maybe just some kind of custom report based on Activities template?

I just tried a custom report based on the activities template to see what it is like, it does let you list the activities and distinguish the two types.

I need to consult with the rest of the team to make sure I understand what is required of it, as I'm just helping out a bit with the coding, more later.

Thanks

Robert

[totten]

I had a very similar situation last month.

I wonder if perhaps there is some field already built in for instance which we could override in some way to make a distinction which will show up in the Activities page?

http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviCRM+hook+specification#CiviCRMhookspecification-hookcivicrmtabs

The idea of using hook_civicrm_tabs to replace the "Activities" tab with your own (e.g., with your own "CiviReport") is appealing -- i.e. both are documented so theoretically future-proof. In fact, it would be nice if all the data tables in Civi could be implemented/overloaded with one framework (e.g. CiviReports). </day-dream>

In reality, it looks like a bit of slog -- there are lots of little requirements and bits of logic in the current data-table (e.g. composing the proper links to Create|View|Edit|Delete an activity) that you'd have to recreate. I wound up hacking the current page instead (attached).

[Donald Lobo]

I think one relatively easy solution might be:

1. add some new hooks (one or two) that allows the columns and rows to be extended by the hook

2. The hook can add/remove/change columns and then modify the row object to do similar changes

3. Fix the templates so it uses the above definitions rather than hard coding stuff (for now, we can skip this stuff and folks can use a customized template)

4. The one disadvantage is, you will not be able to sort on this field (since that is a change to the query)

Seems like the above changes will meet the customization needs of quite a few requests in the past few months. Any developers willing to take this on? We can help out on IRC

lobo

[xavier]

I think one relatively easy solution might be:

1. add some new hooks (one or two) that allows the columns and rows to be extended by the hook

2. The hook can add/remove/change columns and then modify the row object to do similar changes

3. Fix the templates so it uses the above definitions rather than hard coding stuff (for now, we can skip this stuff and folks can use a customized template)

Don't know if this helps, but we did something somehow similar for a list of participants (adding extra columns). we used the buildform hook to fetch all the ids, did one big query "SELECT all the extra fields FROM civicrm_participant, all the other tables WHERE id in $thelistofidsseperatedbycomma";

and added the column. It shouldn't be rocked science to have a generic template that foreach the headers and columns in the rows.

As for the filter, we simply put all the participants and used the jQuery plugin to deal with the sort and filtering.

[robertinventor]

Hi everyone, just to say - we are in the process of finding out more about the requirements of the users.

I understand that you are exploring ideas for future development of civicrm, and it is great if  my question does help with development.

But right now we need a solution I can apply right away for our current problem.

One thing I wondered is - well as it is at present there is an extra field added for the event when you create or edit an Activity.

So original idea was to just display that field to the user as a new column.

But since that is tricky to do - is it to do with the way the database and the html is linked together?

If so maybe instead of creating a new column in the activity table, could either pre-filter the table - or else modify an existing column.

First a simple but clumsy solution - lets call the two types of activity A and B. Well could just require the user to prefix the Subject for the event with A or B

So the events would be e.g.

A - meeting to ask about asset transfer
B - phone call

Or whatever.

Every time user creates a new event they are advised (in help about how to use the web site) to prefix the subject with A or B as appropriate.

That already would go some way towards a solution apparently. It needs to let you sort so the A or the B come first chronologically within each section - or them all mixed together chronologically,

Next idea then is to have a separate event field in the database - but somehow hook in so the activity table gets it prefixed, or suffixed, automatically to the subject - not as an extra column, just editing the subject.

Another idea is - maybe individual administrators are of type A or B - if so then could require them to prefix their name with one or the other when they get a new account.

But - at this stage became clear I need to know more about how it all works, e.g. might the same administrator need to make both A and B type events or is it always one or the other.

So - am finding out more and will get back to you again.

But interested to know meanwhile - is the issue to do with interrogating the database - or is it more to do with getting the data into the html. I rather suspect the latter partly from cursory look at the code, and also because of the error message about numbers of columns.

If it is to do with the linking of database with html you would think you could do quite a lot with the existing structure by dynamically modifying one of the fields e.g. activity subject as described - or else maybe by pre-filtering the data before it gets to the code.

With databases, one technique iI've come across before is to create a temporary table and populate it in this case with only A or only B type events - or in case where user wants to see both events on the same page - modify the subjects in the temporary table as appropriate - and send the temporary table to the template rather than the original database table - and finally delete the table once done.

 - or whatever, depends on how smarty templates and javascript databases work and also on whether this sort of thing has a performance impact - but sounds like the sort of thing that might be feasible one way or another)

[xavier]

But right now we need a solution I can apply right away for our current problem.

What I did for participants (using the buildForm hook to do an extra query fetching the needed data), and modifying the template could be applied now in your case too.

First a simple but clumsy solution - lets call the two types of activity A and B. Well could just require the user to prefix the Subject for the event with A or B

Too clunky even for my tastes I doubt you'll get your users do it systematically

X+

[robertinventor]

Yes - that's the problem especially with events, likely to get slight mis-spellings etc even if they do remember to do it. Luckily you can step in to edit the subject to fix it if they do - but it may mean extra work for someone to do that.

Yes if you were able to add an extra column to the activities table to show extra fields - does sound like what I need to do - are there any issues with future compatibility with updates of civicrm for that approach?

I need to find out a bit more just to be sure that I understand the problem properly, and to make something that will work well for the users, not require any unnecessary repeated steps every time they add a new activity for instance.

When it comes to the actual coding, if this is the best approach to solve it, then I'll need a few pointers to fill out your description a bit - which files to edit, and probably some details about how you implement the hooks and query the database etc.

Thanks,


Thanks,

[robertinventor]

Hi Xavier,

Okay found out a bit more and it sounds as if what you suggest is on the right lines for us.

It's a two state required field A or B, and it needs to be attached to the activity. The same admin will want to create some A type activities and some B type activities - and all activities are one or the other type.

Admins will want to look at the list of all the activities they created and see which were type A and which were type B. Also need to see list of activities for an organisation and see which are A and which are B.

So does seem definitely that modifying the activity table to add an extra column is the best way to go, and need to pick up the A or B from the activity (and not e.g. from the contact or any of the other fields). And editing the activity subject each time would work after a fashion but not a very satisfactory solution.

So - I wonder, can you spell out what to do in a bit more detail?

Don't know if this helps, but we did something somehow similar for a list of participants (adding extra columns). we used the buildform hook to fetch all the ids, did one big query "SELECT all the extra fields FROM civicrm_participant, all the other tables WHERE id in $thelistofidsseperatedbycomma";

and added the column. It shouldn't be rocked science to have a generic template that foreach the headers and columns in the rows.

As for the filter, we simply put all the participants and used the jQuery plugin to deal with the sort and filtering.

Can you fill that out a bit? A google search turns up lots of posts about buildform hooks, but they are from users who apparently already know all about them, and almost no example code to show how they are used, so far anyway.

Which file is it in, what sort of thing is needed, ...

I did find the Activity.tpl and Selector.tpl. I tried adding a new column to those by just duplicating the code

in Selector.tpl

Code: [Select]

   <th class='crm-contact-activity_subject'>{ts}Subject{/ts}
   </th>



and in Activity.tpl

Code: [Select]

       <td class="crm-activity-type crm-activity-type_{$row.activity_type_id}">{$row.activity_type}</td>

which created a new table with an extra row - but obviously, didn't fill it in properly, and got error message about too many columns.

At the end of Selector.tpl there's the function:

Code: [Select]


function buildContactActivities{/literal}{$context}{literal}( filterSearch ) {
    if ( filterSearch ) {
        {/literal}{$context}{literal}oTable.fnDestroy();
    }
    var context = {/literal}"{$context}"{literal};
    var columns = '';
    var sourceUrl = {/literal}'{crmURL p="civicrm/ajax/contactactivity" h=0 q="snippet=4&context=$context&cid=$contactId"}'{literal};

    var ZeroRecordText = {/literal}{ts}'No matches found'{/ts}{literal};
    if ( cj('.crm-activity-selector-'+ context +' select#activity_type_filter_id').val( ) ) {
      ZeroRecordText += {/literal}{ts}' for Activity Type = "'{/ts}{literal} +  cj('.crm-activity-selector-'+ context +' select#activity_type_filter_id :selected').text( ) + '"';
    } else {
      ZeroRecordText += '.';
    }

    {/literal}{$context}{literal}oTable = cj('#contact-activity-selector-' + context ).dataTable({
        "bFilter"    : false,
        "bAutoWidth" : false,
        "aaSorting"  : [],
        "aoColumns"  : [
                        {sClass:'crm-contact-activity-activity_type'},
                     {sClass:'crm-contact-activity_subject'},
                  //  {sClass:'crm-contact-activity_subject'},
                        {sClass:'crm-contact-activity-source_contact'},
                        {sClass:'crm-contact-activity-target_contact', bSortable:false},
                        {sClass:'crm-contact-activity-assignee_contact', bSortable:false},
                        {sClass:'crm-contact-activity-activity_date'},
                        {sClass:'crm-contact-activity-activity_status'},
                        {sClass:'crm-contact-activity-links', bSortable:false},
                        {sClass:'hiddenElement', bSortable:false}
                       ],
        "bProcessing": true,
        "sPaginationType": "full_numbers",
        "sDom"       : '<"crm-datatable-pager-top"lfp>rt<"crm-datatable-pager-bottom"ip>',
        "bServerSide": true,
        "bJQueryUI": true,
        "sAjaxSource": sourceUrl,
        "iDisplayLength": 25,
        "oLanguage": { "sZeroRecords":  ZeroRecordText },
        "fnDrawCallback": function() { setSelectorClass{/literal}{$context}{literal}( context ); },
        "fnServerData": function ( sSource, aoData, fnCallback ) {
            aoData.push( {name:'contact_id', value: {/literal}{$contactId}{literal}},
                         {name:'admin',   value: {/literal}'{$admin}'{literal}}
            );
            if ( filterSearch ) {
                aoData.push(      
                    {name:'activity_type_id', value: cj('.crm-activity-selector-'+ context +' select#activity_type_filter_id').val()}
                );               
            }
            cj.ajax( {
                "dataType": 'json',
                "type": "POST",
                "url": sSource,
                "data": aoData,
                "success": fnCallback
            } );
        }
    });
}



which presumably needs to be modified in some way?

Code: [Select]


                     {sClass:'crm-contact-activity_subject'},
looks as if it should have an extra entry added there to match the new column.

Remember I have never worked with smarty templates or with javascript for databases before. I can probably pick it up reasonably quickly but need a few pointers to get started. Ideally some example code fragments - or pseudo code - and advice about which files need to be modified.

Thanks,

Robert

[xavier]

It's a two state required field A or B, and it needs to be attached to the activity. The same admin will want to create some A type activities and some B type activities - and all activities are one or the other type.

Admins will want to look at the list of all the activities they created and see which were type A and which were type B. Also need to see list of activities for an organisation and see which are A and which are B.

So does seem definitely that modifying the activity table to add an extra column is the best way to go, and need to pick up the A or B from the activity (and not e.g. from the contact or any of the other fields).

I don't understand why you aren't using custom fields for activities. Seems to be made for your needs.

X+

[robertinventor]

Yes we are using those - check it's the right thing.

Go to Administer >> Customize >> Custom Data

Then "Add Set of Custom Fields"

This then adds a custom field to all the activities, which is just what we want.

But the problem then is that the custom field doesn't show up in any of the Activities reports. You only see it when you view or edit one of the activity rows in the report.

The users need to be able to list the activities with this custom field set to value A, the ones with it set to B, and to see a complete list of all the activities with the A and B ones marked accordingly.

So that's where I started to look at the template to see if it could be modified to achieve this by adding an extra column for the custom field values.

Is that any clearer?

[robertinventor]

The idea was by adding an extra column, if user sorts by that column they can put all the A events at top (chronological) or all the B ones at top - or by sorting all the events chronologically then they can show all the events in time order and then look in the new column to see if they are A or B.

[robertinventor]

To show the sort of problems you have as a newbie understanding it, take the example buildform hook:

Code: [Select]

/**
 * Set a default value for an event price set field.
 */
function example_civicrm_buildForm($formName, &$form) {
  if ($formName == 'CRM_Event_Form_Registration_Register') {
    if ($form->getAction() == CRM_Core_Action::ADD) {
      $defaults['price_3'] = '710';
      $form->setDefaults($defaults);
    }
  }
}

The basic idea of the hook is clear enough. It gets called with the form name as a parameter and you check to see if the form is the one you want to modify. If it is then you modify the $form argument which then modifies the created form in the code that calls the hook.

But - where is the documentation to find out what members $form has - and what they all do?

How do you find what the form name is for the report page you want to modify?

Where is the documentation for CRM_Core_Action - a google search turns up nothing particularly obvious by way of documentation.

Where does the variable  $defaults come from in this code? Or the 'price_3' ?

I'm used to working with the Microsoft Windows C, or OpenGL - which has every function documented, and lots of source code examples - and if you don't know how it works you can find boilerplate stuff at codeproject or codeguru etc. Often the "official" documentation is a bit cryptic or sparse and occasionally it is even out of date or incorrect in some of its details - but it is a starting point, and you find forums and websites which deal with those issues too.

I'm not used to this sort of coding environment.

Where did you all find out how to code it?? Or is there some site somewhere which is just not showing up in the search results for some reason?

I don't need to know how the code works for boilerplate stuff, if there are a few example samples you can take them and modify them and expect to learn how it works in detail later on as needed.

As I said in my first post, if this project is beyond my capabilities at this stage, then that is just as useful to know, as it will save everyone a lot of time.

If that is the situation then I should bow out of it, and the best I can do is to recommend that the users modify the Activity subject with an appropriate suffix A or B as required. Clumsy but if there is no other solution will have to go with that idea. Or suggest they try and find another coder with previous experience of Civicrm.

Thanks!

[robertinventor]

Sorry, just discovered the codebase and the flossmanual.
http://en.flossmanuals.net/civicrm
and
http://civiapi.idealworld.org/api/civicrm

My fault, not sure how I missed them, even had link in initial email to me about the issue from another developer in the team.

I'll need to spend a few hours reading those then all of this will make some more sense at least, I'm sure.

More later.

Thanks,