by FibreCRM

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.

Free 30 day trial
Try it Now

User Guide

Dynamic Target List installation PDF

User Guide

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”

Views

Create a query

Create

Label Description
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

Save

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...
Result

User Cases

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.
Query conditions:
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.
Query conditions:
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')

Troubleshooting

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
Saving Comment Saving Comment...
Rating
  • "This add on is great. It continuously updated my target list based on the report criteria - as the records were updated or added/removed from the report. The support team is very responsive and super helpful!"

    Read More Reviews

Keep up to date on the latest additions

We'll send you an email every month with handpicked add-ons, reviews, tricks and tips. Don't worry, we hate spam as much as you do.