Version:

Create a Smart Field Spec

In this exercise, we will create a 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 a new smart field spec to the Blackbaud.CustomFx.FoodBank.Catalog project.

Right-click the project in the Solution Explorer, and select Add\New Item.

On the Add New Item screen, select Blackbaud AppFx Catalog as the category and Smart Field Spec as the template.

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 you can use the smart field in the system. For example, the Smart field tab on the Constituent page displays smart fields where RecordType = “Constituent.”

The SmartFieldFolder attribute defines the folder for 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 element to ensure the stored procedure that calculates 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 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 SQL stored procedure spec. This stored procedure performs the calculation of the smart field.

The stored procedure is named USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT.

This stored procedure accepts the ASOF parameter and FOODBANKTXTYPECODE parameter.

Warning: 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 appears in Last run on column of the Smart Fields grid on the Smart Fields page. 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 in the Visual Studio Solution Explorer, and select Add\New Item.

On the Add New Item screen, select Blackbaud AppFx Catalog as the category and SQL Stored Procedure Spec as the template.

Enter USR_USP_CONSTITUENTSMARTFIELD_FOODBANKTXCOUNT.xml as the XML file name.

The SQL stored procedure spec contains 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 element

The SmartFieldSpec element 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 smart field spec

Step 9 -  Configure a new smart field

From the Administration functional area, click the Smart Fields task under Data. The Smart Fields page displays any existing smart fields.

Step 10 -  Create a smart field

Click the Add action. The Add a smart field definition screen appears. Smart field 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 and click OK.

Step 11 -  Configure the new smart field

On the General tab of the add screen, enter a name and description. We will not use value groups in this example.

On the Parameters tab, select "Receive" in the Transaction Type field.

Step 12 -  Process the smart field

To process the smart field, click the Process smart field action on the Smart Fields page.

After the process begins, the status page for the smart field appears.

Step 13 -  Use the smart field within an ad-hoc query

Navigate to the Analysis functional area.

Click the Query task to begin the process of creating an ad-hoc query.

Click the Information library task to begin the process of creating an ad-hoc query.

Click the Add/Ad-hoc query action and then select the Constituents source view.

On the Queries tab, click the Add an ad-hoc query action and then select the Constituents source view.

Add the Food Bank\Type field to the output.

Add the Food Bank TX Count Smart Field value to the output.

Filter the output for only those food banks that have a type.

Select the Preview results tab.

Here is a link to the Food Bank Source Code.