When Drupal Users are created a CiviCRM record is automatically created.
A request on StackExchange seemed like a good candidate for a View using Aggregation that would give a count of how many Memberships had changed from eg Grace to Expired in a particular period, and by Membership Type.
Note that you may need to login or Masquerade as user/1 to get access to the Import View interface at admin/structure/views/import.
If you found this useful, please consider contributing to one of our projects to improve CiviCRM, such as 'Getting CiviCRM working on Drupal 8'.
Following on the blog by Eileen about using CiviCRM Entities and Views Bulk Operations (VBO) to update start/end dates of civi Relationships in bulk, another client asked for a quick solution for them confirming who attended an event. This was a free event, they had sent out invites, and wanted a quick way to mark people as 'attending' so that they could then bulk update the Pariticipant Status for those who showed up.
Using Entities and VBO made this a simple task. They can now collect names at the door, and submit them in bulk at the end of the process.
The process involved is
- create a View based on CiviParticipant
- add the VBO field and set it to 'modify entity values' and select 'Participant Status ID'
- add other fields eg Event Title and Participant status so they can keep an eye on ensuring Participants are 'as expected'
- set some filters, eg expose the 'event title' field using 'contains' so the same form can be used over for other events
Then at the event, the 'user' has a screen and just need to check a box for each attendee, and then at the end of the influx
- under Operations select Modify entity values and Execute
- enter the Status ID they want applied (yes not hugely intuitive yet, so user needs to go to civicrm/admin/participant_status?reset=1 and eyeball the ID - which for Attended is '2') and confirm
Here are a couple of screenshots that hopefully back up the description above - and I will attach the View so you can just download and try it out ;-) (requires VBO and CiviEntity modules being enabled of course!)
Set up the Bulk Operations field
Set up your other Fields and Filters
And a screenshot for 'on the night'
In this case there was also the potential for a invitee to bring an unexpected guest. We provided a webform solution for this so that names, email, phone could quickly be recorded on a form with an Event registration and a Relationship to the invited person, and then provided a simple link (open in new tab) from the bulk registration form, so the webform would be prefilled with the 'invited person' as cid1.
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
- 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.
Here is how it looks when they fill it in
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
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
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.
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.
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.
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.
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
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
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
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)
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.
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'
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
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
by configuring Xero with a custom link like this (under settings/ general settings / customise links)
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.