Pass the Labels of Simple Data List Entries or Code Table Entries as Parameters

This material pertains to version 4.0 of Blackbaud CRM SDK.

The UseLabelAsValue attribute allows you to include code table fields and simple data list fields as form fields to filter data lists that pull data from the Blackbaud Data Warehouse. Previously, code table fields and simple data list fields could not filter such data lists because data lists identify code table entries or simple data list entries based on their IDs and the data warehouse does not store those IDs.

Starting with version 4.0, the UseLabelAsValue attribute allows data lists to identify code table entries and simple data list entries based on their labels instead of their IDs so that you can use code table fields and simple data list fields to filter data lists that pull data from the warehouse. When you set the UseLabelAsValue attribute to "true" for CodeTable and SimpleDataList elements within a data list spec's Parameters element, these form fields pass the labels for their entries as parameters instead of passing IDs to the data list.

By default, the UseLabelAsValue attribute is set to "false" because passing IDs as parameters is the preferred method to identify code table entries and simple data list entries for most data lists. But since a different parameter is necessary to identify code table entries and simple data list entries for data lists that pull data from the warehouse, the UseLabelAsValue attribute allows you to pass labels as parameters instead.

For example, you can create a data list that pulls constituent data from the data warehouse and include a form field to filter the list based on entries in the code table for titles.

To pull constituent data from the data warehouse instead of the transactional database, you include the MartKey attribute in the data list spec's root DataListSpec element and set it to "BBDW."

<DataListSpec
    xmlns="bb_appfx_datalist"
    xmlns:c="bb_appfx_commontypes"
    ID="a3841f3b-4d52-4261-a399-9ba683e7225f"
    Name="Data Warehouse Test Data List"
    Description="A test data list to display data from the data warehouse."
    Author="Technical Training"
    c:SecurityUIFolder="Data Warehouse"
    MartKey="BBDW"
  >

Then to designate the Data Warehouse as the database schema for the data list's stored procedure, you can add the SPSchema attribute to the SPDataList element and set its value to "BBDW."

Tip: We recommend that you use BBDW as the database schema for stored procedures that pull data from the data warehouse because most database objects in the data warehouse live within this schema.

  <SPDataList SPName="USR_USP_DATALIST_DATAWAREHOUSE_TEST" SPSchema="BBDW">

Next, you create a stored procedure to pull data for the data list in the CreateProceduerSQL element. For this example, the stored procedure selects the full name, title, and marital status of constituents, as well as whether constituents give anonymously.

If you set the SPSchema attribute to "BBDW" in the SPDataList element, make sure to update the stored procedure name in the CREATE line from the default "dbo" to "BBDW."

Also in the CREATE line, you declare an input parameter to the stored procedure. Since the example filters the data list based on title, the CREATE line specifies a @TITLE parameter. Then at the end of the stored procedure, the WHERE clause filters the output based on the value of this variable.

    <common:CreateProcedureSQL>
      <!{CDATA{
create procedure BBDW.USR_USP_DATALIST_DATAWAREHOUSE_TEST (@TITLE nvarchar(255) = null)
as
  set nocount on;


  select CONSTITUENTDIMID,
      FULLNAME,
      TITLE,
      GIVESANONYMOUSLY,
      MARITALSTATUS
  from BBDW.DIM_CONSTITUENT
  where @TITLE is null or TITLE = @TITLE

      ]]>
    </c:CreateProcedureSQL>

Next, in the Parameters element, you include the Title form field so that end users can filter the data list based on titles. In the CodeTable element for the form field, you include the UseLabelAsValue attribute and set its value to "true" so that the form field passes the labels for code table entries as parameters to the data list instead of passing IDs.

  <Parameters>
    <FormMetaData xmlns="bb_appfx_commontypes">
      <FormFields>
        <FormField FieldID="TITLE" Caption="Title" DataType="String">
          <CodeTable CodeTableName="TITLECODE" UseLabelAsValue="true"/>
        </FormField>
      </FormFields>
    </FormMetaData>
  </Parameters>

And finally, in the Output element, you include the output fields to display in the data list.

  <Output>
    <OutputFields>
      <OutputField FieldID="CONSTITUENTDIMID" Caption="ID" DataType="Integer" IsHidden="true" />
      <OutputField FieldID="FULLNAME" Caption="Full name" DataType="String" />
      <OutputField FieldID="TITLE" Caption="Title" DataType="String" />
      <OutputField FieldID="GIVESANONYMOUSLY" Caption="Gives anonymously" DataType="Boolean" />
      <OutputField FieldID ="MARITALSTATUS" Caption="Marital status" DataType="String" />
    </OutputFields>
  </Output>

After you load this data list into Blackbaud CRM, you can display it in the application. When the list first loads, no filter is applied and the full data list is displayed. When you click Filters above the list, the Title form field that you specified on the data list spec appears.

When you select a title from the dropdown list and click Apply, a filtered version of the list displays only the constituents with the title you select.

Tip: For more information about the Blackbaud Data Warehouse, see the Blackbaud Data Warehouse guide.