Dynamic Target Lists for SugarCRM enables users to AUTO populate Target Lists based on one or more pre-defined conditions. This module keeps your Target Lists up to date and relevant.
Once installed navigate to any Target List or create a new one. Once you have a list in view (Detail View), you will see a new Sub Panel at the bottom of the screen called “SQL Queries / Dynamic Target List”
Create a query
|Name||A descriptive title you give to this query|
|Dynamic Action||Append – adds records to the list. Remove and Redo – Query removes records added in previously run queries and re-runs the query. This only removes those records that were added as part of this query condition. It does not remove records that may have been added by another query. Remove – removes records from the list|
|Advanced Options||Enter the crontab values instead of using the Job Interval|
|Job Interval||Visually set the time period(s) when this query will be executed|
|Query Condition||Allows SELECT statements only. Enter the SQL query that should be run including a where clause. If you’re not sure how to write the query, contact a SugarCRM specialist or call us to help you. We have included some examples at the end of this document to help you get started. CHECK QUERY – This checks the query does not contain errors and shows the potential count of records that would meet the criteria|
|Order Number||The order in which one or more queries will be run. i.e. one query maybe to add data while another maybe to remove and you might want the removal query to be run second (e.g. Query 1 - Add all Contacts where a Call is Planned, Query 2 – Remove Contacts where a Case exists. For this example to work, you would need to set the order/sequence of queries)|
|Module||This is the Target List subpanel that will be populated with the results of the query. Make sure it makes sense (e.g. Don’t try and populate the Accounts module from a query that is selecting Leads)|
|Active||Turn on/off this query|
|Date & Time Start||You can set a time when this query will start to be executed|
|Date & Time End||You can set a time when this query will stop being executed|
Save the record
The query will now run at the specified times. If you would like to run the query now click on the “Run Query” button. You can create more queries.
And here is the result of the query after it has run...
Use Cases and Examples SQL Queries
Use Case 1:
I want to keep a list of ‘directors’ up to date. Whenever a new director is added to the CRM or if their title changes, I want the list to be updated.
Title contains “Director”
Tables in query: Contacts
SELECT id FROM contacts WHERE title like '%director%'
Dynamic Action: Remove and Redo (just in case someone in your list is no longer a director you may want them removed from the list)
Use Case 2:
As a sales rep, I want to be able to keep a list up to date with all Decision Makers in My Large Accounts so that I can keep in touch through email and telephone calling plans.
Decision Makers = Maybe this is a custom dropdown field called “Role” at Contact level
Large Accounts = Maybe this is the size of the Account in terms of number of employees (500+ employees)
My Accounts = This would be the Accounts Assigned To me (you can find out your userID from the URL when accessing your Profile. E.g. http://yoursugarcrm/index.php?module=Users&action=EditView&record=4fma4270-7900-2871-9c75-4efc4f341ad0
Tables required in query: Contacts, Contacts_cstm, Accounts, Accounts_Contacts
SELECT contacts.id FROM contacts Inner Join contacts_cstm ON contacts.id = contacts_cstm.id_c Inner Join accounts_contacts ON contacts.id = accounts_contacts.contact_id Inner Join accounts ON accounts_contacts.account_id = accounts.id WHERE contacts_cstm.role_c = 'Decision Maker' AND accounts.assigned_user_id = '<enter the user id here>' AND accounts.employees > 500
Use Case 3:
Keep a list of LEADS which have an email address (just primary email) and are not deleted, converted or dead.
SELECT leads.id FROM leads INNER JOIN email_addr_bean_rel ON leads.id = email_addr_bean_rel.bean_id WHERE leads.deleted = 0 AND email_addr_bean_rel.primary_address = 1 AND leads.`status` not in('Converted','Dead')
Query returns “Database failure. Please refer to sugarcrm.log for details”
- When you run a query you see the above message. This is likely down to the SQL query being written incorrectly. Please check it carefully. There will be information about the error in the sugarcrm.log file which you can access from Admin | Diagnostics
Save button doesn’t work
- The query may be malformed or incorrect.
- Try running a Quick Repair & Rebuild
Other add-ons of interest
Save Your Team Countless Hours. SugarChimp syncs your lists in both directions so that you can work in your preferred application. Segment and Group your MailChimp subscribers based on SugarCRM data so you can market to the right people at the right time. Fully compatible with all versions and e...