Exercise 1: Create the Batch Table Spec

Using our food bank customization as an example, let's create a simple batch type that inserts rows into the custom food item table, USR_FOODITEM, which is used to hold a simple listing of Food SKUs such as cereal, rice, or canned soup. Below is a diagram of the production table.

Note: Throughout this section, I will use the term staging table to denote the batch table where the data is staged prior to being committed to the appropriate production table. In the example below, the USR_FOODITEM table is the production table.

Figure: The USR_FOODITEM table is the production table

Step 1 -  Review the table requirements.

The first thing to create is the table that will hold our batch rows. This is known as the batch staging table. For this, we need to use a TableSpec. This table must fulfill the following requirements:

Staging Table Requirements

1. The form fields for the batch staging table will hold data that will ultimately be placed into the production food item table (USR_FOODITEM – See above).

2. The Name field should be required.

3. The values within the Name field may repeat for different batches but may not repeat within the same batch.

4. The food item batch staging table should be a child table of the BATCHtable.

5. When a row is deleted within the BATCH table, the related child rows within the food item batch staging table should also be deleted.

Step 2 -  Review TableSpec guidelines for creating a batch staging table.

Before we create the Table Spec, let's walk through some of the guidelines for third-party developers who develop customizations that extend the out-of-the-box product.

Batch TableSpec Guidelines

1. Tables created by Blackbaud Product Development cannot be altered.

2. Be sure to prefix custom batch table names with the USR_BATCH prefix (e.g. USR_BATCHFOODITEMADD). USR_BATCH is the prefix that is used by third-party developers to ensure no future naming conflicts with tables authored by Blackbaud. Technically, you can use whatever prefix your team devises. Just make sure the prefix is consistent.

3. Be sure to provide a consistent Author for all Infinity specs. The author should not be "Blackbaud Product Development." Use "Technical Training" for the exercises and labs in the SDK training. For your own custom development, you may use any name of your choosing as long as it is consistent and the name is not "Blackbaud Product Development."

4. Table names should be singular (e.g. BOOK instead of BOOKS)

5. Table names should be in all caps (e.g. BOOK instead of book).

6. Use IsBuiltIn = "false". The IsBuiltIn flag is optional within a table spec and the default value is "false." Indicates whether the table represents a standard table defined by Blackbaud, or a user-defined customization. It is important to set this field correctly to avoid conflicts with future updates from Blackbaud.

7. Add a required ForeignKeyField to the BATCH table with OnDelete set to CascadeDelete. For example:

   <ForeignKeyField Name="BATCHID" Required="true" ForeignTable="BATCH" OnDelete="CascadeDelete" />

Step 3 -  Create the folders for the batch type within Visual Studio.

Let's create a folder structure within our catalog projet to better organize our specs.

• With the Blackbaud.CustomFx.FoodBank.Catalog project open, add a new folder named Batch Types.

• Within the Batch Types folder, create a folder named Food Item.

Step 4 -  Add a TableSpec to the "Batch Types\Food Item" folder.

• The Nameattribute should be equal to "Food Item Add Batch."

• The Descriptionattribute should be equal to "Stores food item add batch information."

• The Authorattribute should be equal to "Technical Training."

• The Tablenameattribute should be equal to "USR_BATCHFOODITEMADD."

Step 5 -  Using the diagram for the production table, USR_FOODITEM, add the appropriate fields for the table. You may also borrow fields from the table spec for USR_FOODITEM:

• Name (Required)

• Description

• CurrentCost

• LowInventoryThreshold

• Weight

Step 6 -  Add the SEQUENCE field to the TableSpec.

Step 7 -  Prevent duplicate food item names within each batch.

Add a unique index to the table that prevents duplicate names within each batch.

Step 8 -  Step 8: Review and load the TableSpec.

When you are done, your spec should look like this:

<TableSpec 
    xmlns:common="bb_appfx_commontypes"
    ID="a613c7a0-20db-4618-9112-17b5d6f59113"
    Name="Food Item Add Batch"
    Description="Stores food item add batch information."
    Author="Technical Training"
    Tablename="USR_BATCHFOODITEMADD"
    IsBuiltIn="false"
    xmlns="bb_appfx_table">
  <Fields>
		<ForeignKeyField Name="BATCHID" Required="true" ForeignTable="BATCH" OnDelete="CascadeDelete" />
		<SequenceField Name="SEQUENCE" />
		<TextField Name="NAME" Length="100" Required="true"/>
		<MemoField Name="DESCRIPTION"/>
		<MoneyField Name="CURRENTCOST"/>
		<NumberField Name="LOWINVENTORYTHRESHOLD" Type="smallint" />
		<DecimalField Description="The weight of a food item." Name="WEIGHT" Precision="10" Scale="2" />
    
    
	</Fields>

  <Indexes>
    <Index IsUnique="true">
      <IndexFields>
        <IndexField Name="BATCHID"/>
        <IndexField Name="NAME"/>
      </IndexFields>
    </Index>
  </Indexes>
</TableSpec>