Build a Search List (SP) Spec and a Translation Function Spec

Let's create a Search List Spec to allow a search of the records from our USR_FOODBANK table. This table should already be created and 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 Search 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 Search List Spec (SP) as the template. Enter FoodBank.Search.XML as the XML file name.

Spec File Name: FoodBank.Search.XML

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

Name: Food Bank Search
Description: Search List of food banks.
Author: Technical Training
RecordType: Food Bank
TranslationFunctionID: <TBD WHEN TRANSALTION FUNCTION SPEC IS CREATED IN FUTURE STEP>

Step 4 - Create the SELECT Transact-SQL statement for the stored procedure/

Within the CreateProcedureSQL element, create a stored procedure that SELECTs the following fields form the USR_FOODBANK and CONSTITUENT tables:

Form Fields for the Search List
Table	Field	ALIAS
USR_FOODBANK	ID
Constituent	KEYNAME	NAME
USR_FOODBANKTYPECODE	DESCRIPTION	FOODBANKTYPE

The SELECT Transact-SQL statement should join the CONSTITUENT table with the USR_FOODBANK table, limit the result set by the incoming @NAME parameter, and order the result set by the Constituent.Name field.

create procedure dbo.USR_USP_SEARCHLIST_FOODBANK
(
	@NAME nvarchar(100) = null,
	@MAXROWS smallint = 500
)
as
	set @NAME = COALESCE(@NAME,'') + '%' ;

	SELECT top(@MAXROWS)
		FB.ID,
		C.KEYNAME as NAME,
		FBTC.DESCRIPTION as FOODBANKTYPE
	FROM 
		dbo.CONSTITUENT C
	INNER JOIN
		dbo.USR_FOODBANK FB on C.ID = FB.CONSTITUENTID
	LEFT JOIN
		dbo.USR_FOODBANKTYPECODE FBTC on FB.FOODBANKTYPECODEID = FBTC.ID
	WHERE
		 C.KEYNAME like @NAME
	ORDER BY
		C.NAME asc

Figure: Food bank search list stored procedure

Step 5 - Create the FormField metadata output fields.

Now we must create the FormField elements that provide metadata about the user interface parameters on our form.

Data List Output Fields
Form Field	Hidden	Caption	Data Type	MaxLength
NAME	False	Name	String	100

When you are done the form fields should look like this:

<!-- describe the filter fields for the search screen -->
<common:FormMetaData>
<common:FormFields>
            <common:FormField FieldID="NAME" Caption="Name" DataType="String" MaxLength="100" />
	</common:FormFields>

	<!-- indicate the client-side component for the filter user interface -->
	<!--<common:FormUIComponent FormUIComponentType="CustomComponent">
<common:CustomComponentID AssemblyName="REPLACE_WITH_ASSEMBLYNAME" ClassName="REPLACE_WITH_FULLCLASSNAME" />
	</common:FormUIComponent>-->
</common:FormMetaData>

Figure: Filter field

You will also want to comment out the reference to the FormUIComponent element since we will not provide a custom client-side component for the filter user interface.

Step 6 - Create the output fields.

Now we must provide the columns for our data list for the search results. To accomplished this, we add an OutputField child element for each item in the following table:

Data List Output Fields
Form Field	Hidden	Caption	Data Type
ID	True	ID	Guid
NAME	False	Name	String
FOODBANKTYPE	False	Type	String

We must also provide the TranslationField attribute for the Output element to indicate the field name that contains the translated values. The value for this attribute corresponds to the output field from our soon to be created Translation Function Spec.

When you are done, the output fields should look like this:

<Output TranslationField="Name">
<OutputFields>
		<OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
		<OutputField FieldID="NAME" Caption="Name" DataType="String" />
		<OutputField FieldID="FOODBANKTYPE" Caption="Type" DataType="String" />
	</OutputFields>
</Output>

Figure: Food bank output fields

Step 7 - Create the Translation Function Spec: Add the new Translation Function Spec item to 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 Translation Function Spec as the template. Enter FoodBank.Translation.XML as the XML file name.

Spec File Name: FoodBank.Translation.XML

Step 8 - Create the Translation Function Spec: Enter the appropriate attributes for the TranslationFunctionSpec element.

Name: Food Bank Translation Function
Description: Get the food bank's name.
Author: Technical Training
RecordType: Food Bank

Step 9 - Create the Translation Function Spec: Enter the TranslationFunctionID attribute for the SearchListSpec element.

Go back to your Search List Spec and enter the ID of the newly created Translation Function Spec as the value of the TranslationFunctionID attribute.

Step 10 - Create the Translation Function Spec: Create the SELECT statement for the stored procedure.

The translation function needs to return the name for the given unique identifier.

CREATE FUNCTION dbo.USR_UFN_TRANSLATIONFUNCTION_FOODBANK
(@ID uniqueidentifier) returns nvarchar(50) 
AS
BEGIN

	DECLARE @s nvarchar(50);
	SELECT 
		@s = NAME 
	FROM 
		dbo.CONSTITUENT 
	WHERE ID = @ID;
	RETURN @s;
	
END

Figure: Food Bank translation function stored procedure

Step 11 - Review the Translation Function Spec and the Search List Spec.

When you are done, the specs should look like this:

<SearchListSpec
    XMLns="bb_appfx_searchlist"
    XMLns:common="bb_appfx_commontypes"
    ID="3eceea4c-cf42-4938-9cf1-c7c77ebc7dc9"
    Name="Food Bank Search"
    Description="Search list for food banks."
    Author="Technical Training"
    RecordType="Food Bank"
    TranslationFunctionID="66d00b36-8c81-4327-84fa-3265e9888b29">

<!-- describe the SP used to fetch the results from the given filters.  Note that the @MAXROWS parameter is required, and should be 
	used to limit the number of rows returned to a reasonable number. -->
	<SPSearchList SPName="USR_USP_SEARCHLIST_FOODBANK">
		<common:CreateProcedureSQL>
			<![CDATA[
create procedure dbo.USR_USP_SEARCHLIST_FOODBANK
(
	@NAME nvarchar(100) = null,
	@MAXROWS smallint = 500
)
as
	set @NAME = COALESCE(@NAME,'') + '%' ;

	select top(@MAXROWS)
		FB.ID,
		C.KEYNAME as NAME,
		FBTC.DESCRIPTION as FOODBANKTYPE
	from 
		dbo.CONSTITUENT C
	inner join
		dbo.USR_FOODBANK FB on C.ID = FB.CONSTITUENTID
	left join
		dbo.USR_FOODBANKTYPECODE FBTC on FB.FOODBANKTYPECODEID = FBTC.ID
	where
		 C.KEYNAME like @NAME
	order by 
		C.NAME asc
			]]>
		</common:CreateProcedureSQL>
	</SPSearchList>

	<!-- describe the filter fields for the search screen -->
	<common:FormMetaData>
		<common:FormFields>
<common:FormField FieldID="NAME" Caption="Name" DataType="String" MaxLength="100" />
		</common:FormFields>

		<!-- indicate the client-side component for the filter user interface -->
		<!--<common:FormUIComponent FormUIComponentType="CustomComponent">
<common:CustomComponentID AssemblyName="REPLACE_WITH_ASSEMBLYNAME" ClassName="REPLACE_WITH_FULLCLASSNAME" />
		</common:FormUIComponent>-->
	</common:FormMetaData>

	<!-- also describe the output fields for the search screen -->
	<Output TranslationField="Name">
		<OutputFields>
			<OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
			<OutputField FieldID="NAME" Caption="Name" DataType="String" />
			<OutputField FieldID="FOODBANKTYPE" Caption="Type" DataType="String" />
		</OutputFields>
	</Output>
</SearchListSpec>

Figure: Completed Search List Spec

<TranslationFunctionSpec
	XMLns="bb_appfx_translationfunction"
	XMLns:common="bb_appfx_commontypes"
	ID="66d00b36-8c81-4327-84fa-3265e9888b29"
	Name="Food Bank Translation Function"
	Description="Get the food bank's name."
	Author="Technical Training"
	RecordType="Food Bank"
	>

	<SQLTranslationFunction FNName="USR_UFN_TRANSLATIONFUNCTION_FOODBANK">
		<common:CreateFunctionSQL>
			<![CDATA[
create function dbo.USR_UFN_TRANSLATIONFUNCTION_FOODBANK(@ID uniqueidentifier) returns nvarchar(50) 
as
begin
	declare @s nvarchar(50);
	select 
		@s = NAME 
	from 
		dbo.CONSTITUENT 
	where ID = @ID;
	return @s;
end	
			]]>
		</common:CreateFunctionSQL>
	</SQLTranslationFunction>
</TranslationFunctionSpec>

Figure: Completed Translation Function Spec

Step 12 - Add dependencies to the Package Spec.

Open the FoodBank.Package.XML file that we created earlier. Add a Dependency element for the translation function and the search list.

<common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll" CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.FoodBank.Translation.XML"/>

<!-- search lists -->
<common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll" CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.FoodBank.Search.XML"/>

Figure: Our new dependency elements

Here is a link to the Food Bank Source Code.