Select your user interface:
Exercise 9 (Continued): Configure a New Batch and Save Data to the Batch
Step 1 - Review the check constraint on USR_BATCHFOODITEM.
When this table spec was loaded into the Infinity catalog, a table was created with a corresponding database constraint to enforce the TextField's Required="true".
-
In SQL Server Management Studio, open the table and review the check constraint.
Figure: Review the check constraint
-
Edit the batch template. In the Food Item Add Batch Template, we see the Name and Current Cost fields are required and locked. The Required checkbox within the Fields defaults section can not be edited either.
Figure: The Required option is defaulted to true and cannot be changed for the Name field.
This is due to the required attributes within form field metadata within the Add Batch Row Data Form and Edit Batch Row Data Form:
<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" Required="true" 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>
Therefore, at this point we can deduce that even though the table allows missing (null) value for CURRENTCOST, any batch created from the Food Item Add Batch Template will not allow a missing value for CURRENTCOST.
When adding a new batch, we select the Food Item Add Batch Template. Looking at the Field Options, we see we have no choice but to accept the fact that both the NAME and CURRENTCOST fields are required. The required nature of these fields is enforced when trying to save records into the USR_BATCHFOODITEMADD batch table.
Figure: The Name and Current Cost fields are required.
So what actually enforced the validation?
Step 2 - View the FORMDEFINITIONXML column for the batch.
If we look for the appropriate metadata within the BATCH table, we can see that the FORMDEFINITIONXML column contains the form field options for the batch that was created from the template.
-
Using the latest batch number, write a SQL SELECT statement to review the FORMDEFINITIONXML column within the BATCH table.
Figure: View form metadata for the batch
Viewing the XML for the FORMDEFINITIONXML column reveals form field metadata being used by the batch UI grid to help control data entry. Notice how this metadata does not differ from the metadata defined by the Add Data Form. The form metadata was copied, as is, from the FORMDEFINITIONXML column of the BATCHTEMPLATE table, which is not surprising given the fact that new batches are created from a batch template. When we created the batch, we accepted the default form metadata form the template and the FormMetaData reflects the fact.
<FormMetaData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_commontypes"> <FormFields> <FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Hidden="true" Caption="Low Inventory Threshold" DefaultValueText="10" /> <FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name" DefaultValueText="" /> <FormField FieldID="CURRENTCOST" DataType="Money" Required="true" Caption="Current Cost" DefaultValueText="0.00" /> <FormField FieldID="DESCRIPTION" Caption="Description" DefaultValueText="" /> <FormField FieldID="WEIGHT" DataType="Decimal" Caption="Weight" Description="The weight of a food item." Precision="10" Scale="2" DefaultValueText="0.00" /> <FormField FieldID="SEQUENCE" DataType="Integer" Required="true" Hidden="true" Caption="Sequence" /> </FormFields> </FormMetaData>
-
Write a Transact-SQL SELECT statement to view the XML from the FORMDEFINITIONXML column of the BATCHTEMPLATE table.
Step 3 - Create a batch.
-
Create a batch and require that the food item description be required on the new batch.
Figure: Description is now required on the batch and not the batch template.
Query the BATCH table and compare the BATCH.FORMDEFINITIONXML column form metadata to the metadata in the BATCHTEMPLATE. FORMDEFINITIONXML.
-
Inspecting the BATCH.FORMDEFINITIONXML column, we can see the altered form metadata now includes the Required attribute for the DESCRIPTION form field.
<FormMetaData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_commontypes"> <FormFields> <FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Hidden="true" Caption="Low Inventory Threshold" DefaultValueText="10" /> <FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name" DefaultValueText=""/> <FormField FieldID="CURRENTCOST" DataType="Money" Required="true" Caption="Current Cost" DefaultValueText="0.00" /> <FormField FieldID="DESCRIPTION" Required="true" Caption="Description" DefaultValueText="" /> <FormField FieldID="WEIGHT" DataType="Decimal" Caption="Weight" Description="The weight of a food item." Precision="10" Scale="2" DefaultValueText="0.00" /> <FormField FieldID="SEQUENCE" DataType="Integer" Required="true" Hidden="true" Caption="Sequence" /> </FormFields> </FormMetaData>
And this is reflected in the error we receive within the batch grid user interface if we do not provide a description for the food item when we save the batch. The form metadata enforces data integrity.
Figure: Form metadata within the BATCH table helps to enforce data integrity
So to review…
-
The form metadata within the BATCH.FORMDEFINITIONXML column helps to provide simple data integrity checks for data saved to the batch table. This form metadata can differ from what was set up within the batch template
Exceptions from Database Constraints
After we hurdle any form field metadata errors that can prevent us from saving data to batch tables, the platform attempts to save the data to batch tables in the database. The platform calls upon the save implementations within the Add Batch Row Data Form and Edit Batch Row Data Form. Within the BatchTypeSpec, you will find references to the AddRowDataFormTemplate and EditRowDataFormTemplate XML elements that point to the ID attribute of an Add Data Form and an Edit Data Form, respectively. These data forms add rows to the batch and edit rows within the batch from the batch user interface grid.
When the save to the batch occurs, if a row was added to the batch user interface grid, then a call is made to the save implementation of the Add Batch Row Data Form. If a row was edited within the batch user interface grid, then a call is made to the save implementation of the Edit Batch Row Data Form. If no rows were modified or added, no calls are made to save data in the database.
Batch staging tables may contain constraints such as unique, required, foreign key, and format. The degree to which constraints are applied to these tables is up to the discretion of the software developer who should consider the source of the data that feeds these tables. As an example, below we see the TableSpec that defines our USR_BATCHFOODITEMADD table. The yellow highlights define the metadata that could cause issues when we try save a record to the batch.
<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>
In the TableSpec example above, we can expect to receive a database exception if we attempt to add a food item without a name. This constraint is defined by the Required ="true" attribute on the NAMETextField. We can also expect to receive a database exception if we attempt to add the same food item name to the same batch. This unique index constraint is defined by the composite unique index that contains the BATCHID and NAME fields. So, we have included the appropriate metadata within our ExpectedDBExceptions sections of both the Add Batch Row Data Form and the Edit Batch Row Data Form.
<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>
Step 4 - Test the check constraint on the batch table.
Let's conduct a test and see what happens when we attempt to add two food items with the same name for the same Batch ID into the batch table.
Add two "Coconut" food items into the batch UI grid and save the batch.
Figure: An expected database exception is handled within the user interface.
As you can see, we have violated the UIX_USR_BATCHFOODITEMADD_BATCHID_NAME constraint on the database table that was defined within our TableSpec. The ExpectedDBException in the Add Batch Row Data Form caught the exception from the database and presented the gracefully formatted error message to the appropriate row. The end result is the duplicate "Coconut" row was not added to the database.
Exceptions from RAISERROR() within Save Implementations
Custom errors raised from either the Add Batch Row Data Form or the Edit Batch Row Data Form save implementation's stored procedures via RAISERROR() will cause errors when you attempt to save data into a batch table.
Step 5 - Use RAISERROR() within the Add batch Row Data Form.
For example, within your Add Batch Row Data Form, before you attempt to add a row into the table, you can check to see if the description is equal to the name. If so, then the description is not descriptive. We use RAISERROR() to raise a custom exception from the stored procedure.
-
Add the code to compare the name against the description. If the name is equal to the description, then raise a custom error message.
-
After you modify the stored procedure, load the changes to the spec.
DECLARE @ERRORMSG nvarchar(100) IF (@NAME = @DESCRIPTION) or (LEN(@DESCRIPTION) = 0) BEGIN SET @ERRORMSG = 'The description is not descriptive' RAISERROR (@ERRORMSG, 13, 1) END ELSE BEGIN insert into dbo.USR_BATCHFOODITEMADD ( [ID], [BATCHID], [SEQUENCE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED],
-
Attempt to add duplicate food items into batch to invoke the custom error message.
After the exception is raised, we see the error message within the grid.
Figure: Display a custom error