Click on the down arrow just to the right of the Selection Reports Icon in the Main Section of the Sidebar Menu.

Query Overview

Advanced Query gives users the power to retrieve virtually any data they might want from Advisors Assistant.  You can query Contacts, Investments, and Insurance all at the same time, combining queries from each of the major areas together.

Query isn't as simple as Selection Reports, but it is much more powerful.

Query requires a step-by-step approach.  Once you have done a few, it will seem very easy.

Search Among Contacts Step By Step

Let's look for all contacts with a Primary Name Type of Client or Group Client with a Birth Date in the month of May or June.

Step 1: Tell Advisors Assistant To Select Names That Match The Query

In the Names Section, just change the default to select matching names by clicking on the field and selecting the item as shown.

Step 2: Choose a Field

Drop down the field list and make a selection from the drop down list. On Screen 2, we've selected to look at the Primary Name Type field.

Step 3: Choose An Operator

An operator is how you want to compare what is in the field (Primary Name Type) and the value you are searching for.  We want to search for Name Types EQUAL TO "Client" so we want to chose "Is Equal To" from the list of Operators.

Note: The list of operators will change automatically based on the type of data you are looking for.  If you're looking at a date field, you will get a different list of operators than if you are looking at a text field.  Numeric fields will be different also.

Step 4: Choose One or More Values

On Screen 3, we've chosen to look for either of two values.  Advisors Assistant will consider it a match if EITHER "Client" or "Group Client" is located in the Primary Name Type.

You don't have to type in "Client" or "Group Client" because Advisors Assistant knows that the field you chose, Primary Name Type is a field that uses lists, so it shows you your list.

Step 5: Click Add

If you wanted to stop and search, you could click on Search and you would find "All contacts with a Primary Name Type that Is Equal To Client OR Group Client," however, we want to look for a birth date of May or June.

By clicking Add, we get a new line to work with.

Step 6: Choose the Date Operator

Dates give you a greater selection of operators to use to match.

Since we are looking for all those born in either May or June, we chose "Month is Equal To" in Screen 5.

Step 7: Choose Values To Match

Complete the line by clicking on the Values Column.  Since Advisors Assistant knows you are looking for months that match, only months are on the list.

Step 7: Click on Search Button

Before you click on Search, you can save the criteria and load it for a future search.

 

Once the search is complete, you get the same Reports Selection Menu that you get with the Selection Report Criteria.

Search Among Investments Step By Step

You can combine contact searches with investment searches.

Let's look for all contacts with a Primary Name Type of Client who have a mutual fund in an account with a value of at least  $100,000.

Investment searches work exactly the same way. Just pick the field, then the operator, and then the value.  You don't have to know any Boolean operators.

Search Among Insurance Step By Step

You can also combine insurance searches.

Add to the Investment Search and look for those clients that don't have any Term Insurance.

The above insurance search will search all insurance clients who do not have Term Life, but those clients with no insurance at all, who only have investments, will not turn up in the search.

Search Not Equal To In Types of Products

When searching Investments or Insurance for those who do NOT have something, it's important to remember that the search is searching Investment or Insurance records.  If the contact has NO INSURANCE AT ALL, then there is no insurance records to search.

Some How To's

How would I search for clients turning 70.5 in 2016?

First compute the beginning of the date range when someone is born to turn 70 1/2 on January 1, 2016.

2016.0  -  70.5  =  1945.5

This means that if you are born on July 1, 1945 (half way through the year) you will be 70.5 on January 1, 2016.

Next, add 12 months to get the end of the date range

July 1, 1945  +  12 months  =  June 30, 1946

How would I search for A Level Clients with birthdays in June?

How to skip right to a field without scrolling down the list

If you know what the field you are searching for is called, you can click to drop down the list and then press the first letter of the name of the field.  If you dropped down the list of Months for a date, and then pressed "J", you would skip down to June.

Saving Search Criteria

To save a criteria for reuse, just click on the Save button before clicking on the search button.

Give the query a descriptive name and then click on the Save Button.

Loading or Deleting Saved Criteria

To Load a previously saved query or to load the last query you searched on, highlight the saved criteria and click on the Load Button.

Loading Previously Searched Criteria

When you click on the Search Button, Advisors Assistant automatically saves your query criteria as "_Previous" so that you can reload it easily and make small changes.

Copy Criteria To Other User

When you copy a criteria to one or more users or to all of the users of a department, the criteria becomes part of their criteria list.  If any one of them change the copied criteria, it only changes their copy.

Copy To Department

You can choose a department at the bottom of the copy screen.  When you copy to a department, it's just as if you checked off each member of the department in the user list.  Each department member will get a copy of the criteria in their saved criteria list.

Share A Saved Criteria

Sharing a criteria is different than copying to a list of users.  The person sharing a criteria is the Owner of the criteria.

That owner can change the criteria and when a change is made, it is automatically shared with all of the other users on that share list.  Shared users cannot make changes.  Only the owner can make changes.

Delete Query Criteria

Use the Delete Button to delete a saved criteria.

 

See Also

Selection Report Criteria

Menu of Reports