User Defined Demographics Technical Information

User Defined Demographics lets you implement custom demographics for organizations, individuals and registrants in baseline NetForum.

This page outlines various technical and design considerations with this feature.

Design Considerations

User defined demographics were added to enable clients to easily implement custom fields easily and at no cost.

Depending on the intended use of the field, however, if may be preferable to create custom extender columns or custom tables to manage demographics data, instead of user defined demographics.

When NOT to implement a User Defined Demographic

If the field meets any of these critera, then it is not a good candidate for being implemented with user defined demographics:

eWeb - if the field needs to be entered on eWeb, and you need to force the user to enter a value (for example, in a membership join wizard, or a site registration wizard, etc.), then user defined demographics is not a good idea because the only way, currently, to capture this information is through the standard entry form; you cannot simply drag-and-drop those fields onto a designed form.

Registrant Demographics in eWeb - at this time, a registrant in eWeb cannot enter these values.

xWeb - if this field might see heavy xWeb activity, then you might want to stay away from user defined demographics. See the xWeb section below for more information.

Business Logic - these fields are intended to be simple data entry. If you have any more complicated business logic, it is not easily done.

Multi-select - the data format for storing multi-select fields is to store the values, comma-separated, in a single column. For example, Apples,Bananas,Oranges. Although this data format is fine for simple demographics, more advanced querying, reporting, programming and data analysis is better served by having true one-to-many tables.

When You Should implement a User Defined Demographic

The field has NO business rules and it NOT referenced anywhere in code or business logic (although it could be acceptable to be output in a report).
When it is acceptable that the field is managed only on the specialized demographics popup form in iWeb, and NOT on eWeb wizards or ANY other forms in iWeb or eWeb.
When the field is NOT used for dues purposes.
The field is NOT needed in xWeb.

Practical Considerations

If extender columns are created (_ext) and user defined demographics are also used, then these two types of information are in different tables. Queries, data conversion and report writing need to be tailored accordingly.
Extender columns and user defined fields can not appear in the same form. User defined demographics only displayed in a specific form (see User Interface below) and this form can not be designed using the Form Designer. Only user defined fields will be displayed in the user defined form based on the order of the fields defined in the setup area. No other information can be included on this specialized data entry page.
The client is responsible in configuring user defined demographics. Avectra can help the client in setting up user defined demographics with additional service fee.

User Interface

These fields are managed by end users on specially programmed ASPX pages, not on designed Toolkit pages. These pages are launched from hyperlinks on profile pages; see User Defined Demographics Setup for more information. All of the code that formats and populates this data is in, or is called from, the "code behind" of those pages.

Object Model

These tables do have dynamic NetForum Objects built around them and can be used in coding.

Database

Demographic Tables

The demographics values entered by the user are stored in these tables:

co_individual_dem - has a FK idm_cst_key
co_organization_dem - has a FK odm_cst_key
ev_registration_dem - has a FK rdm_reg_key

Records are not inserted into these tables until needed, i.e., until a record is edited and a value is entered. At that time, the framework will insert a records in the demographic table.

This is the opposite behavior of extender tables, which always have an equal number of rows as the main table.

Setup Table

The labels and other properties of the demographics are stored in the md_column_custom table.

xWeb

Important! It is safer to use WEBIndividualUpdate and WEBIndividualInsert to manage demographics. Avoid using InsertFacadeObject and UpdateFacadeObject.

To update/insert values in these fields in xWeb, you must use the IndividualCustomDemographics, OrganizationCustomDemographics, and RegistrationDemographics objects instead of standard organization and individual objects.

The calling program will need to determine whether to use WEBIndividualInsert (if demographics have not inserted yet) or WEBIndividualUpdate (if they have).

To determine the questions and potential answers through xWeb, you will need to reference the setup data in the md_column_custom table. At this time, however, this table has not been enabled for xWeb GetQuery. If you want to query this table, you should develop an ExecuteMethod web method with SQL like this:

SELECT mcc_description, mcc_mdc_name, mcc_values, mcc_multi_select
FROM md_column_custom (nolock)
WHERE 
 
   -- choose co_individual_dem, co_organization_dem, or ev_registration_dem
  mcc_mdt_name = 'co_individual_dem'
 
  -- this will omit demographics that have not been enabled
  AND charindex(space(1) + RIGHT(mcc_mdc_name, 2), mcc_description) = 0
 
  -- If this is for "ev_registration_dem", must filter on the ev_event.evt_key;  
  -- if this is for ind or org, then omit this condition 
  -- assumption is that {evt_key} is a run-time parameter
  AND mcc_item_key = {evt_key}
 
  -- filter based on whether column should appear externally; may omit this condition if desired
  AND mcc_eweb = 1 
 
  AND mcc_delete_flag = 0
 
ORDER BY 
	mcc_order
FOR xml path('question'), root('demographics')

The result from the ExecuteMethod web service as structured above will look like this:

<demographics>
  <question>
    <mcc_description>Smoke?</mcc_description>
    <mcc_mdc_name>idm_custom_flag_01</mcc_mdc_name>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
  <question>
    <mcc_description>Vegetarian?</mcc_description>
    <mcc_mdc_name>idm_custom_flag_04</mcc_mdc_name>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
  <question>
    <mcc_description>Children?</mcc_description>
    <mcc_mdc_name>idm_custom_flag_05</mcc_mdc_name>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
  <question>
    <mcc_description>New Date</mcc_description>
    <mcc_mdc_name>idm_custom_date_01</mcc_mdc_name>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
  <question>
    <mcc_description>Yearly Income</mcc_description>
    <mcc_mdc_name>idm_custom_currency_01</mcc_mdc_name>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
  <question>
    <mcc_description>Number of Children</mcc_description>
    <mcc_mdc_name>idm_custom_text_01</mcc_mdc_name>
    <mcc_values>1;2;3</mcc_values>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
  <question>
    <mcc_description>Eye Color</mcc_description>
    <mcc_mdc_name>idm_custom_text_02</mcc_mdc_name>
    <mcc_values>Blue;Green;Brown</mcc_values>
    <mcc_multi_select>0</mcc_multi_select>
  </question>
</demographics>

To develop this web method setup in iWeb, see XWeb: ExecuteMethod Case Study as an example.

For individuals and organization, you could hard-code the questions as these will generally be fixed/static after the initial setup.

For RegistrationDemographics, however, the questions are different for each and every Event. Therefore, you will need to filter the questions based on the particular event, since these questions can vary for each event. To do this, you must ensure that you filter on the mcc_item_key, which will hold the ev_event.evt_key as a foreign key.