Filter System (CRM)

You may also wish to learn about our Report Engine, which links most of the fields in your Administrate database, letting you create even more powerful reports.

Administrate’s filtering system is designed to let you filter your CRM data, search your data, and build complex reports in very powerful ways. Use our filtering system to:

Create custom reports not available as standard within Administrate
Export data for use outside Administrate
Share reports internally within your organization
Create marketing lists

Account Filtering Options

When filtering Accounts you can query fields that belong to the following entities:

Account:	Fields stored on the account screen
Contact:	Fields stored on the contact screen
Parent:	Fields stored on the account screen of the parent account to the child account that you are querying
Tasks:	Fields stored within the task on the account, contact, and event screens

Contact Filtering Options

When filtering Contacts you can query fields that belong to the following entities:

Account:	Fields stored on the account screen
Attended:	Fields stored in a delegate's attendance record
Contact:	Fields stored on the contact screen
Delegate:	Fields relating to the event that the contact has registered to
Opportunity:	Fields stored on the opportunity screen
Staffed:	Fields relating to the event where the contact has been an instructor on administrator
Tasks:	Fields stored within the task on the account, contact, and event screens

How to Create a Report

From the Account or Contact page:

Click on Edit in the top right of the page, a dialog will be displayed showing you the available controls.
Choose a field to filter by. Different field types have different options. For example, checkboxes will have a “true” or “false” option, while fields that hold text will allow you to specify more advanced searches.
Filters can be added on multiple fields, or on multiple contexts for a single field. For example, returning people with first names that begin with ‘S’ and end with ‘H’. You can “AND” or “OR” these conditions by Matching All or Any of the conditions in the drop-down in the top left.
Groups of conditions can be made for even more detailed queries. Simply click +Group to add a new set of conditions to query against.
Once you’ve created your filters, you can pick which columns are presented. These represent the kinds of information you wish to be displayed. Add from the list of columns and drag and drop to rearrange their order.
Choose which fields you’d like to order your results by.
Now click Apply to see your filtered data!

It’s been decided we need to find all Contacts in our database that are located in our home city (Edinburgh) and who have the job title of CEO or COO. This will be a marketing campaign targeted at those job titles.

Click Edit on the Contacts page.
Let’s add the first filter by clicking +Condition . Select Account → City → begins with → type in ‘Edin’. We want the Account’s city, not the city from the personal address of the CEO or COO. We used ‘begins with’ to catch misspellings of “Edinburgh”. This is our first condition.
Next, we add in another condition and select Contact → Job Title → contains → type in ‘CEO’.
Repeat the previous step for “COO”.
Check that you now have three conditions. Select the columns you wish to output on the report.
Click Apply to see the results.

Example 2: Find All Contacts from a Single Source That Are Customers

In this example, we need to find all Contacts in our database that have found us through a particular Source and that we’ve previously traded with. This will be a marketing effort targeting previous customers, offering them a discount to entice them back.

Click on Edit on the Contacts page.
Let’s add the first filter by clicking on the + Condition button. We want to find customers, so we select Account → Customer → Yes.
Next, we add another condition. Select Account → Source → Is → Source Type required.
As we wish to contact these people it’s important to exclude those who are marked as ‘No Mail’ in the CRM, so we add another condition, Contact → No Mail → No
We now have three conditions and will choose the columns to output on the report.
Once the columns are chosen, we can click Apply and see our results.

Example 3: Find all Contacts Who Have Registered for an Event between Certain Dates and Reside in Specific Countries (using groups)

In this example, we need to find Contacts in our database that reside in the UK or the USA and have previously been registered for a particular Event between a range of dates. That is because we want to send them reminders that their certificates are due to expire. We will use the Group function to define two sets of data to query: one for the students for a particular Event within a date range, and then another for those that have an address in the UK or USA.

Part 1

Click on Edit on the Contacts page.
Let’s add the first group by clicking on +Group. Then click on +Condition (within the newly added group) to add the first set of conditions. We want to find Contacts that have previously been students on a particular Event, so we would select Delegate → Code → exactly → code of the Event.
Next, we’ll add in another condition in this group for the date range. Select Delegate → Start Date → after → the beginning of the date range.
Repeat the previous step for the end of the date range, but select Delegate → Start Date → before → the end of the date range.
We’ll want to exclude canceled students that have canceled, so we can add a new condition in this group: Delegate → Cancelled → No.

Part 2

We now have four conditions in one group. However, we want to narrow the results of this group to reflect 1 of 2 countries, the UK, and the USA, only. This is where the second group will come into play.

Click +Group in the top right. This should create a new group below the group we created earlier. If you created a new group within the one created earlier, remove it by clicking the relevant -Group .
Change ‘Match all of the following:’ to ‘Match any of the following’
Click +Condition and select Account → Country → is → USA
Click +Condition and select Account → Country → is → United Kingdom

You should have a configuration that resembles the following screenshot:

Filtering past students from UK and USA Accounts within a date range

We now have 2 groups, the first whereby we must meet All conditions, and the second whereby we must meet Any of the conditions.

Choose which columns you’d like to display and in which order, then click Apply to view the results.

How to Save a Report

Reports you create can be saved for later retrieval. Reports are saved for all Users on Administrate. Simply build a filter, then click Save in the top right section of the page. Name the report, and you’re done.

How to Load a Report

To load a previously saved report, click Load on the top right section of the screen. Your available saved reports will appear and will load upon selection.

How to Export a Report

Reports and filtered results can be exported by clicking Export in the top right section of the screen. Reports can be exported to Comma Separated Values (CSV) files, which can then be imported into a variety of other systems and programs, Microsoft Excel, and (for Contacts only) to a previously defined Marketing Activity.

A Rotation option (Excel Only) is provided to swap column and row values if desired.

Choose your desired output, then click Apply.

Saved Filters on Accounts & Contacts

Articles in this section