At SYSTOPIA, we’ve built and maintain a wide range of tools, from CiviCRM extensions to Drupal modules and more. With over 60 repositories under active management, we’re dedicated to enhancing the CiviCRM ecosystem. In this release, we’re happy to present our SQL Tasks Extension – a SQL driven solution for the configurable execution of CiviCRM actions.
What does it do?
If you use CiviCRM, you’ve surely come across situations where you need to perform specific, repetitive, or highly customized database actions - like updating the membership status of all contacts who haven’t renewed in the last six months. While tasks like this are essential for keeping data up-to-date, manually updating each record would be very time consuming and error-prone.
This is where SQL Tasks steps in. The SQL Tasks Extension was designed to address this common challenge within CiviCRM: performing database actions that are specific, repetitive, or that require custom configurations. While CiviCRM offers many built-in tools, certain complex or unique actions can be difficult to perform efficiently without manual SQL commands or external scripts.
The extension allows the SQL-driven, configurable execution of any of the following generic CiviCRM actions:
- Create activities
- Call any CiviCRM API3 action (API4 is supported in version 2.x+)
- Export to CSV, including zipping, uploading, emailing the results
- Synchronize a SQL result contact ID list with a given tag or group
How does it work?
You can find the SQL Tasks Menu under administer – automation – SQL Tasks. Here, you can create new Tasks and manage or configure existing tasks.
To get started, navigate to SQL Tasks and choose Add New SQL Task. Here, you can define a new task by entering a custom SQL query that performs your desired action. For example, you could set up a task to update all contacts in a specific group or to generate a report on recent contributions. You’ll also find the option to add certain actions, like running a clean up SQL script, which you can use, if wanted.
Once configured, you can save the task for future use. If it’s a task that you’ll need regularly, you can even schedule it to run at specific intervals. For example, you might schedule a weekly SQL task that automatically deactivates expired memberships, or a monthly task to generate custom reports on event participation.
If you want to run the task manually, simply select it from the list and click Execute.
Who is it for?
Due to the fact that the usage of the extension requires quite a bit of technical knowledge, it is recommended to be used by implementers and skilled administrators only. The extension does not have any filters. This means, that if you don‘t know your way around SQL and CiviCRM, it is possible that you will break your system. So, be cautious while working with it.
However, in the hands of power users it becomes a valuable tool that opens up new possibilities for the integration and automation of processes in your organization. For example, you can create a query to find all contacts who have attended a specific event and automatically assign them a corresponding tag. This allows you to target personalized communication and actions to specific groups.
Anything else?
The SQL Tasks Extension is a great example of the benefits of open-source collaboration. While we at SYSTOPIA initially developed the foundation for this extension, it has been significantly optimized thanks to the contributions of Greenpeace Central and Eastern Europe (CEE). Greenpeace CEE, a CiviCRM user since 2017, now has its own dedicated development team that has played a major role in advancing this tool. Therefore, we want to thank them for their valuable contributions!
