There is a trick to implement this. In the example below, I will use it for running SearchKit queries that combine Contributions and Soft Credits, so we can answer questions like, "Who has given us $5,000 or more over a lifetime, including soft credits?"
Non-technical folks can follow these instructions specifically to get combined contribution and soft credits. Technical folks can use this for any arbitrary data. At the end of each section will be an "Example" area - non-technical folks can skip the rest.
Overview
We use a combination of the "Entity" Search Display, and the SQL Tasks extension.
The "Entity" Search Display saves the results of a SearchKit query into a new table, which can be used in a second SK query. We generate an Entity Search Display, then we use SQL Tasks to empty out the data and fill it with our own data.
SearchKit
- Create a search using any entity(-ies) you like.
- Add the same number of columns to the report as your SQL data has. The data types of each column matter - so make sure that the columns you choose match the data types of your actual data. E.g. "contact ID" is an integer, so you can use Contact ID to stand in for an integer column in your data.
- Also, pseudoconstant lookups will be done - e.g. if you pick the
state_province_id field, the integers you import to that column will be converted to the state/province name with the same ID.
- Go to Add » DB Entity on the left.
- Rename the columns. These labels will be used in your future SK queries.
- Rename the display. This will be the name of the entity you choose when creating future SK queries.
- Do NOT enable Auto-Refresh - otherwise your data will be replaced by the "real" data from this query.
Example
Set up the query and search display identically to these two screenshots.
For technical folks:
- I'm using "Contact Source" to replace "Contribution Type" because they're both alphanumeric - the fields you pick don't matter, so long as they're the same type.
- You don't have to join to another entity like I did here unless you need more fields to choose from, or you want a specific pseudoconstant lookup - e.g. I did so for the financial type ID lookup.
MySQL
Non-technical folks who just want contributions + soft credits can skip to "Example". Copy that code, you'll use it in the "SQL Tasks" section.
- Using a MySQL client, find the new table, which will begin with
civicrm_sk_.
- Note the exact names of the fields. Here's mine:
DESCRIBE civicrm_sk_contributions_and_soft_credits;
+------------------------------------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------------------------------+------------------+------+-----+---------+----------------+
| _row | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id | int(10) unsigned | YES | MUL | NULL | |
| display_name | varchar(128) | YES | MUL | NULL | |
| email_primary_email | varchar(254) | YES | MUL | NULL | |
| phone_primary_phone | varchar(32) | YES | MUL | NULL | |
| address_primary_street_address | varchar(96) | YES | MUL | NULL | |
| address_primary_supplemental_address_1 | varchar(96) | YES | MUL | NULL | |
| address_primary_city | varchar(64) | YES | MUL | NULL | |
| address_primary_state_province_id | int(10) unsigned | YES | MUL | NULL | |
| address_primary_postal_code | varchar(64) | YES | MUL | NULL | |
| Contact_Contribution_contact_id_01_total_amount | decimal(20,2) | YES | MUL | NULL | |
| Contact_Contribution_contact_id_01_financial_type_id | int(10) unsigned | YES | MUL | NULL | |
| source | varchar(255) | YES | MUL | NULL | |
+------------------------------------------------------+------------------+------+-----+---------+----------------+
Write a statement to empty the table, e.g. TRUNCATE civicrm_sk_contributions_and_soft_credits;.
Write one or more statements to fill this table with your own data. Since I'm combining data from multiple tables I found it simpler to write multiple SQL statements.
At this point, you should be able to test this by running the SQL manually, then using your data in a new SearchKit query.
If you need to make changes to your parent SK columns, you will likely have to redo any SK queries built on top of it.
If your data never changes, you're done! Congratulations.
Example
TRUNCATE civicrm_sk_contributions_and_soft_credits;
INSERT INTO civicrm_sk_contributions_and_soft_credits
(id, display_name, email_primary_email, phone_primary_phone, address_primary_street_address, address_primary_supplemental_address_1, address_primary_city, address_primary_state_province_id, address_primary_postal_code, Contact_Contribution_contact_id_01_total_amount, Contact_Contribution_contact_id_01_financial_type_id, source)
SELECT cc.id, display_name, email, phone, street_address, supplemental_address_1, city, state_province_id, postal_code, total_amount, financial_type_id, 'Hard'
FROM civicrm_contact cc
LEFT JOIN civicrm_address ca ON ca.contact_id = cc.id
LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
LEFT JOIN civicrm_phone cp ON cp.contact_id = cc.id
INNER JOIN civicrm_contribution money ON money.contact_id = cc.id;
INSERT INTO civicrm_sk_contributions_and_soft_credits
(id, display_name, email_primary_email, phone_primary_phone, address_primary_street_address, address_primary_supplemental_address_1, address_primary_city, address_primary_state_province_id, address_primary_postal_code, Contact_Contribution_contact_id_01_total_amount, Contact_Contribution_contact_id_01_financial_type_id, source)
SELECT cc.id, display_name, email, phone, street_address, supplemental_address_1, city, state_province_id, postal_code, amount, financial_type_id, 'Soft'
FROM civicrm_contact cc
LEFT JOIN civicrm_address ca ON ca.contact_id = cc.id
LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
LEFT JOIN civicrm_phone cp ON cp.contact_id = cc.id
INNER JOIN civicrm_contribution_soft ccs ON ccs.contact_id = cc.id
INNER JOIN civicrm_contribution contribution ON contribution.id = ccs.contribution_id;
SQL Tasks
- Install and enable the SQL Tasks extension. SQL Tasks isn't enabled for in-app download, so you must know how to unzip the extension in your CiviCRM extensions directory.
- Go to Administer » System Settings » SQL Tasks.
- Click Add.
- Fill in the Name and the Execution (how often do you want your data to be refreshed)
- Check the box labeled Run SQL Script and paste your SQL into the SQL Script box.
- Make sure the SQL Tasks Scheduled Job is enabled, and this Task is enabled.
- You're done!
