Answers.Body: <p>There is a trick to implement this.  In the example below, I will use it for running <strong>SearchKit queries that combine Contributions and Soft Credits</strong>, so we can answer questions like, &quot;Who has given us $5,000 or more over a lifetime, including soft credits?&quot;</p>
  <p>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 &quot;Example&quot; area - non-technical folks can skip the rest.</p>
  <h2>Overview</h2>
  <p>We use a combination of the &quot;Entity&quot; Search Display, and the SQL Tasks extension.</p>
  <p>The &quot;Entity&quot; 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.</p>
  <h2>SearchKit</h2>
  <ul>
  <li>Create a search using any entity(-ies) you like.</li>
  <li>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. &quot;contact ID&quot; is an integer, so you can use Contact ID to stand in for an integer column in your data.</li>
  <li>Also, pseudoconstant lookups will be done - e.g. if you pick the <code>state_province_id</code> field, the integers you import to that column will be converted to the state/province name with the same ID.</li>
  <li>Go to <strong>Add » DB Entity</strong> on the left.</li>
  <li>Rename the columns. These labels will be used in your future SK queries.</li>
  <li>Rename the display. This will be the name of the entity you choose when creating future SK queries.</li>
  <li>Do <strong>NOT</strong> enable Auto-Refresh - otherwise your data will be replaced by the &quot;real&quot; data from this query.</li>
  </ul>
  <h4>Example</h4>
  <p>Set up the query and search display identically to these two screenshots.</p>
  <p>For technical folks:</p>
  <ul>
  <li>I'm using &quot;Contact Source&quot; to replace &quot;Contribution Type&quot; because they're both alphanumeric - the fields you pick don't matter, so long as they're the same type.</li>
  <li>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.
  <a href="https://i.sstatic.net/Jpx73iP2.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Jpx73iP2.png" alt="enter image description here" /></a>
  <a href="https://i.sstatic.net/YjsRZlIx.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/YjsRZlIx.png" alt="enter image description here" /></a></li>
  </ul>
  <h2>MySQL</h2>
  <p>Non-technical folks who just want contributions + soft credits can skip to &quot;Example&quot;.  Copy that code, you'll use it in the &quot;SQL Tasks&quot; section.</p>
  <ul>
  <li>Using a MySQL client, find the new table, which will begin with <code>civicrm_sk_</code>.</li>
  <li>Note the exact names of the fields.  Here's mine:</li>
  </ul>
  <pre><code>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    |                |
  +------------------------------------------------------+------------------+------+-----+---------+----------------+
  </code></pre>
  <ul>
  <li><p>Write a statement to empty the table, e.g. <code>TRUNCATE civicrm_sk_contributions_and_soft_credits;</code>.</p>
  </li>
  <li><p>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.</p>
  </li>
  <li><p>At this point, you should be able to test this by running the SQL manually, then using your data in a new SearchKit query.</p>
  </li>
  <li><p>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.</p>
  </li>
  <li><p>If your data never changes, you're done! Congratulations.</p>
  </li>
  </ul>
  <h4>Example</h4>
  <pre><code>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;
  </code></pre>
  <h2>SQL Tasks</h2>
  <ul>
  <li>Install and enable the <a href="https://civicrm.org/extensions/sql-tasks-extension-configurable-recurring-tasks" rel="nofollow noreferrer">SQL Tasks</a> extension. SQL Tasks isn't enabled for in-app download, so you must know how to unzip the extension in your CiviCRM extensions directory.</li>
  <li>Go to <strong>Administer » System Settings » SQL Tasks</strong>.</li>
  <li>Click <strong>Add</strong>.</li>
  <li>Fill in the <em>Name</em> and the <em>Execution</em> (how often do you want your data to be refreshed)</li>
  <li>Check the box labeled <strong>Run SQL Script</strong> and paste your SQL into the <strong>SQL Script</strong> box.</li>
  <li>Make sure the SQL Tasks Scheduled Job is enabled, and this Task is enabled.</li>
  <li>You're done!</li>
  </ul>
  <p><a href="https://i.sstatic.net/CU89TMCr.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/CU89TMCr.png" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-30T20:14:53.260Z
Answers.Text: thanks for the time/effort put in to answering this
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-30T17:29:49.793Z
Answers.LastActivityDate: 2024-05-30T17:29:49.793Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>If you're creating your tables using <a href="https://docs.civicrm.org/dev/en/latest/extensions/civix/#generate-entity" rel="nofollow noreferrer">https://docs.civicrm.org/dev/en/latest/extensions/civix/#generate-entity</a> then they should be automatically available like the built-in entities I would think.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-03T19:27:08.153Z
Answers.LastActivityDate: 2021-09-03T19:27:08.153Z
Answers.UserDisplayName: Demerit
Body: <p>I'm loving the features of SearchKit with Afform/Form Builder. Very powerful and straightforward to use so far.</p>
  <p>We have several entities/tables in MySQL that we use in conjunction with Civi tables (joining to civicrm_contact, for example). So far, to bring these entities into the Civi GUI (as a contact summary tab) so people can create new records or view records, I've been creating Pages and Forms in our internal extension with civix. It seems like SearchKit/Afform would really speed up the development of those pages and even allow others on my team to build forms and search displays.</p>
  <p>Is there a way we can have any SQL statement be a source for SearchKit and Form Builder, with ways to incorporate filters as where clauses?</p>
  <p>If not, is there any other way I can get a custom table to be a source table for these tools, so we can bring them in and join them to other entities like Contacts?</p>
  <p>These options of somehow allowing external tables into the tools would be a huge benefit to us and, I'm sure, other developers in the community.</p>
  
Tags: <searchkit><formbuilder>
Title: Custom (Non-CiviCRM) MySQL Tables in SearchKit and Afform?
ThreadText: # Custom (Non-CiviCRM) MySQL Tables in SearchKit and Afform?
  
  (Asked by: username_0 on 2021-09-03)
  
  I'm loving the features of SearchKit with Afform/Form Builder. Very powerful and straightforward to use so far.
  
  We have several entities/tables in MySQL that we use in conjunction with Civi tables (joining to civicrm\_contact, for example). So far, to bring these entities into the Civi GUI (as a contact summary tab) so people can create new records or view records, I've been creating Pages and Forms in our internal extension with civix. It seems like SearchKit/Afform would really speed up the development of those pages and even allow others on my team to build forms and search displays.
  
  Is there a way we can have any SQL statement be a source for SearchKit and Form Builder, with ways to incorporate filters as where clauses?
  
  If not, is there any other way I can get a custom table to be a source table for these tools, so we can bring them in and join them to other entities like Contacts?
  
  These options of somehow allowing external tables into the tools would be a huge benefit to us and, I'm sure, other developers in the community.
  
  **Tags:** searchkit, formbuilder
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2024-05-30. Score: 2
  
  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.
    [![enter image description here](https://i.sstatic.net/Jpx73iP2.png)](https://i.sstatic.net/Jpx73iP2.png)
    [![enter image description here](https://i.sstatic.net/YjsRZlIx.png)](https://i.sstatic.net/YjsRZlIx.png)
  
  ## 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](https://civicrm.org/extensions/sql-tasks-extension-configurable-recurring-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!
  
  [![enter image description here](https://i.sstatic.net/CU89TMCr.png)](https://i.sstatic.net/CU89TMCr.png)
  
  > **Comments:**
  > **username_2**: thanks for the time/effort put in to answering this  
  
  ---
  
  ### Answer by username_3 on 2021-09-03. Score: 2
  
  If you're creating your tables using <https://docs.civicrm.org/dev/en/latest/extensions/civix/#generate-entity> then they should be automatically available like the built-in entities I would think.
  
  
  ---
  
  