The Key Attribute Profiling feature in Sage CRM provides users with a method for setting up dynamic sets of data associated with People, Companies, Opportunities, Cases, and Leads. 

These Key Attribute sets are divided into use defined categories and System categories. See screen shot below. 

The Key Attribute data sets and the associated data is defined and held a set of tables prefixed with "DD".
  • ddata
  • ddcategory
  • ddentryheader
  • ddentrypoints
  • ddfield
Below is a simplified Data model for the Key Attribute tables
 
The structures are used for holding User Defined Categories, System Information, such as details of Mail Merges, scheduled Tasks and membership of records in static Groups (Target Lists).

Understand a User Defined Category


You can track the definition of Key Attributes for the default Demo data within the Company screens (shown above) using the following SQL:

select * from custom_tabs where tabs_entity = 'company' and tabs_caption = 'key attribute data'
select * from ddentryheader where enth_entrypointid = 5
select * from ddentrypoints where entp_entrypointid = 5
select * from ddcategory where ddcat_categoryid = 8

The Fields included in the category can be listed using:

select * from ddfield where ddfld_categoryid = 8

The Data actually recorded against one of the fields "Existing Enterprise System" for the Company "Gatecom" - comp_companyid = 28 can be shown using:

select * from ddata where ddata_fieldid = 16 and ddata_entity = 5 and ddata_entityid = 28

ddentryheader

This table defines the header record or Category Group. It is the parent record of the ddentrypoints data.

ddentrypoints

The ddentrypoints record holds the summary information and link to the category included in the category group. When defining a category group to be used with Outbound calls it hold the introductory question to the category and the link to the entity to which the answers should belong.

ddcategory

A ddcategory can be thought of as the screen definition - the grouping of the fields that the data will be recorded against. It is at this point the definition made whether the category is either a parental category (which does not have data recorded against it) or either a single instance or multiple instance category.

ddfield

This table holds the definition of the field used in the categories. The information held in the ddfld_properties can be thought of as analogous to the data held in the custom_edits table which in turn define the appearance and behaviour of the fields in 'normal' screens.

ddata

The different entry types that can be used in the ddfield definitions are then actually held in fields of only 4 types.

  • ddata_shortstr defined as nchar, length 500
  • ddata_integer defined as int
  • ddata_real defined as numeric
  • ddata_date defined as datetime

Understanding the Key Attribute Data used in a Static Group (Target Lists)

If we use as an example the static Group "US East Contacts" 

We can track the 219 members of the group using the following SQL:

select * from custom_reports where repo_reportid =111
select * from ddcategory where ddcat_categoryid = 10
select * from ddfield where ddfld_categoryid = 10
select * from ddata where ddata_fieldid = 30

Groups and Target Lists in Code

The COM API includes objects that allow ASP pages and table level scripts to interact with Groups (Target Lists) and control membership.

Example Code


The code below will create a new static group for the person entity. This code will actually create records in the Reports tables and the Key Attribute table areas.

TargetBlock = CRM.TargetLists;
TargetBlock.TargetListID = 0;
TargetBlock.Category = "Person";
TargetBlock.Name = "COM List 1";
TargetBlock.ViewName = "vTargetListPerson";
TargetBlock.WhereClause = "Addr_City = N'London'";

TargetField = TargetBlock.Fields.New();
TargetField.DataField = "Comp_Name";
TargetField = TargetBlock.Fields.New();
TargetField.DataField = "Pers_LastName";
TargetField = TargetBlock.Fields.New();
TargetField.DataField = "Pers_FirstName";
// Add more fields as desired

TargetField = TargetBlock.OrderByFields.New();
TargetField.DataField = "Pers_LastName";
TargetQuery = TargetBlock.Retrieve();