Exercise: Building a Food Bank Summary View Data Form Spec

We will now create a ViewDataFormTemplateSpec that views a record within our USR_FOODBANK table. This table should have already been created and loaded into your Infinity database using the FoodBank.Table.XML TableSpec that was created in Lab: Build the Food Bank Tables. This View Data Form will be used as the summary section for our food bank page. We will create the page and include the view form in future exercises. A food bank is an extension of an organization/constituent record. An example of a food bank would be the Low Country Food Bank (http://www.lowcountryfoodbank.org/). In order to track food inventory for the Low Country Food Bank, we would need to add a constituent record within the CONSTITUENT table and a new row within the new USR_FOODBANK table. A one-to-one relationship will tie the USR_FOODBANK table to the CONSTITUENT table.

The USR_FOODBANK table contains the following columns:

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

If you already have the project open, you may skip this step.

Step 2 - Add the new View Data Form Spec item into the Blackbaud.CustomFx.FoodBank.Catalog project

Right-click the project within the Solution Explorer.

Select Add\New Item… from the popup menu.

Select Blackbaud AppFx Catalog from the list of categories on the right hand side of the Add New Item dialog window. Select View Data Form Template Spec (SP) from the list of templates.

Enter FoodBankSummary.View.XML as the XML file name that will ultimately contain our summary View Data Form for the Food Bank page. We will create the Food Bank page in future exercises.

Edit Data Form Template (SP) File Name: FoodBankSummary.View.XML

Step 3 - Enter the appropriate attributes for the ViewDataFormTemplateSpec element

Name Attribute: Food Bank Summary View

Description: View form for food bank information

Author: Technical Training

Record Type: Food Bank

SecurityUIFolder: Constituent\Food Bank

Step 4 - Add the scalar user defined functions

Like functions in programming languages, Microsoft SQL Server user-defined functions (UFN) are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. For our purposes with the summary view form, we will be creating user defined functions that return a single or scalar value to perform calculations on the total count of transactions (both received and distributed), the total value of the food transactions (both received and distributed), and the weight of the food transactions (both received and distributed). This will enable our users to get a quick summary of how the food bank is performing. The UFN’s can be used as the basis for future key performance indicators (KPI’s).

Go ahead and add the following SQL Function Specs to the project. Right click the project in the Solution Explorer and select Add\Existing Item… from the popup menu. The specs are listed in the table below.

Here is a link to the Food Bank Source Code.

Grab the following specs from the source code. File Names:

User Defined Functions Used By the Stored Procedure
SQL Function Spec File Name	Description
USR_UFN_FOODBANKTX_COUNT.XML	Calculates the total count of transactions (both received and distributed)
USR_UFN_FOODBANKSUMFOODITEMAMT.XML	Calculates the total value of the food transactions (both received and distributed)
USR_UFN_FOODBANKSUMFOODITEMWEIGHT.XML	Calculates the weight of the food transactions (both received and distributed)

Step 5 - Create the stored procedure

After the spec has been added to the project, you will notice the spec contains a CreateProcedureSQL element. The stored procedure you create is used to load values for a given row from the database and populate the form fields within the user interface. At the heart of the load implementation stored procedure is a Transact-SQL SELECT statement that pulls a row of data for the given @ID parameter.

Remember that the stored procedure is responsible for the following:

Setting the @DataLoaded = 1 within the SELECT statement
Selecting the appropriate row to edit using the @ID parameter within our WHERE clause
Assigning the values for our fields. Therefore we must use OUTPUT parameters and default them to NULL.

You must modify the stored procedure’s parameters and fields within the Transact-SQL SELECT statement to account for the following fields. Note that we are pulling data across multiple tables and employing the help of scalar user defined functions for summary calculations.

View Data Form Stored Procedure Parameters and Form Fields
Stored Procedure Parameter Name	Table/Scalar User Defined Function (UFN)	Column Name	Data Type
@ID	CONSTITUENT	ID	uniqueidentifier
@NAME	CONSTITUENT	NAME	nvarchar(100)
@DESCRIPTION	USR_FOODBANK	DESCRIPTION	nvarchar(max)
@MISSIONSTATEMENT	USR_FOODBANK	MISSIONSTATEMENT	nvarchar(max)
@PRIMARYADDRESS	UFN_BUILDFULLADDRESS (ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID)	PRIMARYADDRESS	nvarchar(300)
@TOTALFOODRECEIVED	dbo.USR_UFN_FOODBANKTX_COUNT(CONSTITUENT.ID, 0)	N/A	integer
@TOTALFOODDISTRIBUTED	dbo.USR_UFN_FOODBANKTX_COUNT(CONSTITUENT.ID, 1)	N/A	integer
@TOTALFOODRECEIVEDAMOUNT	dbo.USR_UFN_FOODBANKSUMFOODITEMAMT(CONSTITUENT.ID, 0)	N/A	money
@TOTALFOODDISTRIBUTEDAMOUNT	dbo.USR_UFN_FOODBANKSUMFOODITEMAMT(CONSTITUENT.ID, 1)	N/A	money
@TOTALFOODRECEIVEDWEIGHT	dbo.USR_UFN_FOODBANKSUMFOODITEMWEIGHT(CONSTITUENT.ID, 0)	N/A	numeric(20,4)
@TOTALFOODDISTRIBUTEDWEIGHT	dbo.[USR_UFN_FOODBANKSUMFOODITEMWEIGHT]( CONSTITUENT.ID, 1)	N/A	numeric(20,4)
@FOODBANKTYPE	USR_FOODBANKTYPECODE	DESCRIPTION	nvarchar(100)

When you are done your stored procedure should look like this within the spec:

create procedure dbo.USR_USP_DATAFORMTEMPLATE_VIEW_FOODBANKSUMMARY
 
(
 
@ID uniqueidentifier,
 
@DATALOADED bit = 0 output,
 
@NAME nvarchar(100) = null output,
 
@DESCRIPTION nvarchar(max) = null output,
 
@MISSIONSTATEMENT nvarchar(max) = null output,
 
@FOODBANKTYPE nvarchar(100) = null output,
 
@PRIMARYADDRESS nvarchar(300) = null output,
 
@TOTALFOODRECEIVED integer = null output,
 
@TOTALFOODDISTRIBUTED integer = null output,
 
@TOTALFOODRECEIVEDAMOUNT money = null output,
 
@TOTALFOODDISTRIBUTEDAMOUNT money = null output,
 
@TOTALFOODRECEIVEDWEIGHT numeric(20,4) = null output,
 
@TOTALFOODDISTRIBUTEDWEIGHT numeric(20,4) = null output
 
)
 
as
 
set nocount on;
 
-- be sure to set this, in case the select returns no rows
 
set @DATALOADED = 0;
 
-- populate the output parameters, which correspond to fields on the form. Note that
 
-- we set @DATALOADED = 1 to indicate that the load was successful. Otherwise, the system
 
-- will display a "no data loaded" message.
 
select
 
@DATALOADED = 1,
 
@NAME = C.NAME,
 
@DESCRIPTION = FB.DESCRIPTION,
 
@MISSIONSTATEMENT = FB.MISSIONSTATEMENT,
 
@FOODBANKTYPE = FBTC.DESCRIPTION,
 
@PRIMARYADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
 
@TOTALFOODRECEIVED = dbo.USR_UFN_FOODBANKTX_COUNT(C.ID, 0),
 
@TOTALFOODDISTRIBUTED = dbo.USR_UFN_FOODBANKTX_COUNT(C.ID, 1),
 
@TOTALFOODRECEIVEDAMOUNT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMAMT](C.ID, 0),
 
@TOTALFOODDISTRIBUTEDAMOUNT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMAMT](C.ID, 1),
 
@TOTALFOODRECEIVEDWEIGHT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMWEIGHT](C.ID, 0),
 
@TOTALFOODDISTRIBUTEDWEIGHT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMWEIGHT](C.ID, 1)
 
from
 
dbo.CONSTITUENT C
 
join
 
dbo.USR_FOODBANK FB on C.ID = FB.ID
 
left join
 
dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
 
left join
 
dbo.USR_FOODBANKTYPECODE FBTC on FB.FOODBANKTYPECODEID = FBTC.ID
 
where
 
C.ID = @ID
 
return 0;

Step 6 - Create the Form Fields

Next we have to describe the fields which will appear on our View Data Form. These names of our fields will correspond to the parameters within our stored procedure. The parameters such as @ID do not need to be included on our data form. Using the same table above that we used for the stored procedure parameters, create the form field metadata for our View Data Form.

<!-- describe fields on the form, which correspond to parameters on the SP. Note that system parameters like the context @ID, @DATALOADED, and @CURRENTAPPUSERID need not be listed. -->
<common:FormMetaData FixedDialog="true">
<common:FormFields>
<common:FormField FieldID="NAME" Caption="Name" DataType="String" />
<common:FormField FieldID="DESCRIPTION" Caption="Description" DataType="String" />
<common:FormField FieldID="MISSIONSTATEMENT" Caption="Mission Statement" DataType="String" />
<common:FormField FieldID="FOODBANKTYPE" Caption="Food Bank Type" DataType="String" />
<common:FormField FieldID="PRIMARYADDRESS" Caption="Primary Address" DataType="String" />
<common:FormField FieldID="TOTALFOODRECEIVED" Caption="Total Food Received" DataType="String" />
<common:FormField FieldID="TOTALFOODDISTRIBUTED" Caption="Total Food Distributed" DataType="String" />
<common:FormField FieldID="TOTALFOODRECEIVEDAMOUNT" Caption="Total Received Amount" DataType="String" />
<common:FormField FieldID="TOTALFOODDISTRIBUTEDAMOUNT" Caption="Total Distributed Amount" DataType="String" />
<common:FormField FieldID="TOTALFOODRECEIVEDWEIGHT" Caption="Total Received Lbs" DataType="String" />
<common:FormField FieldID="TOTALFOODDISTRIBUTEDWEIGHT" Caption="Total Distributed Lbs" DataType="String" />
</common:FormFields>
</common:FormMetaData>

Step 7 - Review the Spec and Load the Spec using LoadSpec

At this point your spec should look something like this. The GUID’s for the ID and DataFormInstanceID attributes will be different since new GUIDs are generated by the SDK when you added the new spec to the project. Once you have the spec completed, try and load it using LoadSpec.

<ViewDataFormTemplateSpec
 
XMLns="bb_appfx_viewdataformtemplate"
 
XMLns:common="bb_appfx_commontypes"
 
ID="965337a3-9ce2-459f-90ca-61abb9949cd5"
 
Name="Food Bank Summary View"
 
Description="View form for food bank information."
 
Author="Technical Training"
 
DataFormInstanceID="703f99d3-fb38-409f-9843-3a6a1ab13b4c"
 
RecordType="Food Bank"
 
common:SecurityUIFolder="Constituent\Food Bank">
 
<!-- define the SP that returns the data used by the form -->
 
<SPDataForm SPName="USR_USP_DATAFORMTEMPLATE_VIEW_FOODBANKSUMMARY">
 
<common:CreateProcedureSQL>
 
<![CDATA[
 
create procedure dbo.USR_USP_DATAFORMTEMPLATE_VIEW_FOODBANKSUMMARY
 
(
 
@ID uniqueidentifier,
 
@DATALOADED bit = 0 output,
 
@NAME nvarchar(100) = null output,
 
@DESCRIPTION nvarchar(max) = null output,
 
@MISSIONSTATEMENT nvarchar(max) = null output,
 
@FOODBANKTYPE nvarchar(100) = null output,
 
@PRIMARYADDRESS nvarchar(300) = null output,
 
@TOTALFOODRECEIVED integer = null output,
 
@TOTALFOODDISTRIBUTED integer = null output,
 
@TOTALFOODRECEIVEDAMOUNT money = null output,
 
@TOTALFOODDISTRIBUTEDAMOUNT money = null output,
 
@TOTALFOODRECEIVEDWEIGHT numeric(20,4) = null output,
 
@TOTALFOODDISTRIBUTEDWEIGHT numeric(20,4) = null output
 
)
 
as
 
set nocount on;
 
-- be sure to set this, in case the select returns no rows
 
set @DATALOADED = 0;
 
-- populate the output parameters, which correspond to fields on the form. Note that
 
-- we set @DATALOADED = 1 to indicate that the load was successful. Otherwise, the system
 
-- will display a "no data loaded" message.
 
select
 
@DATALOADED = 1,
 
@NAME = C.NAME,
 
@DESCRIPTION = FB.DESCRIPTION,
 
@MISSIONSTATEMENT = FB.MISSIONSTATEMENT,
 
@FOODBANKTYPE = FBTC.DESCRIPTION,
 
@PRIMARYADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
 
@TOTALFOODRECEIVED = dbo.USR_UFN_FOODBANKTX_COUNT(C.ID, 0),
 
@TOTALFOODDISTRIBUTED = dbo.USR_UFN_FOODBANKTX_COUNT(C.ID, 1),
 
@TOTALFOODRECEIVEDAMOUNT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMAMT](C.ID, 0),
 
@TOTALFOODDISTRIBUTEDAMOUNT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMAMT](C.ID, 1),
 
@TOTALFOODRECEIVEDWEIGHT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMWEIGHT](C.ID, 0),
 
@TOTALFOODDISTRIBUTEDWEIGHT = [dbo].[USR_UFN_FOODBANKSUMFOODITEMWEIGHT](C.ID, 1)
 
from
 
dbo.CONSTITUENT C
 
join
 
dbo.USR_FOODBANK FB on C.ID = FB.ID
 
left join
 
dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
 
left join
 
dbo.USR_FOODBANKTYPECODE FBTC on FB.FOODBANKTYPECODEID = FBTC.ID
 
where
 
C.ID = @ID
 
return 0;
 
] ]>
 
</common:CreateProcedureSQL>
 
</SPDataForm>
 
<!-- describe fields on the form, which correspond to parameters on the SP. Note that system parameters
 
like the context @ID, @DATALOADED, and @CURRENTAPPUSERID need not be listed. -->
 
<common:FormMetaData FixedDialog="true">
 
<common:FormFields>
 
<common:FormField FieldID="NAME" Caption="Name" DataType="String" />
 
<common:FormField FieldID="DESCRIPTION" Caption="Description" DataType="String" />
 
<common:FormField FieldID="MISSIONSTATEMENT" Caption="Mission Statement" DataType="String" />
 
<common:FormField FieldID="FOODBANKTYPE" Caption="Food Bank Type" DataType="String" />
 
<common:FormField FieldID="PRIMARYADDRESS" Caption="Primary Address" DataType="String" />
 
<common:FormField FieldID="TOTALFOODRECEIVED" Caption="Total Food Received" DataType="String" />
 
<common:FormField FieldID="TOTALFOODDISTRIBUTED" Caption="Total Food Distributed" DataType="String" />
 
<common:FormField FieldID="TOTALFOODRECEIVEDAMOUNT" Caption="Total Received Amount" DataType="String" />
 
<common:FormField FieldID="TOTALFOODDISTRIBUTEDAMOUNT" Caption="Total Distributed Amount" DataType="String" />
 
<common:FormField FieldID="TOTALFOODRECEIVEDWEIGHT" Caption="Total Received Lbs" DataType="String" />
 
<common:FormField FieldID="TOTALFOODDISTRIBUTEDWEIGHT" Caption="Total Distributed Lbs" DataType="String" />
 
</common:FormFields>
 
</common:FormMetaData>
 
</ViewDataFormTemplateSpec>