Create a Smart Field Spec

In this exercise we will create a smart field that calculates the total number of food bank transactions for the food banks in the system. The smart field contains one parameter:

Transaction Type (Receive vs. Distribute)

Step 1 - Open the Blackbaud.CustomFx.FoodBank.Catalog project within Visual Studio 2008

Here is a link to the Food Bank Source Code.

Step 2 - Add the new Smart Field 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 Smart Field Spec from the list of templates.

Enter ConstituentFoodBankTXCount.SmartField.xml as the XML file name.

Step 3 - Enter the appropriate attributes for the SmartFieldSpec element

The DataType attribute represents that data type returned by the smart field stored procedure.

The RecordType attribute indicates the record type, which governs how the smart field can be used as by other places in the system. For example, the Smart field tab on the Constituent page displays smart fields that have a RecordType = “Constituent.”

The SmartFieldFolder attribute defines the folder that the smart field definition resides within the user interface.

<SmartFieldSpec
    xmlns="bb_appfx_smartfield"
    xmlns:common="bb_appfx_commontypes" 
    ID="4fb03200-530f-4f48-b6c6-2c1805360f41"
    Name="Constituent Food Bank Transaction Count Smart Field"
    Description="Smart field to count the total number of food bank transactions for a constituent."
    Author="Technical Training"
    SPName="USR_USP_SMARTFIELD_CONSTITUENTFOODBANKTXCOUNT"
    DataType="Integer" 
    RecordType="Constituent" 
    DisplayName="Constituent Food Bank Transaction Count" 
    SmartFieldFolder="Constituent\Food Bank">

Step 4 - Add the Dependencies

Below the SmartFieldSpec element place the following DependencyList which ensures the stored procedure used to calculate the smart field already exists in the catalog. The stored procedure referenced in this dependency is wrapped by the main stored procedure for our Smart Field Spec.

<common:DependencyList>
   <common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll" 
      CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT.xml"/>
</common:DependencyList>

Step 5 - Add the stored procedure implementation used to calculate the smart field values

This stored procedure wraps the main stored procedure that calculates the values for the smart field. The stored procedure accepts an ASOF parameter and a FOODBANKTXTYPECODE parameter. The ASOF parameter is used to incrementally process only records that have changed since the last run. This offers a significant performance increase since the calculation is run on a smaller number of records. The FOODBANKTXTYPECODE parameter value will come from the end user via the corresponding form field.

<!-- describe the SP used to calculate the smart field values -->
    <CreateProcedureSQL>
        <![CDATA[
create procedure dbo.USR_USP_SMARTFIELD_CONSTITUENTFOODBANKTXCOUNT
(
    @FOODBANKTXTYPECODE tinyint,
    @ASOF datetime 
)
as
    exec dbo.USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT 
        @FOODBANKTXTYPECODE,
        @ASOF
        ] ]>
    </CreateProcedureSQL>

Step 6 - Add the Stored Procedure Spec to the Catalog project

The stored procedure in the previous step wraps the stored procedure that we must now create using a SQLStoredProcedureSpec. This stored procedure will perform the calculation of the smart field.

The stored procedure is named USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT.

This stored procedure will accept in the ASOF parameter and a FOODBANKTXTYPECODE parameter.

Whenever you process a smart field within the user interface, the stored procedure within the Smart Field Spec should update the values based on any new data entered since the Last run on date. This date displays in Last run on column of the Smart Fields grid on the Smart Fields page. If you want to update the value of a smart field and consider data entered during all dates that satisfy the premiered settings in the smart field and not just the data entered since the last update, you can click the Clear results action.

The stored procedure must check to if the @ASOF parameter is NULL. The first time the smart field is called by the user interface the @ASOF parameter will be NULL signifying that all the appropriate records should be processed.

Right-click the Catalog project within the Visual Studio 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 SQL Stored Procedure Spec from the list of templates.

Enter USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT.xml as the XML file name.

The SQL Stored Procedure Spec should contain the following XML:

<SQLStoredProcedureSpec
    xmlns="bb_appfx_sqlstoredprocedure"
    xmlns:common="bb_appfx_commontypes" 
    ID="5998c072-4cac-4638-b197-699790a71ef0"
    Name="USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT"
    Description="Stored procedure to count a constituent's food bank transactions."
    Author="Technical Training"
    SPName="USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT">

    <CreateProcedureSQL>
        <![CDATA[
create procedure dbo.USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT(
    @FOODBANKTXTYPECODE tinyint,
    @ASOF datetime)
as
    BEGIN    
    declare @CONSTITUENTFILTER table(ID uniqueidentifier primary key)

IF @ASOF IS NULL
BEGIN
-- if @ASOF is null then this is the first time we are running the smart field.
-- Therefore, grab all the food bank id's and count up the number of
-- food bank transactions for the given food bank transaction type (receive/distribute)
    SELECT FB.ID, COUNT(FBTH.ID) 
    FROM dbo.USR_FOODBANKTXHEADER FBTH 
    INNER JOIN USR_FOODBANK FB On FBTH.FOODBANKID = FB.ID
    WHERE FBTH.FOODBANKTXTYPECODE = @FOODBANKTXTYPECODE
    GROUP BY FB.ID
END
ELSE
BEGIN

    -- Grab newly created food bank organizations since the last run date
    insert into @CONSTITUENTFILTER(ID)
        SELECT ID 
        FROM dbo.USR_FOODBANK 
        WHERE DATEADDED > @ASOF
    union
    -- Grab newly created food bank transactions since the last run date
        select FOODBANKID 
        from dbo.USR_FOODBANKTXHEADER 
        where DATEADDED > @ASOF
    union
    -- Grab newly created food bank transaction audit records since the last run date
        select FBTHA.FOODBANKID 
        from dbo.USR_FOODBANKTXHEADERAUDIT FBTHA 
        --join dbo.CONSTITUENT C on FBTHA.CONSTITUENTID = C.ID 
        where FBTHA.AUDITDATE > @ASOF
    
    -- Using the list of constituent id's collected in the previous SQL Statement,
    -- count the number of Food bank Transaction's
    -- for the given food bank transaction type (receive/distribute)
    select
        CF.ID,
        (select 
                count(FBTH.ID) 
            from 
                dbo.USR_FOODBANKTXHEADER FBTH 
            where 
                FBTH.FOODBANKID = CF.ID and 
                FBTH.FOODBANKTXTYPECODE = @FOODBANKTXTYPECODE)
    from
        @CONSTITUENTFILTER CF
END
END
        ] ]>
    </CreateProcedureSQL>
</SQLStoredProcedureSpec>

Load the SQL Stored Procedure Spec.

Step 7 - Add the form metadata to the SmartFieldSpec

The SmartFieldSpec defines the parameters and optionally a user interface presentation for the Smart Field Spec. We will need to provide one form field: FOODBANKTXTYPECODE, which is a required field.

<!-- describe any parameters (other than the ASOF date) defined on the SP -->
    <common:FormMetaData>
        <common:FormFields>
            <common:FormField Caption="Transaction Type" FieldID="FOODBANKTXTYPECODE" DataType="TinyInt" Required="true">
                <common:ValueList>
                    <common:Items>
                        <common:Item>
                            <common:Value>0</common:Value>
                            <common:Label>Receive</common:Label>
                        </common:Item>
                        <common:Item>
                            <common:Value>1</common:Value>
                            <common:Label>Distribute</common:Label>
                        </common:Item>
                    </common:Items>
                </common:ValueList>
            </common:FormField>
        </common:FormFields> 


The completed smart field spec should look like this:
 
<SmartFieldSpec
    xmlns="bb_appfx_smartfield"
   xmlns:common="bb_appfx_commontypes" 
   ID="4fb03200-530f-4f48-b6c6-2c1805360f41"
   Name="Constituent Food Bank Transaction Count Smart Field"
   Description="Smart field to count the total number of food bank transactions for a constituent."
   Author="Technical Training"
   SPName="USR_USP_SMARTFIELD_CONSTITUENTFOODBANKTXCOUNT"
   DataType="Integer" 
   RecordType="Constituent" 
   DisplayName="Constituent Food Bank Transaction Count" 
   SmartFieldFolder="Constituent\Food Bank">

    <common:DependencyList>
        <common:DependencyCatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll" 
                      CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT.xml"/>
    </common:DependencyList>
    
    <!--describe the SP used to calculate the smart field values-->
    <CreateProcedureSQL>
        <![CDATA[
create procedure dbo.USR_USP_SMARTFIELD_CONSTITUENTFOODBANKTXCOUNT
(
    @FOODBANKTXTYPECODE tinyint,
    @ASOF datetime 
)
as
    exec dbo.USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT 
        @FOODBANKTXTYPECODE,
        @ASOF
       ] ]>
    </CreateProcedureSQL>

    <!--describe any parameters (other than the ASOF date) defined on the SP-->
    <common:FormMetaData>
        <common:FormFields>
            <common:FormFieldCaption="Transaction Type"FieldID="FOODBANKTXTYPECODE"DataType="TinyInt"Required="true">
                <common:ValueList>
                    <common:Items>
                        <common:Item>
                            <common:Value>0</common:Value>
                            <common:Label>Receive</common:Label>
                        </common:Item>
                        <common:Item>
                            <common:Value>1</common:Value>
                            <common:Label>Distribute</common:Label>
                        </common:Item>
                    </common:Items>
                </common:ValueList>
            </common:FormField>
        </common:FormFields>
    </common:FormMetaData>
</SmartFieldSpec>

Step 8 - Load the SmartFieldSpec

Step 9 - Configure a new smart field

Select the Administration functional area. Select the Smart Fields task near the bottom of the functional area. The Smart Fields page appears. Any existing smart fields appear on this page.

Step 10 - Add a new smart field

Click the Add action. The Add Smart Field Instance window appears which allows you to select a smart field definition. Definitions are templates that form the basis for your new smart field instances. A smart field definition specifies how to calculate a smart field. For this scenario, select Constituent Food Bank Transaction Count within the Smart fields\Constituent\Food Bank folder.