Exercise: Build a Data List (SP)

You can create functional areas, tasks, and data lists with two methods:

1. Configure the feature through Shell Design.

2. Use the Infinity SDK to edit the appropriate XML spec within Visual Studio.

Let's create a Data List Spec to display the records from our USR_FOODITEM table. This table should already be loaded into your Infinity database using the FoodItem.Table.XML Table Spec that was created in Lab: Build the Food Bank Tables.

Here is a link to the Food Bank Source Code.

Step 1 -  Open your Blackbaud.CustomFx.FoodBank.Catalog project. You can create the project with Lab: Build the Food Bank Tables.

Step 2 -  Add the new Data List Spec (SP) item into the Blackbaud.CustomFx.FoodBank.Catalog project.

Right-click the project within the Solution Explorer and select Add\New Item… from the popup menu. Select Blackbaud AppFx Catalog as the category on the Add New Item dialog window and select Select Data List (SP) as the template. Enter FoodItem.DataList.XML as the XML file name.

Step 3 -  Enter the appropriate attributes for the DataListSpec element.

• Name: Food Item List

• Description: Returns all Food Item records
  

• Author: Technical Training

• SecurityUIFolder: Constituent\Food Bank\Food Item

Step 4 -  Create the stored procedure.

Within the CreateProcedureSQL element, create a stored procedure that SELECTs the following fields form the USR_FOODITEM table:

• ID

• NAME

• DESCRIPTION

• CURRENTCOST

• LOWINVENTORYTHRESHOLD

• WEIGHT

Step 5 -  Create the output fields.

Now we must provide the columns for our data list.  This is accomplished by adding an OutputField child element for each item listed in the table below:

Step 6 -  Add a filter with the Parameters element.

We now add a filter that is almost exactly identical to the filter we created for the Widget Parts data list. The first step is to add the Parameters element above the Output element. Within the Parameters element, add the FormMetaData, FormFields, and FormField tags. 

<Parameters>
<c:FormMetaData>
<c:FormFields>
<c:FormField FieldID="Name" Caption="Name" DataType="String" />
</c:FormFields>
<c:FormUIComponent FormUIComponentType="DefaultUI" />
</c:FormMetaData>
</Parameters>

Example: The Name filter

Step 7 -  Modify the stored procedure to accommodate the filter.

In the stored procedure, we add a @NAME parameter and a WHERE clause on our SELECT statement. The @NAME parameter should default to NULL. When you finish, your stored procedure should look like this:

create procedure dbo.USP_DATALIST_FOOD_ITEM
(
	@NAME nvarchar(100) = null	
)
as
	set nocount on;
	select 
        USR_FOODITEM.[ID],
        USR_FOODITEM.[NAME],
        USR_FOODITEM.[DESCRIPTION],
        USR_FOODITEM.[CURRENTCOST],
        USR_FOODITEM.[LOWINVENTORYTHRESHOLD],
		USR_FOODITEM.[WEIGHT]
	from 
        dbo.USR_FOODITEM
	WHERE 	(@NAME is null or USR_FOODITEM.[NAME] LIKE '%' + @NAME + '%')

Example: The modified stored procedure within the Food Items data list

After you modify the stored procedure, your new DataListSpec should look like this: 

<DataListSpec 
    XMLns:c="bb_appfx_commontypes"
    ID="5d4d94df-d0ee-491a-aaf3-4f3ff5a9c2ba"
    Name="Food Item List"
    Description="Returns all Food Item records."
    Author="Technical Training"
    c:SecurityUIFolder="Constituent\Food Bank\Food Item"
    XMLns="bb_appfx_datalist"
    >
    <SPDataList SPName="USP_DATALIST_FOOD_ITEM">
        <c:CreateProcedureSQL>
<![CDATA[
create procedure dbo.USP_DATALIST_FOOD_ITEM
(
	@NAME nvarchar(100) = null	
)
as
	set nocount on;
	select 
        USR_FOODITEM.[ID],
        USR_FOODITEM.[NAME],
        USR_FOODITEM.[DESCRIPTION],
        USR_FOODITEM.[CURRENTCOST],
        USR_FOODITEM.[LOWINVENTORYTHRESHOLD],
		USR_FOODITEM.[WEIGHT]
	from 
        dbo.USR_FOODITEM
	WHERE 	(@NAME is null or USR_FOODITEM.[NAME] LIKE '%' + @NAME + '%')

] ]>
</c:CreateProcedureSQL>
    </SPDataList>

	<Parameters>
		<c:FormMetaData>
			<c:FormFields>
<c:FormField FieldID="Name" Caption="Name" DataType="String" />
			</c:FormFields>
			<c:FormUIComponent FormUIComponentType="DefaultUI" />
		</c:FormMetaData>
	</Parameters>
	
	<Output>
        <OutputFields>
            	<OutputField FieldID="ID" Caption="ID" IsHidden="true" DataType="Guid" />
            	<OutputField FieldID="NAME" Caption="Name" DataType="String" />
<OutputField FieldID="DESCRIPTION" Caption="Description" DataType="String" />
<OutputField FieldID="CURRENTCOST" Caption="Current Cost" DataType="Money" />
<OutputField FieldID="LOWINVENTORYTHRESHOLD" Caption="Low Inventory Threshold" DataType="SmallInt" />
		<OutputField FieldID="WEIGHT" Caption="Weight" DataType="Decimal" />
        </OutputFields>
    </Output>
</DataListSpec>

Example: Our completed data list spec

Step 8 -  Add the Dependency element to the Package Spec.

Open the FoodBank.Package.XML file that we created earlier. Add a new comment and Dependency child element within the DependencyList element: 

<!-- Data Lists -->
<common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll" CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.FoodItem.DataList.XML"/>

Example: Our new Dependency element

Here is a link to the Food Bank Source Code.