Blogs

13 Jun 2017

Per a request in StackExchanged I cloned this View and cleaned it up in case it could help in their use case.  The view ensures the block shows activities Assigned to the logged in user.

16 Nov 2016

Quickly built this to provide an example view for this question on Stack Exchange. Obviously the relationship types may need resetting to match which relationships are required.

26 May 2016

This came up as a question for a civi report that could provide a pivot table for contacts created each month v the Source field.

21 Nov 2015

A question on StackExchange had me wondering if a View would provide a solution for showing Civi Memberships and OG Mem

21 Oct 2015

A StackExchange question asked how to provide a link to whatever was the latest newsletter. Seemed like a good fit for a View so tried one out. Here is the file. if it helps, let us know.

28 Aug 2015

I spotted this on StackExchange and Link was okay with me adding this to the repository here.

28 Jul 2015

If you have the CiviCRM Entities module (2.x version)  and the Calendar module then in View

25 Jul 2015

A request on StackExchange seemed like a good candidate for a View using Aggregation that would give a count of

10 Apr 2015

Following on the blog by Eileen about using CiviCRM Entities and Views Bulk Operations (VBO) to update start/end dates of civi Relationships in


25 Mar 2015

It has been possible to make batch changes to CiviCRM data using VBO (Views Bulk Operations) for a while but I only just got around to implementing it for a customer so I am taking the chance to document what I did here!

The recipe is basically

- install civicrm_entity version 2.x & vbo

- create a View

- add and configure the desired 'bulk operation'

And, setting up a View with a bulk operation to modify Relationship End Dates was pretty much that easy

 

Install CiviCRM Entity version 2.x

  • https://www.drupal.org/project/civicrm_entity
  • note that by default you will get 1.x if you 'drush dl'
  • make sure you clear caches afterwards very thoroughly - especially if upgrading from 1.x
  • version 2.x includes a bunch of functionality around Display Suite contributed by Mark Hanna. It also exposes CiviCRM Entities as Content Types. I haven't explored all of this stuff fully

Create a View

  • Below is a shot of my resulting Views page display. This View was based on CiviCRM Relationships
  • Note the operation is visible here

Adding bulk operations

  • Bulk operations are added via the 'Add fields dialog' - choose a bulk operations option as in the picture
  • Set the Bulk operation to be 'modify entity values' and choose the 'properties' you want to be modifiable. You probably want to override the label too.

  • Save....

 

Here is how it looks when they fill it in

 

Cautions

It's not terribly clear what should be filled into those fields - ie. is active should be 0 or 1. It might be better to create a rules component to help your users in some cases.

 

Here is the feature in this example

https://github.com/eileenmcnaughton/feature_manage_relationships_vbo


23 Aug 2014

Because CiviCRM uses ? in its urls it can get tricky to specify when you want a Drupal Block to show on specific donation or other pages.

A solution we found to work well is the https://www.drupal.org/project/block_query which adds a new Visibility Setting called Query.

There you can set eg id=39 which means if you have Pages set to 'only on the listed pages' and eg civicrm/event/register* then the id=39 should cause it to show only on that Event registration page.

If you also need to do a 'and don't show on these other pages' then it works nicely in combination with https://www.drupal.org/project/multiblock 


10 Aug 2014

In many cases, such as for the European Greens, we want to create Drupal pages for some contacts, in their case Parties, so that we can pull together both a collection of information about the contact from Civi, and display related Drupal content, such as News items about the Party. You can see an example of that for the German Bundnis party here. (And I will write this approach up in greater detail later)

But in other cases we may want to do this purely with Civi data, such as places that are 'BreastFeeding Friendly' places for the Australian Breastfeeding Association.

In the case of the latter we can still do this in Views. The attached View does it via the following

- a display that provides a list of the Organisations concerned, with the 'display name' rewritten to be a link to a yet to exist path that would have the 'display name' in it as the second part of the path, eg /clients/European_Greens

- a second display that uses a Contextual Filter that then 'looks' for the display name in the second argument and pulls in the civi data for that contact.

You can see a simple example of this in action here where the page shows a list of some of our clients, and each entry links through to a page for that specific client where other CiviCRM information could be shown.

 

NB to import a View in D7 you either need to be user/1 or to Masquerade as admin.


09 Aug 2014

The European Greens (and other clients since) wanted to have blocks showing News items relating to their Party pages, so that for example a news item that 'node references' Die Grunen would show up on the page about Die Grunen.

The concept is simple enough and can be handled in Views but requires a bit of extra configuration.

For its usage on this site for example, this blog is 'related' to the European Greens (client). So on the Client 'content type' you need a 'node reference' field. And on this blog there is a field where I can use an autocomplete to find European Greens.

Then there is a block that is set to show just on Client content types, and that View has:

  • a Contextual Filter to check the node id that is being viewed,
  • a Relationship ((node reference) (field_<fieldname>) - reverse) to return the nodes that reference the one being viewed.

 

Requires Reference and Node Reference as well as Views of course.

The attached View should work though you may need to tweak the field name for the node reference.

Hat Tip to CodeForbidden for the clear write up on this.

NB to import a View in D7 you either need to be user/1 or to Masquerade as admin.


08 Aug 2014

This has come up repeatedly on the Civi Forums and in this case International Mountain Biking Association needed one for Drupal 6, so I thought I would quickly share a Drupal 6 and Drupal 7 solution as a starting point for others.

The block is built from a View based on 'memberships' and links the Membership to the Civi Contact and then to the Drupal User, which is then limited to being the 'current user'. For non-members you can use the 'no result' option to provide them a link to JOIN - and then if you want this showing only on the User page, set the block to show only at user and user/*

 

NB to import a View in D7 you either need to be user/1 or to Masquerade as admin.


12 Mar 2014

Styling emails can be a bit of a challenge. Sometimes emails look OK on the screen but don't work in the email client. Reasons for this include

  • css stylesheets that are linked to format correctly in the WYSIWIG but not in the emails
  • css inline stylesheets format correctly in the WYSIWIG and in many email clients but do not in Google Webmail
  • layout done via divs works in most email clients but outlook requires table css to give a table look

The first 2 of these can be resolved by converting the css in a stylesheet to inline style information. We have published an extension that uses the cssin library to convert css in a linked stylesheet to inline css. This means that you include a link to your stylesheet in the email - eg

 

<link href="http://mysite.org/style.css?ver=3.0" rel="stylesheet" type="text/css" />

 

The cssinline library will download this stylesheet and construct a set of rules and then parse your emails and alter the html based on the stylesheet. This is done at the last minute as it needs to be done after any tokens are parsed (as some tokens like The CiviCRM views token return html that needs parsing) so by it's nature it must have some performance cost - however the cssinline library does have some caching built in and we have not observed a slow-down on a few thousand emails. We haven't done performance testing on really large mailings. The performance impact should be negligible on emails with no stylesheets and keeping the stylesheets simple should also mitigate any performance effects. We recommend a specially written stylesheet.

 

Installation

As the extension has not been set to automatic download you will need to download from github to your downloads directory - you can use git clone or the 'zip' link. Install as per any normal extension

Links

https://civicrm.org/extensions/css-inline

https://github.com/eileenmcnaughton/nz.co.fuzion.cssinline

https://github.com/djfm/cssin.git


26 Feb 2014

We recently had a situation where we wanted to synchronise CiviCRM relationships to drupal roles using Drupal rules. We had been doing this for a while - creating a Volunteer role when the contact got a new Volunteer relationship and removing it when the relationship was deleted or expired. However, the problem was that the contact might have another relationship that entitled them to have the Drupal role.

There are many ways to skin this cat. However, in this case the customer has some very clever super-users on board so we wanted to look at a code-free solutions so we wanted to build on the civicrm_entity + rules combo

The 2 key things we needed to add to do this were

rules_list_conditions module

And update civicrm_entity to fetch CiviCRM entities by property

Once we had this we had to build up the rule in components.

1) A component to check if the current relationship has the correct criteria for our role. Here our relationship is type = Trainer OR type = Trainee and descripion = advanced. We choose to create a component and choose 'condition set OR'. We are going to pass this condition set a list of CiviCRM relationships and it will respond with a YES or NO as to whether there is an appropriate trainer relationship in the list.

NOTE I found that is was more reliable using isvolunteer as the variable name than is_volunteer which seem to sometimes get misconstructed as is-volunteer. I didn't get to the bottom of this but recommend you run your words together in your variable names to make life easier

 

We add our conditions in this case the conditions are

- is_active = 1 AND    relationship_type_id is one of 46, 47, 49, 58, 59, 60

Active Trainer Conditions

Next we need two more rule components to determine the status of our contact. One component will receive a list of relationship types and return TRUE if one of them meets the criteria in our component above. The other receives a contact object & loads the associated relationships passing them into the rule that checks the list of relationship types. We need to create multiple components because we can't load related entities in the conditions section so we have to create the conditions we want as rule components. The rules_conditional module may help a bit with that - but I didn't figure that part out. So here are parts 2 & 3

2) Set up a RULE component - Check Relationship List for Trainer - set this up to receive a relationship LIST & return a truth value for 'istrainer'.  Setting this up as a Parameter + Provided seems to help ensure it is set & we can deal with it being either 0 or 1 in the calling function

Here we use the 'Add ANY' to add a function that will cycle through our relationship list & continue to the Action (set the value istrainer to TRUE) if ANY of the relationships meet the condition. We add our previous component as our condition by choosing 'Add Condition' to the right of our 'ANY' loop. Note that the links below action (add conditional, add switch, add while) were added by the rules_conditional module which I have not yet experimented with.

3) We are now ready to set up an action component (or rule component I expect) that receives a contact as a parameter and provides 'istrainer' as an output. The key here is that we are adding the action 'Fetch CiviCRM Entity By Property' to load a list of the CiviCRM contact's relationships

This returns a relationshiplist which we pass to our previous rule 'Check Relationship for Trainer'  - this will return the variable which we have called 'isatrainer' in this action set and then we set our variable 'istrainer' to equal that (not sure if there is a way to set istrainer directly). Now we have a rule that will take a CiviCRM contact as a paramer and return YES or NO as to whether that contact has a trainer relationship

 

Phew getting there!! Now we just need to do something based on that - yes another components - 2 actually - 1 will add the trainer role if the contact has the trainer relationship. The other will remove it if not - we'll just look at the add. It receives civicrmcontact & istrainer as variables. Note that in order to have a Drupal user to work on we need to 'Create or Load linked Drupal User Account'

 

We do the remove much the same and FINALLY we can create our complete rule - this one we create as add rule rather than add component - it looks like this & says

"When a CiviCRM relationship is created or Updated Check if they have a relationship that makes them eligible to be a trainer. If yes, then add the role, if no then remove the role (if it exists)

 

 


18 Feb 2014

It is possible to integrate CiviCRM with Xero - although the extension (purposefully) cannot be downloaded through the UI as it defintely requires someone technically savy to be involved in setting it up & maintaining it. In this blog I'm going to explain what you can expect and what you can't expect from the Xero integration. In order to get install and configure it you should refer to the Readme.

 

What you can expect to see?

CiviCRM Xero integration relies on a supporting extension CiviCRM Account Sync. This extension adds a table in your database that stores the connections between your contacts in CiviCRM and your contacts in Xero and a second table that stores connections between contributions in CiviCRM and invoices in Xero. If you have a 'match' in this table then you will see links in CiviCRM that point to the contact in Xero and to a list of their transactions. On the contributions page linked transactions will be also show links to the relevant contribution.

Contact Links From CiviCRM to XeroLink to Xero Invoice From CiviCRM

You will also see a new Xero link in your Menu. This is for configuration only (and actually the CiviCRM Dashboard link goes nowhere at the moment). See the Readme for more.

The only other place you will see Xero integration in the user interface is the Scheduled Jobs page. CiviXero integration runs using the API and scheduled jobs is the interface to this - providing pull & push actions for contacts & invoices along with actions to complete or cancel contributions depending on their status in Xero. Note that start_date really means 'modified_since' & I am considering renaming it to this. More specifically it means "modified in Xero since this date & time".

You can schedule these jobs or run them manually using the 'more' link & 'execute now'

 

Xero on scheduled jobs

 

How do CiviCRM contacts and invoices get matched to Xero?

There are currently 2 ways

1) the invoice or contact was created in CiviCRM and pushed to Xero

2) the invoice or contact was created in Xero, pulled to CiviCRM via the scheduled job and you have taken an action to match them. There is currently not an interface for this so you need to do it via the DB in the civicrm_accounts_* table or a hook in your current module. So far the customers we are supporting use either a highly customised flow where a hook makes sense or a flow where CiviCRM is the master so only #1 occurs. I have a few ideas where we might go with #2 but it will depend on customer requirements (or the contributions of other developers).

 

Which CiviCRM contacts & invoices get pushed to Xero?

In the Xero Settings page you configure which actions should cause a contact to be marked for update in Xero. In the screenshot below you can see that if you create or edit a contact in CiviCRM then an entry for that contact will be created in the synchronisation table (civicrm_accounts_contact*) if it does not exist and the 'needs_update' flag will be set to TRUE. Next time the contactpush job is done all contacts set to 'needs_update' will be created in Xero.

The second selection allows you to specify what 'create' or 'update' actions will cause a contact to be updated. ie. if the contact has not been pushed to Xero nothing will happen if you update the address. But, if it has then when the address is updated it will set the needs_update flag to TRUE and on the next contactpush it will be updated in Xero.

The third selection allows you to specify what 'create' actions will cause the contribution to be pushed through as an invoice into Xero. Currently the choices are to push all Contributions to Xero or none.

It should be noted that if you have custom requirements you can update the synchronisation tables using the relevant api yourself to implement your needs. For this reason the actions of determining who to update / create is separate from the updating.

I am pondering making it so that an activity is created when a sync is scheduled

CiviXero Trigger Config

Why does my contact have a number after it in Xero?

Xero enforces name as a unique field. For unknown reasons this isn't always the case in the real world. So, we have appended the CiviCRM id - if you have an alternative solution you can propose it on github as an issue. Or simply override this as a hook. Appending the id also prevents Xero from automatically matching based on name to existing contacts. This seems like a good thing as it is a 'cautious' approach - if you wind up with duplicates in Xero you should merge the one without an ID after the name into the one with an ID.

What happens to my invoices in Xero?

Invoices are pushed into Xero as 'draft invoices'. You need to approve them for them to show in your reconcilliation screen. By default the account code is 200 which is the CiviCRM default for 'Sales'. This is over-ridable by hook & if we get a customer requirement it would make sense to make this a configurable option.

Once approved it is possible to reconcile a payment from your bank feed with it in Xero or add a payment to it in Xero. Next time the 'invoicepull' job runs the updates to the invoice will be pulled back and stored in the synchronisation table. If the contribution is pending in CiviCRM and has been completed in Xero then next time the 'completetransaction' scheduled job runs the contribution will be updated to 'completed' as will any associated event registrations or memberships. If this completes a registration for which a confirmation receipt is configured that will be sent. If the invoice has been voided in Xero the CiviCRM transaction will be cancelled but at this stage participant records & membership records will not be.

What happens if I delete a Contact in CiviCRM?

If you delete a contact that has not been synced or permanently delete a contact that has been synced it will be removed from the synchronisation table. If you permanently delete a contact that has been previously synced it will be removed and you should see a message on the screen to encourage you to ensure you have taken an appropriate action in Xero. If you delete a contact to trash in CiviCRM then you should see a message alerting you but the synchronisation record will remain.

How do I make Xero link back to CiviCRM?

You can get a link like

Link from Xero to Civi

by configuring Xero with a custom link like this (under settings/ general settings / customise links)

Xero Customise Link

What happens when I merge contacts in CiviCRM?

If the record being removed has been linked with a Xero record and the contact being kept has not then the linkage will be transferred. Otherwise no action will be taken. This is handled by the accountsync module & I have not yet determined the best approach when both have been synced. However, 'trashing' a synced record should trigger an alert per the discussion on deleting above.

 

How can I use the API with CiviXero?

Refer to The API Documentation on for information on how to call the API outside 'scheduled jobs'. The most up-to-date information about the api will always be accessible by going to /civicrm/api/explorer on a site that has CiviXero install. Currently accountsync extension adds create, get & delete accounts for the sync table account_contact and account_invoice along with a special 'account_invoice.getderived' api which outputs a contribution with all it's lineitems & account codes. As of 4.4 this is a bit hard to construct yourself but in 4.5 it is hoped some proposed changes will mitigate this. CiviXero provides pull & push for contacts & invoices and pull for tracking categories (these are not stored in the DB)

What hooks can I use?

You can use standard _pre & _post database hooks on the synchronisation entities (account_contact, account_invoice)

To alter the data being pushed to Xero use

hook_civicrm_accountPushAlterMapped($entity, $contact, &$proceed, &$new_contact);

Note that setting $proceed to FALSE wil block it

To alter the data coming back from Xero before it is stored in the synchronisation tables use

hook_civicrm_accountPullPreSave($entity, $contact, &$save, &$params);

In particular setting $save to FALSE will mean that the contact is effectively skipped.

 

 

 

 


18 Dec 2011

A major challenge for CiviCRM implementers is migrating data from legacy systems into CiviCRM. For large datasets the front end import mechanisms time-out and don't allow for the complexity of the data. For the last year I have been using Migrate module in conjunction with the CiviCRM api. I have recently switched to using migrate module 2 (drupal 6 & drupal 7 compatible) with CiviCRM api v3. To do this I have written a CiviMigrate module which is a lightweight bridge between the two.

In order to use CiviMigrate for Migrate 2 you will need to write your own module. However, there is very little real code in it. In order to write a repeatable import for contacts and their contributions from MySQL tables I have used 34 lines of code.

Migrate module is a very powerful module which allows you to grab data from a variety of 'sources' such as csv, xml, oracle and mysql and map them to a variety of destinations such as node, user, commerce items, comments and ....the CiviCRM api. Some of the nice features of Migrate is that it keeps track of the mappings between the ids of your source data and your CiviCRM destination IDs and allows you to rollback your imports and try again or tweak them and run them as an update (e.g. you could add a field mapping and then re-run the import after setting 'needs_update'. Migrate module gives you several opportunities to manipulate data.

There is a lot of good documentation about migrate module and it ships will a detailed example module so I'm going to write a fairly basic explanation of using Migrate module with the CiviCRM api' as a destination. In my example I am using mysql as the source. I recommend you use this with the version of the API that ships with 4.1 (it's fairly easy to back port to 3.4/ 4.0 although you do need to change one file outside the API folder). The reason for this is that CiviCRM 'advertises'  different field names in 4.1 - for example it 'advertises' 'source' rather than 'contribution_source' on the contribution entity (although it accepts both we now encourage you to use 'source').

I have written a small civimigrate example module & you can enable it along with CiviMigrate.  (I stole the sample data from the beer migration example so it's a bit obscure :-). The example module imports 3 contacts and then attempts to import 4 contributions (one fails as I filtered out the contact it relates to as they are inactive). It demonstrates the fairly important technique of importing data against a contact (or other entity) created in a previous import.

 

 

For a large number of the imports I was working with I wanted to get all the fields from a base table (and then possibly join on other tables / add conditions / concatenate fields or whatever). One of the advantages of using Migrate is that it helps you to explore your data and categorise it in the code itself in a way that can be seen through the web so exposing all fields of the base table was generally useful. Since I was doing this a lot I made it pretty simple to do. Once you have your migration set up you can easily see what fields you have and haven't mapped.

 

The destination is a CiviCRM entity within the CiviCRM UI - you define it by saying

  protected $entity = 'contact'; // this is the default - it refers to the CiviCRM entity

When you click on 'destinations' you can see all the API fields that are advertised for that entity and what, if anything, you have mapped to them

 

The lines to designate a base table as the source are

  protected $base_table = 'civimigrate_example_people_names';
  protected $base_table_id = 'aid'; // name of id field
  protected $base_table_alias = 'names';

 

And then add these lines to generate the query - I have added a condition in here to only include active 'people'

    $query = $this->getQuery($this->base_table,$this->base_table_alias,$this->base_table_id)
              ->condition('status', 1); // only include those with status = 1
    $this->source = new MigrateSourceSQL($query);

As with the destination data you can see which fields you mapped

 

The three mappings in the first job are

    $this->addFieldMapping('first_name', 'names_name'); 
$this->addFieldMapping('contact_type')->defaultValue('Individual');
$this->addFieldMapping(null, 'names_posted')
->description('Not sure what this field is for - ideas?')
->issueGroup('To discuss');

The first 2 can be seen on the 'Mapped tab'

Whereas the last has been categorised 'To discuss' and shows on that tab with the description posted

 

The second job, the contributions, relies on a neat trick of using the results from one migration in the next migration

    $this->dependencies = array('people');
/*
* The line below says 'take the value of the person_id field and look up what it was mapped to
* during the previous peopleMigration import and use that for the contact_id
*/
$this->addFieldMapping('contact_id','payments_person_id')
->sourceMigration('people');

You can see one error on the dashboard. That's because I only imported 3 of the 4 contacts in the table (using the condition 'is_active') so one of the contributions will not import as the contact doesn't exist
This results in an error message and we would probably exclude it from our import.


Note that if you do a Drupal User import after your CiviCRM contact import using the Migrate module it will link the new drupal user accounts & the CiviCRM contacts as long as you have imported emails against both

23 May 2011

A customer recently requested that the little contact search box in the top left corner be made available on all their drupal pages and that it link to the contact's Drupal page.

 

Re-using the Civi-search box seemed a bit too difficult but there was a fairly easy solution to this request using existing drupal modules - finder + views (plus features to deploy).

 

Finder module will create an autocomplete box off any view. I created a basic View based on drupal users. After Pete had finished with it it had 3 fields: contact display name - linked to CiviCRM contact, Drupal user name - linked to Drupal contact & drupal email linked to a mail to. It was also filtered to 'Is Primary Email'. It looks like this (filtered to a single record).

 

CiviFinder view

 

Next up I installed Finder module along with finder_views and finder_autocomplete. Finder appears under site building when installed. I chose to add a Views Finder and selected my View.  I selected both to be able to view the Finder as form and as a block. I then went to 'add element' and chose an autocomplete textfield. There is a 'find items by field' box and I chose the same 3 fields as above. In order to reduce the load I chose the drupal email which is in the same table as the drupal username rather than the Civi email. I'll see if I get a request for the CiviCRM email to be used...

Those few steps were enough that my Finder page now looks like the below. It will autocomplete on username, email or CiviCRM display name.

CiviFinder

The block sits on the left hand side & currently looks like this.

 

After a bit of tinkering when it seemed OK I packaged the view into a Feature which can be downloaded & installed from the fuzion svn (accept the certificate - it's self-signed). One of my biggest gripes about the Finder module is that it isn't possible to export finders as a Feature. The maintainer seems fairly opposed for some reason I can't make sense of. Anyway, I did find I could add the Finder module to the install hook on my Feature so it was possible to package it with the view although it's not possible to maintain it in code going forwards like you can with a Feature.

So, to use our implementation - you need a bunch of modules; Views, Features, Finder, CTools. Once you have all of them enabled just enable civifinder and there will be a block available for you to enable wherever you want on your site