You are here

Home

SuiteCRM / SugarCRM Cheat Sheet

Contents

  • 1. Find id of record to unlink. 
    SQL> SET @uid = 'f232e8d0-9b82-20f7-7c35-5c1a5ce94e0a';
  • 2. Find entry in Target List table: 
    SQL> SELECT plp.id, plp.related_type, plp.date_modified, plp.deleted
     , pl.name
  FROM prospect_lists_prospects AS plp
  JOIN prospect_lists AS pl ON plp.prospect_list_id = pl.id
 WHERE plp.related_id = @uid
;
  • 3. Update entry in Target List table: 
    SQL> START TRANSACTION;
SQL> UPDATE prospect_lists_prospects
   SET deleted = 1
 WHERE related_id = @uid
   AND id = '5b3047b2-3b45-1a6e-e6fb-5d3fe1efa93b'
;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
SQL> COMMIT;

Find Target Lists of Target/Prospect

Result:

+--------------------------------------+--------------+------------+-----------+------------------------+
| id                                   | first_name   | last_name  | type      | target_lists           |
+--------------------------------------+--------------+------------+-----------+------------------------+
| e9678a2a-ee96-dbc6-6fe0-5d0e7be4ac45 | Thomas       | Benderskiy | Prospects | Target EN              |
| e97b4caf-317a-9d97-58b0-5d0e38b81959 | Marko        | Marklund   | Prospects | Target DACH            |
| e98c07e8-64e7-8731-8ad9-5d0e7a6e8844 | Avik         | Beyer      |           |                        |
| e98106c2-f0cf-f453-162b-5d0e80422eb9 | Christian    | Ribas      | Prospects | Target DACH, Target EN |
+--------------------------------------+--------------+------------+-----------+------------------------+

Accounts:

SELECT a.id, a.name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM accounts AS a
  JOIN accounts_cstm AS ac ON a.id = ac.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = a.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE ac.interests_c LIKE '%^brman^%'
   AND a.deleted = 0
 GROUP BY a.id, a.name, plp.related_type
;

Contacts:

SELECT c.id, c.first_name, c.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM contacts AS c
  JOIN contacts_cstm AS cc ON c.id = cc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = c.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE cc.interests_c LIKE '%^brman^%'
   AND c.deleted = 0
 GROUP BY c.id, c.first_name, c.last_name, plp.related_type
;

Leads:

SELECT l.id, l.first_name, l.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM leads AS l
  JOIN leads_cstm AS lc ON l.id = lc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = l.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE lc.interests_c LIKE '%^brman^%'
   AND l.deleted = 0
 GROUP BY l.id, l.first_name, l.last_name, plp.related_type
;

Targets / Prospects:

SELECT p.id, p.first_name, p.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM prospects AS p
  JOIN prospects_cstm AS pc ON p.id = pc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = p.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE p.deleted = 0
 GROUP BY p.id, p.first_name, p.last_name, plp.related_type
;

List all email addresses of all accounts

SELECT ea.email_address, eabr.bean_module
  FROM email_addr_bean_rel AS eabr
  JOIN email_addresses AS ea ON ea.id = eabr.email_address_id
 WHERE eabr.bean_module = 'Accounts'
  AND eabr.deleted = 0
;
+------------------------+-------------+
| email_address          | bean_module |
+------------------------+-------------+
| info@top.org           | Accounts    |
| first.last@company.com | Accounts    |
+------------------------+-------------+

Objects without Target List

Contacts without Target List:

SELECT c.first_name, c.last_name
  FROM contacts AS c
  LEFT JOIN prospect_lists_prospects AS plp ON c.id = plp.related_id AND plp.deleted = 0
 WHERE c.deleted = 0
   AND plp.id IS NULL
;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Hans       | Maier     |
| Fritz      | Müller    |
+------------+-----------+

Add Contacts without Target List to a Target List:

SQL> SELECT id, name FROM prospect_lists WHERE deleted = 0 ORDER BY name;
+--------------------------------------+----------------------------------------+---------+
| id                                   | name                                   | deleted |
+--------------------------------------+----------------------------------------+---------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH                            |       0 |
| 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | Target EN                              |       0 |
+--------------------------------------+----------------------------------------+---------+

SQL> SELECT id INTO @plid FROM prospect_lists WHERE deleted = 0 AND name = 'Target DACH';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, c.id, 'Contacts', CURRENT_TIMESTAMP(), 0
  FROM contacts AS c
  LEFT JOIN prospect_lists_prospects AS plp ON c.id = plp.related_id AND plp.deleted = 0
 WHERE c.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Leads without Target List:

SELECT l.first_name, l.last_name
  FROM leads AS l
  LEFT JOIN prospect_lists_prospects AS plp ON l.id = plp.related_id AND plp.deleted = 0
 WHERE l.deleted = 0
   AND plp.id IS NULL
;

Add Leads without Target List to a Target List:

SQL> SELECT id, name FROM prospect_lists WHERE deleted = 0 ORDER BY name;
+--------------------------------------+----------------------------------------+---------+
| id                                   | name                                   | deleted |
+--------------------------------------+----------------------------------------+---------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH                            |       0 |
| 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | Target EN                              |       0 |
+--------------------------------------+----------------------------------------+---------+

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, l.id, 'Leads', CURRENT_TIMESTAMP(), 0
  FROM leads AS l
  LEFT JOIN prospect_lists_prospects AS plp ON l.id = plp.related_id AND plp.deleted = 0
 WHERE l.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Prospects without Target List:

SELECT p.first_name, p.last_name, p.primary_address_country
  FROM prospects AS p
  LEFT JOIN prospect_lists_prospects AS plp ON p.id = plp.related_id AND plp.deleted = 0
 WHERE p.deleted = 0
   AND plp.id IS NULL
;

Add Prospects without Target List to a Target List:

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, p.id, 'Prospects', CURRENT_TIMESTAMP(), 0
  FROM prospects AS p
  LEFT JOIN prospect_lists_prospects AS plp ON p.id = plp.related_id AND plp.deleted = 0
 WHERE p.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Find PLP without Objects (Contacts/Leads/Prospects)

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN prospects AS p on p.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Prospects'
   AND p.id IS NULL
;

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN leads AS l on l.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Leads'
   AND l.id IS NULL
;

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN contacts AS c on c.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Contacts'
   AND c.id IS NULL
;

Find Prospect List Entries per Prospect Type

SELECT related_type, COUNT(*)
  FROM (
SELECT DISTINCT plp.related_type, plp.related_id
  FROM prospect_lists_prospects AS plp
 WHERE plp.deleted = 0
) AS d GROUP BY related_type
;
+--------------+----------+
| related_type | COUNT(*) |
+--------------+----------+
| Contacts     |     1959 |
| Leads        |     5891 |
| Prospects    |     8970 |
+--------------+----------+

Quick set-up on Ubuntu 18.04

# apt-get update
# apt-get install mariadb-server php apache2 unzip

Check: http://192.168.56.101

# cd /tmp
# wget https://suitecrm.com/files/160/SuiteCRM-7.10/480/SuiteCRM-7.10.22.zip

# grep -r DocumentRoot /etc/apache2/
/etc/apache2/sites-available/000-default.conf:  DocumentRoot /var/www/html

# cd /var/www/html
# unzip /tmp/SuiteCRM-7.10.22.zip
# ln -s SuiteCRM-7.10.22 SuiteCRM
# cd SuiteCRM
# chown -R www-data:www-data .
# chmod -R 755 .
# chmod -R 775 cache custom modules themes data upload
# chmod 775 config_override.php 2>/dev/null

# apt-get install php-mysqli php-xml php-zip php-curl php-imap php-gd

# There is still a bug in SuiteCRM so 6M is not enough!
# cat >/etc/php/7.2/mods-available/SuiteCRM.ini <<_EOF
[PHP]
upload_max_filesize = 7M
_EOF

# phpenmod -v 7.2 -s apache2 SuiteCRM
# systemctl restart apache2

SQL> CREATE DATABASE suitecrm;
SQL> CREATE USER 'suitecrm'@'localhost' IDENTIFIED BY '...';
SQL> GRANT ALL ON *.* TO 'suitecrm'@'localhost';

Installation: http://192.168.56.101/SuiteCRM/install.php

Add targets from old target lists to new target list

1. Find target lists.

SELECT id, name FROM prospect_lists
 WHERE deleted = 0 AND list_type = 'default'
   AND name IN ('Target DACH', 'Target D', 'Target CH', 'Target A')
;
+--------------------------------------+-------------+
| id                                   | name        |
+--------------------------------------+-------------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH |
| 8eb0ec25-6bbb-68de-d44f-5d0e262cd93d | Target D    |
| d23490c8-99eb-f298-6aad-5d0e28e7fd4f | Target A    |
| d81e9aae-ef60-fca2-7d99-5d0e269de1c0 | Target CH   |
+--------------------------------------+-------------+

2. Find id of new target list:

SELECT id INTO @target_list_id FROM prospect_lists
 WHERE name = 'NewTL'
;
SELECT @target_list_id;
+--------------------------------------+
| @target_list_id                      |
+--------------------------------------+
| 627129c3-7432-9fc4-efb4-5e58e8e04e06 |
+--------------------------------------+

3. Insert targets from old target lists into new target list:

INSERT INTO prospect_lists_prospects
SELECT UUID(), @target_list_id, related_id, related_type, CURRENT_TIMESTAMP(), 0
  FROM prospect_lists_prospects
 WHERE deleted = 0
   AND prospect_list_id IN
       (   SELECT id FROM prospect_lists
            WHERE deleted = 0 AND list_type = 'default'
              AND name IN ('Target DACH', 'Target D', 'Target CH', 'Target A')
       )
;

Undelete accounts

SET @id = '39e1dc35-0085-94ca-1251-61b1df6abd61';

SELECT id, name, deleted FROM accounts WHERE id = @id AND deleted = 1;
+--------------------------------------+------------+---------+
| id                                   | name       | deleted |
+--------------------------------------+------------+---------+
| 39e1dc35-0085-94ca-1251-61b1df6abd61 | XXXXXXX    |       1 |
+--------------------------------------+------------+---------+

SELECT * FROM accounts_bugs WHERE account_id = @id;
SELECT * FROM accounts_cases WHERE account_id = @id;
SELECT * FROM accounts_contacts WHERE account_id = @id;
SELECT * FROM accounts_opportunities WHERE account_id = @id;

SELECT * FROM cases WHERE account_id = @id;
SELECT * FROM documents_accounts WHERE account_id = @id;
SELECT * FROM leads WHERE account_id = @id;
SELECT * FROM projects_accounts WHERE account_id = @id;
SELECT * FROM surveyresponses WHERE account_id = @id;

SELECT * FROM address_book WHERE bean_id = @id;
SELECT * FROM custom_fields WHERE bean_id = @id;
SELECT * FROM email_addr_bean_rel WHERE bean_id = @id;
SELECT * FROM emails_beans WHERE bean_id = @id;
SELECT * FROM users_last_import WHERE bean_id = @id;
SELECT * FROM address_book WHERE bean_id = @id;

START TRANSACTION;
UPDATE accounts SET deleted = 0 WHERE id = @id AND deleted = 1;
UPDATE accounts_contacts SET deleted = 0 WHERE account_id = @id AND deleted = 1;
COMMIT;
...

tags: 
SuiteCRM
SugarCRM
crm
target
target_list

Comments

Hello,

Thank you for this great blog post.We use suitecrm and we are having issues creating a query.

We have different target lists, for example
Code violations
Vacants
Absentee ...
and we wanted to know how can we show the list of targets and sort them by the target list count?

For example
joe doe - 3 list
mary doe - 2 list
peter doe - 1 list

This way we can see which record is in the most lists? and possible see what list they are in?

thank you

leocomment Sat, 2021-06-26 04:10

Hello Leo

Thank you for your question. What did you try so far? Or where did you stuck? Can you show us the query you have created so far?

An alternative would be to use HeidiSQL, MySQL Workbench or phpMyAdmin to design the right query.

Possibly the GROUP BY clause is what you are looking for. Please read here.

Regards, Oli
Shinguzcomment Mon, 2021-06-28 08:10