Select your user interface:

Exercise 15: Create the BatchTypeExtensionSpec

With a BatchTypeExtensionSpec, we declare the base table that we add our batch rows to. You can think of the base table as the holding area for our batch extension rows. Our rows are stored here until the batch type that we extend is committed. We must also declare the batch type to extend. Next we need to author and declare stored procedures to add and edit rows to and from the staging area table. These stored procedures manage the data within our staging area for our batch extension rows. When the batch is committed, the data within the staging area needs to move to the production table. After the batch is committed, the rows are transferred from the holding area into production tables via a commit row stored procedure.

To create a BatchTypeExtensionSpec we must provide the following pieces of information:

Declare the batch type to extend by providing the GUID for the BatchTypeCatalogID attribute.
Declare the name of the batch table by providing the SQL Server table name for the BaseTableName attribute. This is the name of the staging area table.

Step 1 - Add the BatchTypeExtensionSpec to the catalog project.

Right-click the Blackbaud.CustomFx.FoodBank.Catalog project and select Add > New Folder… from the context menu. Name the folder "Batch Extensions."
In the new Batch Extensions folder, add a folder named "Constituent Batch Food Bank."
Right-click the Constituent Batch Food Bank folder and select Add > New Item… from the context menu. Select the "Blackbaud AppFx Catalog" category and then select the "Batch Type Extension Spec" item template. Name the spec file "FoodBank.BatchExtension.xml."

Figure: Create a batch type extension

Step 2 - The BaseTableName attribute points to the staging area.

According to the BatchTypeExtensionSpec.xsd schema, the BaseTableName is the name of the main table that will be used to store this batch template's data. This is the staging area table. The table name can be up to 100 characters, beginning with A-Z, followed by one or more uppercase letters and digits. Since it is a custom table, it should use the "USR_" prefix. In the example below, you can see the value of USR_BATCHFOODBANK for the BaseTableName attribute.

Modify the BatchTypeExtensionSpec BaseTableName attribute with the name of the batch extension staging table: USR_BATCHFOODBANK.

<BatchTypeExtensionSpec
	xmlns="bb_appfx_batchtypeextension"
	xmlns:common="bb_appfx_commontypes"
	Author="Technical Training"
	BaseTableName="USR_BATCHFOODBANK"
	BatchTypeCatalogID="A979005C-D15D-4A8A-8173-200318BB453A"
	Description="Batch extension to add food bank information for a constituent."
	ID="f38b46c8-b929-47e2-b3b3-ad6d65756df4"
	Name="Food Bank Batch Extension"
	>…

BatchTypeExtensionSpec BatchTypeCatalogID Attribute

BatchTypeCatalogID is used associate the GUID of the batch type that you are extending with your BatchTypeExtensionSpec. If our BatchTypeExtensionSpec is meant to extend the Constituent Batch then we must provide the globally unique identifier for this batch type via the BatchTypeCatalogID attribute of the BatchTypeExtensionSpec element.

Find the ID for the Batch Type by querying the BATCHTYPECATALOG table within SQL Server. See below The BATCHTYPECATALOG table stores batch types that can be used to create batches for viewing and updating data throughout the system. Place the value for the ID column as the value for the BatchTypeCatalogID within the BatchTypeExtensionSpec element.

Figure: Find the ID for the batch type by querying the BATCHTYPECATALOG table

Using the sample below, add a Table Spec within the same folder as the batch extension spec. The Table Spec will create the Table Spec for the batch extension staging table. The batch extension's base table is named USR_BATCHFOODBANK. See below.

<TableSpec 
	xmlns="bb_appfx_table"
	xmlns:common="bb_appfx_commontypes" 
	ID="c7e5d512-fc69-4f2b-a66e-60a4c53eb994"
	Name="Batch Food Bank"
	Description="Table to store the food bank batch."
	Author="Technical Training"
	Tablename="USR_BATCHFOODBANK" 
	PrimaryKeyAsForeignKeyTablename="BATCHCONSTITUENT">

	<!-- define fields on the table -->
	<Fields>
		<MemoField
			Name="DESCRIPTION"
			Description="A description for the food bank"
			/>

		<MemoField
			Name="MISSIONSTATEMENT"
			Description="The mission statement for the food bank"
			/>

		<CodeTableField
			Name="FOODBANKTYPECODEID"
			Description="The type of food bank."
			CodeTable="USR_FOODBANKTYPECODE"
			/>	
	</Fields>
</TableSpec>

Note that this table must contain a foreign key to the base table of the BATCHTYPE that it is associated with. In the Table Spec listed above (green highlight), the PrimaryKeyAsForeignKeyTablename attribute points to the base table of BATCHCONSTITUENT.

How did we know about the BATCHCONSTITUENT table? If we look in the BATCHTYPECATALOG table for the batch type catalog ID referenced with the above BatchTypeCatalogID="A979005C-D15D-4A8A-8173-200318BB453A," we can see the BASETABLENAME of the batch type we are extending is BATCHCONSTITUENT.

Scripting the CREATE TABLE DDL for the USR_BATCHFOODBANK table, we can see the foreign key constraints between the USR_BATCHFOODBANK and BATCHCONSTITUENT tables:

ALTER TABLE [dbo].[USR_BATCHFOODBANK]  WITH CHECK ADD  CONSTRAINT [FK_USR_BATCHFOODBANK_ID] FOREIGN KEY([ID])
REFERENCES [dbo].[BATCHCONSTITUENT] ([ID])
ON DELETE CASCADE
GO

Manipulate Data within the Staging Area Table with AddRow and EditRow

The AddRow and EditRow elements within the BatchTypeExtensionSpec element pertain to the mechanisms to add and edit the rows within the staging area table. The stored procedure defined within the SaveImplementation of the AddRow element allows for the addition of a row to the staging table. The stored procedure defined within the LoadImplementation of the EditRow element retrieves row from the staging area table to be edited while the stored procedure defined within the SaveImplementation of the EditRow element saves the edited row back to the staging area table.

You can think of the AddRow and EditRow tags as embedded business/data logic for both Add and Edit Data Forms. At the bottom of the BatchTypeExtensionSpec, you find the form fields that describe the fields within the batch's UI grid. These form fields correspond to the parameter on the stored procedures.

Step 3 - AddRow.

Read the following material below and add the AddRow tag to your BatchTypeExtensionSpec.

The values used to insert data into the staging area table are provided from two sources:

By the user who enters the data into the batch
By the stored procedure itself

The stored procedure for the AddRow section must include the parameters in the table below.

Add Data Form Stored Procedure Required Parameters
Name	Data Type	Description
ID	unique identifier	Output parameter that returns the generated ID of the row being inserted.
CHANGEAGENTID	unique identifier	The ID of the agent executing the operation.

The AddRow tag contains a SaveImplementation stored procedure that inserts a row into the staging area table. Just like authoring a save implementation for an Add Data Form, the stored procedure author is responsible for handling the @ID of the new rows as well as the @CHANGEAGENTID and the @CURRENTDATE. A stored procedure written for an Add Data Form is required to have certain behaviors. Specifically, the stored procedure:

Creates a new ID for the row we insert (using the "newid ()" function) and assigns it to the output ID parameter.
Assigns the change agent ID parameter (or creates one if it's null) to the "added by" and "changed by" columns.
Assigns the current date (obtained with the "getdate ()" function) to the "date added" and "date changed" columns.

Warning: Please note that the @ID parameter must be marked as an OUTPUT parameter. The Infinity platform uses the GUID returned as the context of the new row.

<AddRow  DataFormInstanceID="797378c7-6d14-4c7e-9474-78c587157a23" 
          DataFormTemplateID="1c7cdaf5-df7c-41f9-aa77-3134502c0fde" >
	<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODBANK">
		<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODBANK(
	@ID uniqueidentifier output,
	@DESCRIPTION nvarchar(max) = null,
	@MISSIONSTATEMENT nvarchar(max) = null,
	@FOODBANKTYPECODEID uniqueidentifier = null,
	@CHANGEAGENTID uniqueidentifier = null)
as	  
	  set nocount on;
		
	if @ID is null
		set @ID = newid()

	if @CHANGEAGENTID is null  
		exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime = getdate()

    begin try
	    insert into dbo.USR_BATCHFOODBANK(
			ID,
			DESCRIPTION,
			MISSIONSTATEMENT,
			FOODBANKTYPECODEID,
			ADDEDBYID,
			CHANGEDBYID,
			DATEADDED,
			DATECHANGED)
		values(
			@ID,
			@DESCRIPTION,
			@MISSIONSTATEMENT,
			@FOODBANKTYPECODEID,
			@CHANGEAGENTID,
			@CHANGEAGENTID,
			@CURRENTDATE,
			@CURRENTDATE)
		End try
	begin catch
	  exec dbo.USP_RAISE_ERROR;
	  return 1;
	end catch	
	return 0;] ]>
		</common:CreateProcedureSQL>
	</SaveImplementation>
</AddRow>

Step 4 - EditRow.

Within the LoadImplementation tag within EditRow tag, you will find a stored procedure to retrieve the row out from the staging table given the @ID of the row. Similarly to an Edit Data Form the author of this stored procedure is responsible for setting the @DATALOADED and @TSLONG parameters.

When creating the AddRow tag, we just had to create a stored procedure that inserted a row into our table. However, with an EditRow you have to create one stored procedure to load the data and an additional stored procedure to update the data. The stored procedure to load the data uses a simple SELECT Transact-SQL statement that grabs the appropriate row using the @ID parameter in the WHERE clause. The stored procedure to save the row uses a simple UPDATE Transact-SQL statement that updates the appropriate row using the @ID parameter in the WHERE clause.

If you look closely at the stored procedure within the LoadImplementation element, you will notice the @DataLoaded parameter and the @TSLONG parameter. Both parameters are output parameters, meaning that the platform will use output parameters to return values to the caller of the procedure.

Add the following EditRow tag to the BatchTypeExtensionSpec

<EditRow DataFormInstanceID="cd9499cb-e5bf-4569-b955-244299a57ab0" 
           DataFormTemplateID="e3d526db-ae75-4d73-be2b-0d344af076ea">
    
<LoadImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITLOAD_BATCHFOODBANK">
	<common:CreateProcedureSQL>
		<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITLOAD_BATCHFOODBANK(
	@ID uniqueidentifier,
	@DESCRIPTION nvarchar(max) = null output,
	@MISSIONSTATEMENT nvarchar(max) = null output,
	@FOODBANKTYPECODEID uniqueidentifier = null output,
	@TSLONG bigint = 0 output,
	@DATALOADED bit = 0 output
)
as
	set nocount on;

	set @DATALOADED = 0;
	set @TSLONG = 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.  Also note that we fetch the TSLONG so that concurrency
	-- can be considered.
	select
		@DATALOADED = 1,
		@DESCRIPTION = DESCRIPTION,
		@MISSIONSTATEMENT = MISSIONSTATEMENT,
		@FOODBANKTYPECODEID = FOODBANKTYPECODEID,
		@TSLONG = TSLONG
	from
		dbo.USR_BATCHFOODBANK
	where
		ID = @ID
    
	return 0;] ]>
	</common:CreateProcedureSQL>
</LoadImplementation>

The stored procedure to save our data to the database sets the CHANGEDBYID and DATECHANGED fields within our table. This save procedure is similar to the AddRow, but we're issuing an update command instead of an insert, and only modifying the "last changed" fields instead of the "created" fields.

<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITSAVE_BATCHFOODBANK">
	<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITSAVE_BATCHFOODBANK(
	@ID uniqueidentifier,
	@DESCRIPTION nvarchar(max),
	@MISSIONSTATEMENT nvarchar(max),
	@FOODBANKTYPECODEID uniqueidentifier,
	@CHANGEAGENTID uniqueidentifier = null)
as								 
	set nocount on;
	if @CHANGEAGENTID is null  
		exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
				
	declare @CURRENTDATE datetime = getdate() 
	begin try
		update dbo.USR_BATCHFOODBANK set
			DESCRIPTION = @DESCRIPTION,
			MISSIONSTATEMENT = @MISSIONSTATEMENT,
			FOODBANKTYPECODEID = @FOODBANKTYPECODEID,
			CHANGEDBYID = @CHANGEAGENTID,
			DATECHANGED = @CURRENTDATE
		where
			ID = @ID	
	End try
	begin catch
		exec dbo.USP_RAISE_ERROR;
		return 1;
	end catch] ]>
		</common:CreateProcedureSQL>
</SaveImplementation>
</EditRow>

Step 5 - CommitRow.

Review the material below and add the CommitRow tag to the spec.

The CommitRow tag is responsible for committing the rows from staging tables into production tables. Since our batch extension only adds rows to the database, we only need to provide the save implementation for the AddRow tag. Within the AddRow tag, notice the ContextRecordType and RecordIDParameter attributes (yellow and green highlights below). The ContextRecordType attribute prompts us for the type of value that will come into the batch extension. Since we add food bank records for constituents, the ContextRecordType value is "Constituent." We will need a constituent ID value to add the food bank records for the appropriate constituent. The constituent ID value comes into the stored procedure via the CONSTITUENTID parameter.

Figure: The RecordIDParameter attribute maps the context value to the save implementation stored procedure parameter.

Validation within Commit for a Batch Extension

As for the commit stored procedure, we will first call upon a USR_USP_FOODBANK_BATCHEXTVALIDATE custom stored procedure to verify the following:

The constituent added to the database is an organization.
The food bank has a food bank type code.

Figure: Validation within the commit for a batch extension is a little different...

Below is the SQLStoredProcedureSpec that defines the USR_USP_FOODBANK_BATCHEXTVALIDATE stored procedure.

Why perform such validation during the commit? What about validating a batch extension before committing? Hey, where is the @VALIDATEONLY Flag within the commit stored procedure for a batch extension?

"Validate Only" is not recognized within batch extensions. The value for the @VALIDATEONLY flag does not get passed to the commit for batch extensions.

If either business rule is violated, then the stored procedure raises an error to our commit stored procedure and the appropriate custom exception ExpectedDBExceptions tag will help gracefully describe the error to the end user.

Figure: The ExpectedDBExceptions tag will help gracefully describe the error to the end user.

If no errors are raised from USR_USP_FOODBANK_BATCHEXTVALIDATE then an insert is attempted to the USR_FOODBANK production table. As a nice extra feature, we will add a "food bank" user defined constituency code to the organization's record. Below is the code for both the CommitRow section of the batch extension and the USR_USP_FOODBANK_BATCHEXTVALIDATE stored procedure.

<CommitRow>
    <AddRow ContextRecordType="Constituent"
            DataFormInstanceID="4878d9c2-bb1f-4abb-aa7b-ea16623d96d4"
            DataFormTemplateID="9edf325e-1cb1-404d-8fb8-5c50c0ee8aed"
            RecordIDParameter="CONSTITUENTID">
      <SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_COMMITADD_BATCHFOODBANK">
        <common:CreateProcedureSQL>
          <![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_COMMITADD_BATCHFOODBANK(	
	@ID uniqueidentifier output,
	@CONSTITUENTID uniqueidentifier,
	@DESCRIPTION nvarchar(max) = null,
	@MISSIONSTATEMENT nvarchar(max) = null,
	@FOODBANKTYPECODEID uniqueidentifier = null,
	@CHANGEAGENTID uniqueidentifier = null)
AS
	set nocount on;
	 
	DECLARE @FoodBankTypeCodeCountID as uniqueidentifier
	DECLARE @CONSTITUENCYID as uniqueidentifier
	DECLARE @CURRENTDATE datetime = getdate()
	
	BEGIN TRY
		-- check to see if constituent is an organziation if not then error will be raised.
		EXEC USR_USP_FOODBANK_BATCHEXTVALIDATE @CONSTITUENTID, @FOODBANKTYPECODEID

  
		insert into dbo.USR_FOODBANK(
			ID,
			CONSTITUENTID,
			DESCRIPTION,
			MISSIONSTATEMENT,
			FOODBANKTYPECODEID,
			ADDEDBYID,
			CHANGEDBYID,
			DATEADDED,
			DATECHANGED)
		values(
			@CONSTITUENTID,
			@CONSTITUENTID,
			@DESCRIPTION,
			@MISSIONSTATEMENT,
			@FOODBANKTYPECODEID,
			@CHANGEAGENTID,
			@CHANGEAGENTID,
			@CURRENTDATE,
			@CURRENTDATE)
      
			-- MAKE SURE WE HAVE A FOOD BANK USER DEFINED 
      -- CONSTITUENCY IN THE CONSTITUENCYCODE TABLE
			SELECT @FoodBankTypeCodeCountID = ID
			FROM CONSTITUENCYCODE
			WHERE DESCRIPTION = 'Food Bank'
    
			-- IF WE DONT,  ADD ONE TO THE CONSTITUENCYCODE  TABLE
		   IF @FoodBankTypeCodeCountID IS NULL
		   BEGIN
				EXEC dbo.[USP_CONSTITUENCYCODE_CREATEENTRY] @DESCRIPTION=N'Food Bank',@ACTIVE=1,@CHANGEAGENTID=@CHANGEAGENTID
		   
			SELECT @FoodBankTypeCodeCountID = ID
			FROM CONSTITUENCYCODE
			WHERE DESCRIPTION = 'Food Bank'
		   END
 
		   -- ADD THE NEW ORGANIZATION/CONSTITUENT TO THE USER DEFINED CONSTITUENCY
		   EXEC  dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENCY  
			@CONSTITUENCYID output
			, @CHANGEAGENTID
			, @CONSTITUENTID
			, @FoodBankTypeCodeCountID
	
	END TRY
   
  BEGIN CATCH
		exec dbo.USP_RAISE_ERROR;
		return 1;
	END CATCH

	RETURN 0;	
          ] ]>
        </common:CreateProcedureSQL>

        <common:ExpectedDBExceptions>
          <common:Constraints>
            <common:Constraint Name="FK_USR_FOODBANK_FOODBANKTYPECODEID" Field="FOODBANKTYPECODEID" Type="ForeignKey" />
          </common:Constraints>
          <common:CustomExceptions>      
            <common:Exception Field="DESCRIPTION" SearchText="Constituent is not an organization">
              <common:CustomErrorMsg>Constituent is not an organization. Can not add food bank data.</common:CustomErrorMsg>
            </common:Exception>
            <common:Exception Field="FOODBANKTYPECODEID" SearchText="Food Bank Type Code is required">
              <common:CustomErrorMsg>Food Bank Type Code is required</common:CustomErrorMsg>
            </common:Exception>
          </common:CustomExceptions>
        </common:ExpectedDBExceptions>
        
      </SaveImplementation>
    </AddRow>
  </CommitRow>

Here is the code for the stored procedure:

<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes"
	Name="USR_USP_FOODBANK_BATCHEXTVALIDATE"
	ID="001a0977-b446-445b-8e80-9103c6e3e575"
	Description="USR_USP_FOODBANK_BATCHEXTVALIDATE"
	Author="Technical Training"
	SPName="USR_USP_FOODBANK_BATCHEXTVALIDATE"
	GrantServiceRolePermission="false">
  <CreateProcedureSQL>
    <![CDATA[
			create procedure dbo.USR_USP_FOODBANK_BATCHEXTVALIDATE(
				 @CONSTITUENTID uniqueidentifier
        ,@FOODBANKTYPECODEID uniqueidentifier = null) as
				set nocount on;
								
				DECLARE @ISORG as bit = 0
  
        SELECT @ISORG = C.ISORGANIZATION
        FROM dbo.CONSTITUENT AS C
	      WHERE C.ID = @CONSTITUENTID; 
  
 	      IF @ISORG = 0 
        BEGIN
          raiserror('Constituent is not an organization', 13, 1)
					return 1;
        END
        
        IF @FOODBANKTYPECODEID IS NULL 
        BEGIN
          raiserror('Food Bank Type Code is required', 13, 1)
					return 1;
        END
        
				return 0;] ]>
  </CreateProcedureSQL>
</SQLStoredProcedureSpec>

Step 6 - FormMetaData.

Next, we need to describe on the form the form fields that correspond to the parameters on the stored procedure. Since we are extending the batch with the food bank description, mission statement, and food bank type code fields, add the following form metadata to the BatchTypeExtensionSpec:

<common:FormMetaData>
  <common:FormFields>
     <common:FormField FieldID="DESCRIPTION" Caption="Description" DataType="String"   	Multiline="true" Category="Food bank" />
     <common:FormField FieldID="MISSIONSTATEMENT" Caption="Mission statement" DataType="String" 	Multiline="true" Category="Food bank" />
     <common:FormField FieldID="FOODBANKTYPECODEID" Caption="Type" DataType="Guid" Category="Food 	bank">
	<common:CodeTable CodeTableName="USR_FOODBANKTYPECODE" />
     </common:FormField>
</common:FormFields>
</common:FormMetaData>

Step 7 - Add the DependencyList element.

Our batch extension has two dependencies that we need to account for with a DependencyList tag. First is the staging table for our extension, and the second is for the validation procedure.

Using the XML below as a guide, add the dependencies to the BatchTypeExtensionSpec:

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

Step 8 - Review and load the completed BatchTypeExtensionSpec.

With the sample below as a guide, review and complete your batch extension.

<BatchTypeExtensionSpec
	xmlns="bb_appfx_batchtypeextension"
	xmlns:common="bb_appfx_commontypes"
	Author="Technical Training"
	BaseTableName="USR_BATCHFOODBANK"
	BatchTypeCatalogID="A979005C-D15D-4A8A-8173-200318BB453A"
	Description="Batch extension to add food bank information for a constituent."
	ID="f38b46c8-b929-47e2-b3b3-ad6d65756df4"
	Name="Food Bank Batch Extension">

  <common:DependencyList>
    <common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll"
     CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.BatchFoodBank.Table.xml" />
    <common:Dependency CatalogAssembly="Blackbaud.CustomFx.FoodBank.Catalog.dll"
     CatalogItem="Blackbaud.CustomFx.FoodBank.Catalog.USR_USP_FOODBANK_BATCHEXTVALIDATE.xml" />
  </common:DependencyList>
  
	<AddRow  DataFormInstanceID="797378c7-6d14-4c7e-9474-78c587157a23" 
          DataFormTemplateID="1c7cdaf5-df7c-41f9-aa77-3134502c0fde" >
    
		<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODBANK">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODBANK(
	@ID uniqueidentifier output,
	@DESCRIPTION nvarchar(max) = null,
	@MISSIONSTATEMENT nvarchar(max) = null,
	@FOODBANKTYPECODEID uniqueidentifier = null,
	@CHANGEAGENTID uniqueidentifier = null)
as	  
	  set nocount on;
		
	if @ID is null
		set @ID = newid()

	if @CHANGEAGENTID is null  
		exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime = getdate()

    begin try
	    insert into dbo.USR_BATCHFOODBANK(
			ID,
			DESCRIPTION,
			MISSIONSTATEMENT,
			FOODBANKTYPECODEID,
			ADDEDBYID,
			CHANGEDBYID,
			DATEADDED,
			DATECHANGED)
		values(
			@ID,
			@DESCRIPTION,
			@MISSIONSTATEMENT,
			@FOODBANKTYPECODEID,
			@CHANGEAGENTID,
			@CHANGEAGENTID,
			@CURRENTDATE,
			@CURRENTDATE)
		End try
	begin catch
	  exec dbo.USP_RAISE_ERROR;
	  return 1;
	end catch	
	return 0;							 
						  ] ]>
			</common:CreateProcedureSQL>


      <common:ExpectedDBExceptions>
        <common:Constraints>
          <common:Constraint Name="FK_USR_BATCHFOODBANK_FOODBANKTYPECODEID" Field="FOODBANKTYPECODEID" Type="ForeignKey" />
          
        </common:Constraints>
      </common:ExpectedDBExceptions>
		</SaveImplementation>
    
	</AddRow>
  
	<EditRow DataFormInstanceID="cd9499cb-e5bf-4569-b955-244299a57ab0" 
           DataFormTemplateID="e3d526db-ae75-4d73-be2b-0d344af076ea">
    
		<LoadImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITLOAD_BATCHFOODBANK">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITLOAD_BATCHFOODBANK(
	@ID uniqueidentifier,
	@DESCRIPTION nvarchar(max) = null output,
	@MISSIONSTATEMENT nvarchar(max) = null output,
	@FOODBANKTYPECODEID uniqueidentifier = null output,
	@TSLONG bigint = 0 output,
	@DATALOADED bit = 0 output
)
as
	set nocount on;

	set @DATALOADED = 0;
	set @TSLONG = 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.  Also note that we fetch the TSLONG so that concurrency
	-- can be considered.
	select
		@DATALOADED = 1,
		@DESCRIPTION = DESCRIPTION,
		@MISSIONSTATEMENT = MISSIONSTATEMENT,
		@FOODBANKTYPECODEID = FOODBANKTYPECODEID,
		@TSLONG = TSLONG
	from
		dbo.USR_BATCHFOODBANK
	where
		ID = @ID
    
	return 0;  
					    ] ]>
			</common:CreateProcedureSQL>
		</LoadImplementation>
    
		<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITSAVE_BATCHFOODBANK">
			<common:CreateProcedureSQL>
				<![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITSAVE_BATCHFOODBANK(
	@ID uniqueidentifier,
	@DESCRIPTION nvarchar(max),
	@MISSIONSTATEMENT nvarchar(max),
	@FOODBANKTYPECODEID uniqueidentifier,
	@CHANGEAGENTID uniqueidentifier = null
)
as								 
	set nocount on;
  
	if @CHANGEAGENTID is null  
		exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
				
	declare @CURRENTDATE datetime = getdate() 
	
	begin try
		update dbo.USR_BATCHFOODBANK set
			DESCRIPTION = @DESCRIPTION,
			MISSIONSTATEMENT = @MISSIONSTATEMENT,
			FOODBANKTYPECODEID = @FOODBANKTYPECODEID,
			CHANGEDBYID = @CHANGEAGENTID,
			DATECHANGED = @CURRENTDATE
		where
			ID = @ID	
	End try
	begin catch
		exec dbo.USP_RAISE_ERROR;
		return 1;
	end catch									 
						  ] ]>
			</common:CreateProcedureSQL>

      <common:ExpectedDBExceptions>
        <common:Constraints>
          <common:Constraint Name="FK_USR_BATCHFOODBANK_FOODBANKTYPECODEID" Field="FOODBANKTYPECODEID" Type="ForeignKey" />
         
        </common:Constraints>
      </common:ExpectedDBExceptions>

    </SaveImplementation>
    
	</EditRow>

  <CommitRow>
    <AddRow ContextRecordType="Constituent"
            DataFormInstanceID="4878d9c2-bb1f-4abb-aa7b-ea16623d96d4"
            DataFormTemplateID="9edf325e-1cb1-404d-8fb8-5c50c0ee8aed"
            RecordIDParameter="CONSTITUENTID">
      <SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_COMMITADD_BATCHFOODBANK">
        <common:CreateProcedureSQL>
          <![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_COMMITADD_BATCHFOODBANK(	
	@ID uniqueidentifier output,
	@CONSTITUENTID uniqueidentifier,
	@DESCRIPTION nvarchar(max) = null,
	@MISSIONSTATEMENT nvarchar(max) = null,
	@FOODBANKTYPECODEID uniqueidentifier = null,
	@CHANGEAGENTID uniqueidentifier = null)
AS
	set nocount on;
	 
	DECLARE @FoodBankTypeCodeCountID as uniqueidentifier
	DECLARE @CONSTITUENCYID as uniqueidentifier
	DECLARE @CURRENTDATE datetime = getdate()
	
	BEGIN TRY
		-- check to see if constituent is an organziation if not then error will be raised.
		EXEC USR_USP_FOODBANK_BATCHEXTVALIDATE @CONSTITUENTID, @FOODBANKTYPECODEID

  
		insert into dbo.USR_FOODBANK(
			ID,
			CONSTITUENTID,
			DESCRIPTION,
			MISSIONSTATEMENT,
			FOODBANKTYPECODEID,
			ADDEDBYID,
			CHANGEDBYID,
			DATEADDED,
			DATECHANGED)
		values(
			@CONSTITUENTID,
			@CONSTITUENTID,
			@DESCRIPTION,
			@MISSIONSTATEMENT,
			@FOODBANKTYPECODEID,
			@CHANGEAGENTID,
			@CHANGEAGENTID,
			@CURRENTDATE,
			@CURRENTDATE)
      
			-- MAKE SURE WE HAVE A FOOD BANK USER DEFINED 
      -- CONSTITUENCY IN THE CONSTITUENCYCODE TABLE
			SELECT @FoodBankTypeCodeCountID = ID
			FROM CONSTITUENCYCODE
			WHERE DESCRIPTION = 'Food Bank'
    
			-- IF WE DONT,  ADD ONE TO THE CONSTITUENCYCODE  TABLE
		   IF @FoodBankTypeCodeCountID IS NULL
		   BEGIN
				EXEC dbo.[USP_CONSTITUENCYCODE_CREATEENTRY] @DESCRIPTION=N'Food Bank',@ACTIVE=1,@CHANGEAGENTID=@CHANGEAGENTID
		   
			SELECT @FoodBankTypeCodeCountID = ID
			FROM CONSTITUENCYCODE
			WHERE DESCRIPTION = 'Food Bank'
		   END
 
		   -- ADD THE NEW ORGANIZATION/CONSTITUENT TO THE USER DEFINED CONSTITUENCY
		   EXEC  dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENCY  
			@CONSTITUENCYID output
			, @CHANGEAGENTID
			, @CONSTITUENTID
			, @FoodBankTypeCodeCountID
	
	END TRY
   
  BEGIN CATCH
		exec dbo.USP_RAISE_ERROR;
		return 1;
	END CATCH

	RETURN 0;	
          ] ]>
        </common:CreateProcedureSQL>

        <common:ExpectedDBExceptions>
          <common:Constraints>
            <common:Constraint Name="FK_USR_FOODBANK_FOODBANKTYPECODEID" Field="FOODBANKTYPECODEID" Type="ForeignKey" />
          </common:Constraints>
          <common:CustomExceptions>      
            <common:Exception Field="DESCRIPTION" SearchText="Constituent is not an organization">
              <common:CustomErrorMsg>Constituent is not an organization. Can not add food bank data.</common:CustomErrorMsg>
            </common:Exception>
            <common:Exception Field="FOODBANKTYPECODEID" SearchText="Food Bank Type Code is required">
              <common:CustomErrorMsg>Food Bank Type Code is required</common:CustomErrorMsg>
            </common:Exception>
          </common:CustomExceptions>
        </common:ExpectedDBExceptions>
        
      </SaveImplementation>
    </AddRow>
  </CommitRow>

	<!-- optionally, list any client-side event handlers that will be plugged into the event model for the batch data entry form.  Each handler
	refers to a class that inherits from Browser.Batch.BatchEntryHandler -->
	<!--<EventHandler AssemblyName="REPLACE_WITH_ASSEMBLYNAME" ClassName="REPLACE_WITH_CLASSNAME"/>-->

 	<!-- describe fields on the form, which correspond to parameters on the SP.  Note that system paramters 
	like the record ID, DATALOADED, TSLONG, and CURRENTAPPUSERID need not be listed. -->
	<common:FormMetaData>
		<common:FormFields>
			<common:FormField FieldID="DESCRIPTION" Caption="Description" DataType="String" Multiline="true" Category="Food bank" />
			<common:FormField FieldID="MISSIONSTATEMENT" Caption="Mission statement" DataType="String" Multiline="true" Category="Food bank" />
			<common:FormField FieldID="FOODBANKTYPECODEID" Caption="Type" DataType="Guid" Category="Food bank">
				<common:CodeTable CodeTableName="USR_FOODBANKTYPECODE" />
			</common:FormField>
		</common:FormFields>
	</common:FormMetaData>
</BatchTypeExtensionSpec>

Step 9 - Review the spec metadata.

The AddRow, EditRow, and CommitRow tags contains GUID for both the instance ID and the template ID.

Figure: Reference the data forms

When the BatchTypeExtensionSpec is loaded into the catalog tables, a single row is added into the BATCHTYPEEXTENSIONCATALOG table and multiple rows are added to the DATAFORMTEMPLATECATALOG table and the DATAFORMINSTANCECATALOG table.

Using the BatchTypeExtensionSpec's ID attribute, query the BATCHTYPEEXTENSIONCATALOG table and review the metadata for the row.
Note the values for the following columns in relation to the spec:
- [BATCHTYPECATALOGID]
- [NAME]
- [DESCRIPTION]
- [BASETABLENAME]
- [FORMUIXML]
- [ADDDATAFORMTEMPLATEID]
- [EDITDATAFORMTEMPLATEID]
- [COMMITADDDATAFORMTEMPLATEID]
- [CONTEXTRECORDID]
- [SPECXML]
Note the value for COMMITEDITDATAFORMTEMPLATEID column. Why is it null?
Using the IDs from the AddRow, EditRow, and CommitRowtags, query the DATAFORMINSTANCECATALOG. Review the XML within the FORMUIXML. Compare this against the form fields within the BatchTypeExtensionSpec.

Take the Batch Extension for Test Drive

Click to the Batch task within the Administration functional area and then click the Batch entry task to open the Batch Entry page.

Figure: Test the batch extension
Select the Batch templates action within the Configuration action group opens the Batch Templates page.

Figure: Create a batch template
From the Batch Templates page, add a new batch template based on the Constituent Batch type.

Figure: Add a batch template
On the Select a batch type screen, select Constituent Batch as the batch type.
On the Add a batch template screen, provide a batch template name, description, workflow, and numbering scheme.

Figure: Name the new batch template
Select the Select fields and defaults section, and in the Available fields options list, expand the Food bank folder and add the Description, Mission statement, and Type fields to the list of selected fields.

Figure: Add our batch extension fields
Add some defaults for the selected fields to make it easier to add rows to the batch.

Figure: Add defaults
Save the template. Navigate back to the Batch Entry page and add a new batch based on our new Constituent Batch with Food Bank Extension Template.

Figure: Add a new batch that is based on our new template
With the batch open, add two new food bank organizations. Next, check out the commit logic within our batch extension. Leave the Type column blank on the first row in the batch and change the constituent type to Individual within the second row within the batch.

Figure: Check out the commit logic
Save and close the batch. Commit the batch with the following options:

Figure: Commit the batch
You should see that the batch completed with two errors:

Figure: Exceptions
Navigate back to the Batch Entry page and review the exception batch to see the custom errors raised from the USR_USP_FOODBANK_BATCHEXTVALIDATE stored procedure:
The rows did not pass batch extension validation.
Clean up the offending errors and commit the exception batch. The batch should commit without exceptions. Review the organization record created with user-defined constituency and the food bank record added via our batch extension.

Figure: Review a committed row

Figure: New food bank within the food bank page