Question: How do I create, use, and edit advanced queries in Act!?
Product Details:
Product Family: Act!
Product: Act! Pro, Act! Premium, Act! Premium (access via web)
Version: 2010 (v12) and later
Answer:
Advanced queries allow you to create lookups based on more than one criteria by chaining together multiple simple lookups into a list using Boolean (And/Or) statements and parenthetical grouping.
Creating a New Advanced Query
Reusing a Saved Query
Editing a Saved Query
Product Details:
Product Family: Act!
Product: Act! Pro, Act! Premium, Act! Premium (access via web)
Version: 2010 (v12) and later
Answer:
Advanced queries allow you to create lookups based on more than one criteria by chaining together multiple simple lookups into a list using Boolean (And/Or) statements and parenthetical grouping.
Creating a New Advanced Query
- In Act!, click Lookup > Advanced > Advanced Query…
Note: Depending on what view you are in, this may also be under Lookup > Advanced Query…
- Under Search for:, choose Company, Contact, Group, or Opportunity, from the drop-down, depending on what type of record you are creating a query for
- Under Type, choose the desired type from the drop-down
Note: Available types will be consistent with the option chosen in Step 2. If you chose Contacts from the “Search for:” field, the Type field will allow you to query contact data or query opportunity data to find contacts associated with the specified query.
- From the Field Name drop-down, select the field you wish to query
- From the Operator drop-down, select the operator you wish to use
Note: For detailed information about operators in Act!, refer to the following knowledgebase article:
What are operators in Act!?
- Under Value, type the criteria you wish to search for, or if the field you selected for Field Name is linked to a drop-down list, click the drop-down under Value to select from the list
- Click Add to list to apply the settings to the query list
- Repeat steps 3-7 for additional conditions as needed
- If necessary, once the field conditions have been entered, rearrange the order of the conditions by clicking a condition in the list to highlight it, then by clicking the Move Up or Move Down buttons
- If necessary, modify And/Or statements by clicking a condition in the list to highlight it, then by clicking the And/Or field for that row, and selecting Or. If you are unfamiliar with using And/Or statements, expand the section below for detailed information:
How to use And/Or statements
And/Or statements allow you to choose how a selected condition relates to the condition that comes immediately after it. By default, all conditions are configured to use And, unless it is the last condition in the list, in which case it is set to End, which marks the end of the query. Using And means that all conditions using this parameter must be met. Using Or means that only one of the conditions using this parameter must be met.
For example:
- City criteria AND State criteria AND Zip criteria – the query will only return records who have the specified values for all three of the fields
- City criteria OR State criteria OR Zip criteria – the query will only return results for records who meet at least one specified value for any one of the three fields
- City criteria AND State criteria AND Zip criteria – the query will only return records who have the specified values for all three of the fields
- If you need to form parenthetical groups, click to highlight the appropriate beginning condition, click the “ ( “ column on the same row to select the appropriate parentheses option, then click the appropriate ending condition and add the closing parentheses from the “ ) ” column on the same row. If you are not familiar with parenthetical grouping, click to expand the section below for detailed information:
How to use the ( and ) columns for Parenthetical grouping
The parentheses columns allow you to group multiple conditions together to create a more complex query, where the conditions that are grouped by parentheses are added together to create a much larger condition. For example, if you wanted to look up a list of contacts who exist in either Phoenix Arizona, or Scottsdale Arizona AND who are also either Leads or Prospects, you could use parentheses to do this using the conditions below:
( Type Field Name Operator Value ) And/Or ( Contact City Equal To (=) Phoenix Or Contact City Equal To (=) Scottsdale And Contact State Equal To (=) AZ ) And ( Contact ID/Status Equal To (=) Prospect Or Contact ID/Status Equal To (=) Lead ) End
Without the parentheses, this criteria would return a list of contacts that would include all contacts who had Phoenix as their City (even if their State or ID/Status field had no data in it), as well as all Prospects and Leads in Scottsdale Arizona.
Parentheses can be nested up to 5 levels.
- To preview to see if your conditions have been properly formatted, click Preview to populate a list in the bottom pane of the query Criteria window
- Once finished, save your new query by clicking File > Save
- Click OK to run perform the query
- When prompted, choose whether you wish to Replace lookup, Add to lookup, or Narrow lookup, then click OK
Note: If the last lookup you did was of All contacts, you will only have the option to Replace lookup
Reusing a Saved Query
- Navigate to the Record type that matches the Query you wish to run
- In Act!, click Lookup > Advanced > Advanced Query…
Note: Depending on what view you are in, this may also be under Lookup > Advanced Query…
- In the query Criteria window, click File > Open
- Select the desired query, then click Open
- Click OK to run perform the query
- When prompted, choose whether you wish to Replace lookup, Add to lookup, or Narrow lookup, then click OK
Note: If the last lookup you did was of All contacts, you will only have the option to Replace lookup
Editing a Saved Query
- Navigate to the Record type that matches the Query you wish to run
- In Act!, click Lookup > Advanced > Advanced Query…
Note: Depending on what view you are in, this may also be under Lookup > Advanced Query…
- In the query Criteria window, click File > Open
- Select the desired query, then click Open
- Make the desired changes
Note: If you need to edit the value or operator of an existing condition, click the condition, then click Edit… to update the criteria, or you can remove the condition entirely after selecting it by clicking the Remove button.
- Click File > Save, or if you want to save a copy without overwriting the existing query, click File > Save As… to save it as a different name