When working in Virtuous Analytics, you may want to include specific Custom Field data to further filter or expand on what you are looking at in your final report. Many Tables on CRM+ Objects already include the related Custom Field data (e.g. Contact Custom Fields live on the dm_contacts Table), but the data needs to be extracted for it to be usable. Or, you may need more flexibility and decide to work with entire Custom Field Tables. This guide will walk through steps to extract Custom Fields from object-based tables for a simple addition to Reports and then dive into other Table options for reporting on Custom Fields and how and why to use them.
Table of Contents
- Extracting Custom Fields from Virtuous CRM+ Object Tables
- Aggregate and Dimension Custom Field Table Options
- How and Why to Use DM Custom Field Tables
- How and Why to Use AG Custom Field Tables
To help illustrate the process, this guide will use a Contact Custom Field example to ultimately create a Contact Report that can be filtered by "Language Spoken."
Extracting Custom Fields from Virtuous CRM+ Object Tables
In Virtuous Analytics, most Custom Field data is included as a column in the dataset the Custom Field is tied to. So, Contact Custom Field data can be found in Contact tables. To find the Contact Custom Field of "Language Spoken," first add a Table from the Data Element, and search for "dm_contacts."
To double check that you have selected the correct dataset, select Preview and then search the term "JSON" in the column search bar. This should show a result containing the Contact Custom Field. Select Add.
Now, you should have all Contact data on your Page. Using the right-hand menu, find the Contact Custom Field Values line. Clicking on this will take you straight to the column on the table.
Because JSON contains all of the table’s Custom Fields and their associated values in this single column, you need to extract the specific Custom Field you want along with its associated values. Click the caret just to the right of the column name. This opens an action menu for this specific column. From this menu, select "Extract Columns".
In the pop-out menu, select the specific Custom Fields you want to extract. For this example, select "Language Spoken". Select "Confirm".
Now, you have a column just to the right of the original "Contact Custom Field Value Json" that is specifically the data connected to the Custom Field "Language Spoken".
With this data now extracted, you can filter the Contact table by "Language Spoken". Using the caret to the right of "Language Spoken" open the actions menu and select "Filter".
In the pop-out window, you can now select exactly which entries you want to filter to include or exclude in your resulting data.
Aggregate and Dimension Custom Field Tables Options
In Virtuous Analytics, Custom Field data also has its own dedicated Tables. If you need a Chart based on Custom Fields or a Report that combines Custom Field data with a different Virtuous CRM+ object entirely (e.g. Languages Spoken with Giving Statistics), you may need these Custom Field Tables instead. Using these Tables also gives you more flexibility in customizing the output by displaying the data in rows vs. columns.
Most Custom Field Tables include two types of tables for every CRM+ Object:
- Tables that start with AG, or Aggregate Tables: These tables roll up the data in the table and display data in columns.
- Tables that start with DM, or Dimension Tables: These tables provide details on an object and display data in rows.
| Virtuous CRM+ Object | Virtuous Analytics Table | Column with Corresponding Custom Fields |
|---|---|---|
| All Custom Fields | DM_CUSTOM_FIELDS | CUSTOM_FIELD_VALUE |
| Contact Custom Fields | DM_CONTACT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_CONTACT_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Contact Individual Custom Fields | DM_CONTACT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_CONTACT_INDIVIDUAL_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Contact Note Custom Field | DM_CONTACT__NOTE_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_CONTACT__NOTE_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Event Custom Fields | DM_EVENT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_EVENT_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Event Invitation Custom Fields | DM_EVENT_INVITATION_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_EVENT_INVITATION_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Gift Ask Custom Fields | DM_GIFT_ASK_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_GIFT_ASK_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Gift Custom Fields | DM_GIFT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_GIFT_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Grant Custom Fields | DM_GRANT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_GRANT_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Media Outlet Custom Fields | DM_MEDIA_OUTLET_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_MEDIA_OUTLET_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Planned Gift Custom Fields | DM_PLANNED_GIFT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_PLANNED_GIFT_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Pledge Custom Fields | DM_PLEDGE_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_PLEDGE_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Premium Custom Fields | DM_PREMIUM_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_PREMIUM_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON | |
| Project Custom Fields | DM_PROJECT_CUSTOM_FIELD_VALUES | CUSTOM_FIELD_VALUE |
| AG_PROJECT_CUSTOM_FIELD_VALUES_JSON | CUSTOM FIELD VALUES JSON |
Both types (DM and AG) of Table contain the Custom Field data, but the two Tables have slightly different methods of displaying the desired Custom Field. The focus of your Report will guide you toward using one or the other, which is explained below.
How and Why to Use DM Custom Field Tables
Custom Field Tables that begin with DM are helpful when you are analyzing one Custom Field at a time or in a row without the clutter of other Contact data. This might mean analyzing trends, creating a Pivot Table, or creating a Chart from a single Custom Field. Or simply adding the Custom Field to another Table through a Lookup, Join, or Union. If using DM Custom Field Tables, we recommend pulling the Custom Field data—which are the different responses, like Spanish for Language Spoken—from the CUSTOM_FIELD_VALUE column and the Custom Field itself from the CUSTOM_FIELD_NAME column. As “Language Spoken” is a Contact Custom Field, you can use the DM_CONTACT_CUSTOM_FIELD_VALUES Table. Once you’ve added the Table to your Page, all Contact Custom Fields will display broken out by rows.
To narrow down the Table to only Contacts with the Language Spoken values, create a Filter on the “Custom Field Name” column to only include the specific Custom Field.
From here, you can easily build a Chart based on Language Spoken, or you can add this information to another Report or Table through a Lookup.
For example, if you want to see the Language Spoken alongside Contact information and Giving Statistics, the Field Name and Field Value columns can be added via Lookup to the AG_CONTACT_GIVING_STATISTICS Table.
Now your Languages Spoken will display alongside all of the Contact giving statistics and your Report can be further tailored to see exactly what you need!
How and Why to Use AG Custom Field Tables
Custom Field Tables that begin with AG are helpful when you need to add multiple Custom Fields to a Report in separate columns (e.g. Language Spoken and Prospect Status). This might mean analyzing multiple data points or even correlations for a single Contact at a time. If using AG Custom Field Tables, we recommend using the CUSTOM_FIELD_VALUES_JSON columns. As Language Spoken is a Contact Custom Field, you can use the AG_CONTACT_CUSTOM_FIELD_VALUES_JSON Table.
Once you’ve added the Table to your Page, all Contact Custom Fields will display in a single column. To narrow down the Table to only Contacts with the Language Spoken values, Extract the field using the method above. In our example, you may choose to Extract additional Custom Fields too, like Prospect Status.
Once Languages Spoken and Prospect Status is extracted, it can be added via Lookup to AG_CONTACT_GIVING_STATISTICS so that you can identify the Language Spoken in a single column alongside the Contact information and Giving Statistics.