Making CRM work for YOU! User-Defined Data Lists and User-Defined Smart Queries

Welcome to Shell Design!  This is where we get to make changes and design decisions that make BBEC more specific to your organization, with unique layouts, functionality, and organization.  We can choose exactly what is most important for your users to see on every page, and change those pages or make new ones!  Even better, we can use this personalized information to stay up-to-date on the daily workings of your organization in the form of RSS Feeds, email alerts when information is updated, and dashboards.

We’re going to take a look at two specific areas in Shell Design that help us do this: User-defined data lists and user-defined smart queries.

User-Defined Data Lists

When working in Blackbaud CRM, every page that has a list of information (revenue records, prospect plans, attributes, etc.) is a data list.  Most data lists that you use are probably pre-defined in the system, and set up to appear with specific columns.  However, if there are pieces of information that you need at your fingertips that aren’t listed in that data list, you can change that!  Custom data lists allow us to specify exactly which records we’d like to see on a page, and what details about those records we need included.

For example, let’s say we want to be able to see some personal information about our constituents gathered together when we first open their records.  We could click from tab to tab to locate it all, or we can create our own data list, and have it show up on their record.  Let’s look at how we can build a list like this.

In Administration > Application > Shell Design, there is a tab for User-defined Data Lists.  That’s where we start!  When we click Add to create a new list, we’ll see a screen that’s probably familiar – “Select a Source View.”  This is similar to what we see when we’re creating an ad-hoc query.  It’s where we specify what type of records we’re collecting and outputting data for – constituent, revenue, designation, volunteer, etc.  Let’s build a constituent list:

Building a data list is very similar to setting up an ad-hoc query.  We can specify which records we want to include in the list in the Filters section, and we can select what fields we’d like to be able to see when we put the data list to use.  This is the easy part!

Next, we need to actually create our data list, by selecting to save and close the query.  Here’s where things start looking different:

Here are some key things we need to set up:

• Output type: Whether a field will be visible or not when we view the data list.  In the example above, Constituent record is marked as hidden, because that’s the GUID that will identify the records we want to view, but we won’t need to actually see that number on our constituent records (unless we want to!).
• Context Record ID field: The field to use to identify an item in the data list.  Since we’re working with constituent records and information, we’ll select constituent record here.
• Context record type: The record type associated with the context record ID.  Again, we’ll choose Constituent here.

Check out the F1 Help Topics for details about other available settings and configuration.

Before we save the data list, check out the “Alert options” tab.  Here’s where we can configure this data list to be used in RSS feeds and make it available for email alerts.  Here are the fields we’ll need to know about and configure:

• Channel: The title that will show up when we view the RSS feed.
• Style: We can choose whether to view the RSS feed in feed or list form.
• Item ID field: The output field to use to identify the RSS feed.
• Item title field: The output field to use as a title for each item on the feed.
• Publication date field: The output field date to associate with the RSS feed as the publication date (e.g., we could choose revenue date, post date, receipt date, mailing date, or the date the record was added, depending on the situation).

Once we’ve got all of that set up, we can save the data list.  Then it’ll show up in our list of User-defined Data Lists, as well as in a regular data list search in Administration > Application > Features.  Once the data list has been saved, we can incorporate it into any page where we need that information!  We can also go to Administration > Email Alerts > Custom Email Alerts and build alerts to notify us when new information is added to the data list.

User-defined data lists can also be included in your dashboards, so that your users can keep track of current activity and updates in your database.  Start off by going to Analysis > Web Dashboard, and select the dashboard you want to edit (or add a new one!).  Go to Page options and choose to edit the page.  When you click “Add dashboard item,” you can browse available data list parts and select any user-defined data list you’ve built.

User-Defined Smart Queries

Smart queries let us perform a series of complex calculations using set parameters and logic in a fixed format.  BBEC includes a variety of commonly used smart queries like LYBUNT and SYBUNT queries, but if you have other queries that need to be run regularly with different filter values, it can be useful to create your own smart query so that users can fill in their own filter values and get consistent results.  Creating a user-defined smart query allows us to specify output, sort order, and filter fields in advance.

Conveniently, building a user-defined smart query is pretty similar to making a user-defined data list!

As before, the filters section allows us to “pre-filter” the records we want to consider as part of the results.  So for example, if we want to build a smart query that allows us to see gifts given in a specific year for board members only, we may want to add a filter for the Board Member constituency, so we don’t have to filter by that constituency every time.

The output section is where we put any fields that we want to see when we run the smart query, as well as any fields we want to have as editable filters when we run the query.  So, following the example above, we might want to see the constituent name and amount of the gift, so we’ll put all of those in the output.  When we actually use the smart query, we are going to want to filter by the date the gifts were given, so we’ll include that as well, and move on to the next screen!

When we save the smart query definition, we’ll see a screen that looks a lot like the one we saw with the user-defined data list.  We choose what fields we want to be able to filter, and how we want to filter them (we can have multiple filters in our smart query).  Then, we just have a couple of fields to define:

• Primary key field: Choose an existing output field that is a unique identifier for the records.  In this example, we could choose something like “Revenue record.”
• Record type: The record type to associated with this smart query definition.  It’ll be the one we see if we sort our smart query definitions by record type.
• Name: It’s required!

Once we save the smart query definition, we can go to Analysis > Query > Add a smart query to see our new definition at work!

Any filters you marked checkboxes for will show up on the Parameters tab.  If you marked multiple filters, you can populate all, some, or none of them to see the results you need, depending on the situation.  Just keep in mind that you may have filtered out some records already based on the filters you created in the smart query definition!

On the Results tab, we’ll see all of the fields that we included in our output (and left marked as Visible when we saved the definition).

For more information about how to build and use user-defined data list and smart queries, check out our Page Designer Guide.

Happy Designing!!

You can keep up with us on Twitter by following @bbsupport; we use the hashtag #bbcrm when we discuss Blackbaud CRM. Take a look at our Twitter Guide for more information.