Select your user interface:

Exercise 2: Add Batch Row Data Form a.k.a. "Batch Add"

Next we need a way to add a row into the batch. The Add Batch Row Data Form Spec tells the batch how to add a row to the batch staging table. When using the batch user interface within the shell, the adding of the batch row occurs using a grid. The Infinity platform uses the form field metadata within this Add Data Form to display the columns within the grid and uses the save implementation to save the data, one row at a time, into the staging table.

Figure: Batch user interface

After you click the Save button, the stored procedure defined within the Add Batch Row Data Form Spec is called by the Infinity platform, the values within the grid are passed to the stored procedure and an INSERT statement adds the row to staging tables. Below is a query of the USR_BATCHFOODITEMADD staging table. As you can see, the rows in the figure above were saved to the database table by the stored procedure (not shown) defined within the Add Data Form.

Figure: Saved rows

Step 1 - Add a new AddDataFormTemplateSpec.

The Add Batch Row Data Form Spec is an add data form spec.

Add a new AddDataFormTemplateSpec to the Batch Types\Food Item folder.
The Nameattribute should be "Food Item Add Batch Row Add Form."
The Description should be "Add data form template for food item add batch records."
The spec's RecordType attribute value should equal the name of the batch staging table: "‘Food Item Add Batch."
The Author attribute should be "Technical Training."

Step 2 - Add a Context tag to the Add Data Form Spec.

The data form should contain a Context tag with a ContextRecordType attribute equal to "Batch" that should be mapped to the appropriate parameter within the save implementation stored procedure in the next step.

Context

The "Batch" ContextRecordType referred to within the Context element is a reference to the primary key of the BATCH table. The ContextRecordType attribute refers to a value of a record type. The record types originate from the Name attribute within the associated TableSpec. The context tag below basically indicates that the identifier from the Batch record type (which corresponds to the BATCH table) should be passed into the Add Data Form from the client (that is the calling application, typically the Infinity shell user interface) and the BATCHID parameter within the data form's stored procedure should catch the value.

<Context ContextRecordType="Batch" RecordIDParameter="BATCHID" />

The USR_BATCHFOODITEMADD table holds records for a specific batch. The BATCH table holds a list of batches created from batch templates. Every time we create a batch from batch template, a new row is added to the BATCH table. The context element within the data form ensures the rows that are added to the batch staging table (USR_BATCHFOODITEMADD) references the correct parent row entry within BATCH table. In the entity relationship diagram below, we can see the relationship between the food item batch staging table (USR_BATCHFOODITEMADD) and the BATCH table. A foreign key on the USR_BATCHFOODITEMADD.BATCHID column relates the USR_BATCHFOODITEMADD table to the BATCH table through the BATCH.ID column, which prevents orphan records within the USR_BATCHFOODITEMADD table.

Figure: Batch is the parent table of the staging table.

Step 3 - Add the form fields.

The data form will have form fields for each data element in the batch. You should also include a SEQUENCE form field to track the position of the row amongst the other rows. Add the form fields for the following USR_BATCHFOODITEMADD table columns.

SEQUENCE
NAME
DESCRIPTION
CURRENTCOST
LOWINVENTORYTHRESHOLD
WEIGHT

Step 4 - Add the stored procedure containing an INSERT statement.

At the heart of the spec is an INSERT statement to add the row to the batch table. The data form should contain a Context tag with a ContextRecordType equal to "Batch" that should be mapped to the appropriate parameter within the save implementation stored procedure. The stored procedure should contain a parameter for the SEQUENCE form field, in addition to the main form fields for the table.

Step 5 - Add the ExpectedDBExceptions.

Add the Constraint tag for the required NAME field.
Add the Constraint tag for the unique index on BATCHID, NAME.

Step 6 - Review and load the spec.

Review your spec against the code sample below. Adjust your code, if necessary, and load the spec.

<AddDataFormTemplateSpec 
    xmlns:common="bb_appfx_commontypes"
    ID="cd2f3971-5b17-4b97-8b7f-1f2dbcfddfea"
    Name="Food Item Add Batch Row Add Form"
    Description="Add dataform template for fooditemadd batch records."
    Author="Technical Training"
    RecordType="Food Item Add Batch"
    DataFormInstanceID="9b64c263-ea71-4f10-b2df-02276d191409"
    common:SecurityUIFolder="Constituent\Food Bank\Food Item\Batch"
    SecurityUIDisplayFeature="false"
    xmlns="bb_appfx_adddataformtemplate">
	<SPDataForm>
		<SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODITEMADDBATCHROW">
			<common:CreateProcedureSQL>
  <![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_ADD_BATCHFOODITEMADDBATCHROW
(
	@ID uniqueidentifier = null output,
	@BATCHID uniqueidentifier,
	@CHANGEAGENTID uniqueidentifier,
	@SEQUENCE int,
	@NAME nvarchar(100) = '',
	@DESCRIPTION nvarchar(max) = '',
	@CURRENTCOST money = 0.00,
	@LOWINVENTORYTHRESHOLD smallint = 0,
	@WEIGHT decimal(10,2) = 0.00
)
as
set nocount on;

declare @CURRENTDATE datetime;

if @ID is null
	set @ID = newid();

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

set @CURRENTDATE = getdate();

begin try

insert into dbo.USR_BATCHFOODITEMADD
(
	[ID],
  [BATCHID],
	[SEQUENCE],
	[ADDEDBYID],
	[CHANGEDBYID],
	[DATEADDED],
	[DATECHANGED],
	[NAME],
	[DESCRIPTION],
	[CURRENTCOST],
	[LOWINVENTORYTHRESHOLD],
	[WEIGHT]
) values (
	@ID,
	@BATCHID,
	@SEQUENCE,
	@CHANGEAGENTID,
	@CHANGEAGENTID,
	@CURRENTDATE,
	@CURRENTDATE,
	coalesce(@NAME, ''),
	coalesce(@DESCRIPTION, ''),
	coalesce(@CURRENTCOST, 0.00),
	coalesce(@LOWINVENTORYTHRESHOLD, 0),
	coalesce(@WEIGHT, 0.00)
);
end try
begin catch
	exec.dbo.USP_RAISE_ERROR;
	return 1;
end catch

return 0;

]]></common:CreateProcedureSQL>
      <common:ExpectedDBExceptions>
        <common:Constraints>
<common:Constraint Name="CK_USR_BATCHFOODITEMADD_NAME" Field="NAME" Type="Required" />
<common:Constraint Name="UIX_USR_BATCHFOODITEMADD_BATCHID_NAME" Field="NAME" Type="Unique" />
 </common:Constraints>
      </common:ExpectedDBExceptions>
    </SaveImplementation>
	</SPDataForm>
  
  	<Context ContextRecordType="Batch" RecordIDParameter="BATCHID" />
    
	<common:FormMetaData>
		<common:FormFields>
<common:FormField FieldID="SEQUENCE" Required="true" Hidden="true" Caption="Sequence" />
<common:FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name" />
<common:FormField FieldID="DESCRIPTION" Caption="Description" DefaultValueText="" />
<common:FormField FieldID="CURRENTCOST" DataType="Money" Caption="Current Cost" DefaultValueText="0.00" />
<common:FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Caption="Low Inventory Threshold" DefaultValueText="0" />
<common:FormField FieldID="WEIGHT" DataType="Decimal" Caption="Weight" Description="The weight of a food item." Precision="10" Scale="2" DefaultValueText="0.00" />
		</common:FormFields>
	</common:FormMetaData>
</AddDataFormTemplateSpec>