Collection Form Fields

Since we will replace the out-of-the-box BATCHCONSTITUENTPHONE table with the USR_BATCHCONSTITUENTPHONE table within our batch extension, we will need a way to capture the original form fields used to add phone records in addition to a new form field for our BESTTIMETOCALLCODE field. Since the phone records are child records in relation to the parent constituent record, they will represented as a collection of form fields within the batch extension. The batch extension's forms field metadata for the phone records may be found below:

  <common:FormFields>
      <common:FormField FieldID="EXTPHONES" Caption="PhonesExt" CaptionResourceKey="$$phones">
        <common:Collection SequenceFieldID="SEQUENCE">
          <common:Fields>
            <common:FormField FieldID="ID" Caption="ID" DataType="Guid" Hidden="true" />
            <common:FormField FieldID="PHONETYPECODEID" Caption="Phone type" DataType="Guid" 			CaptionResourceKey="$$phone_type">
              <common:CodeTable CodeTableName="PHONETYPECODE" />
            </common:FormField>
            <common:FormField FieldID="NUMBER" Caption="Phone number" DataType="String" 				ApplyPhoneFormatting="true" Required="true" CaptionResourceKey="$$phone_number" />
            <common:FormField FieldID="COUNTRYID" Caption="Country" DataType="Guid" 				CaptionResourceKey="$$country">
              <common:SimpleDataList SimpleDataListID="C9649672-353D-42E8-8C25-4D34BBABFBBA" />
            </common:FormField>
            <common:FormField FieldID="ISPRIMARY" Caption="Primary" DataType="Boolean" 				CaptionResourceKey="$$primary" />
            <common:FormField FieldID="SEASONALSTARTDATE" Caption="Seasonal start" 				DataType="MonthDay" DefaultValueText="0000" CaptionResourceKey="$$seasonal_start" 			/>
            <common:FormField FieldID="SEASONALENDDATE" Caption="Seasonal end" 					DataType="MonthDay" DefaultValueText="0000" CaptionResourceKey="$$seasonal_end" />
            <common:FormField FieldID="SEQUENCE" Caption="Sequence" DataType="Integer" 				Hidden="true" />
            <common:FormField FieldID="STARTTIME" Caption="Call after" DataType="HourMinute" 			CaptionResourceKey="$$call_after" />
            <common:FormField FieldID="ENDTIME" Caption="Call before" DataType="HourMinute" 			CaptionResourceKey="$$call_before" />
            <common:FormField FieldID="STARTDATE" Caption="Start date" DataType="Date" 				CaptionResourceKey="$$start_date" />
            <common:FormField FieldID="BESTTIMETOCALLCODE" 
                              Caption="Best Time To Call" DataType="TinyInt" 
                              Description="Best time to call" 
                              DefaultValueText="3">
                <common:ValueList>
                  <common:Items>
                    <common:Item>
                        <common:Value>0</common:Value>
                        <common:Label>Morning</common:Label>
                    </common:Item>
                    <common:Item>
                      <common:Value>1</common:Value>
                        <common:Label>Midday</common:Label>
                    </common:Item>
                    <common:Item>
                      <common:Value>2</common:Value>
                        <common:Label>Afternoon</common:Label>
                    </common:Item>
                    <common:Item>
                      <common:Value>3</common:Value>
                        <common:Label>After Work</common:Label>
                    </common:Item>
                    <common:Item>
                      <common:Value>4</common:Value>
                        <common:Label>Evening</common:Label>
                    </common:Item>
                  </common:Items>
                </common:ValueList>
            </common:FormField>
          </common:Fields>
        </common:Collection>
      </common:FormField>
  </common:FormFields>

In the form fields above, note how the collection field (green highlight) is wrapped with form field (yellow highlight) that has a FieldID attribute equal to EXTPHONES. Also notice within the collection field you find the original phone form fields from the Constituent Batch and an additional BESTTIMETOCALLCODE (blue highlight) for our new custom fields that we are adding with our batch extension.

Within batches, child tables are used to hold child data such as the phone data. A collection of form fields represent the child data for the phone form fields. When data is saved to the batch phone staging table (USR_BATCHCONSTITUENTPHONE) via the add and edit stored procedures or when the data is committed from the staging table to the phone production tables (PHONE and USR_PHONE) the data within the collection of form fields is passed as XML. Each row (collection item) within the form field batch user interface grid is represented as an <Item> element wrapped a parent tag <EXTPHONES>. Note how <EXTPHONES> tag within the XML data corresponds with the value of the FieldID attribute <common:FormField FieldID="EXTPHONES" Caption="PhonesExt" CaptionResourceKey="$$phones"> within the form field metadata above.

A collection of form fields represent the child data for the phone form fields

Use the CreateChildCollectionFunction Tag within a SQLFunctionSpec

Dealing with the XML passed to and from the database within our batch extension's stored procedures can be tricky to manage. Luckily, we can author a SQLFunctionSpec to retrieve the rows from the staging table and by adding a little additional XML tag; we can prompt the Infinity platform to create some useful functions and stored procedures in addition to the SQLFunctionSpec to manage the XML.

Next, we will author a SQLFunctionSpec that will retrieve the rows from our custom USR_BATCHCONSTITUENTPHONE staging table that our custom batch extension uses. We will include the CreateChildCollectionFunction XML element below the CreateFunctionSQL XML element. Including the CreateChildCollectionFunction element, the platform will generate some helpful and time-saving user-defined functions and stored procedures that help to retrieve and save our collection data that is expressed as XML. When saving data for the batch, data within collection fields such as our phone data is represented as an XML document. This XML document is passed from the batch user interface grid to the appropriate stored procedure for updating or saving of the rows.

When the CreateChildCollectionFunction element is included within the SQLFunctionSpec, the Infinity platform will create two translation user-defined functions that help manage the XML being passed:

  1. The FROMITEMLISTXML table valued user-defined function is used to translate incoming XML into a table of phone records. After the XML is translated from XML to a table, we can use the table to update the data within the database.

  2. The TOITEMLISTXML scalar-valued user-defined function accepts a unique identifier and retrieves the rows from the batch extension staging table as XML.

In addition, the Infinity platform will create two stored procedures to manage the adding and editing of the XML data to the batch extension staging table.

  1. The ADDFROMXML adds data into the batch extension staging table.

  2. The UPDATEFROMXML updates data into the batch extension staging table.

These database objects allow a consistent translation between a tabular representation of the results and an XML variable. Below we see an example of a SQLFunctionSpec for our phones data that contains the CreateChildCollectionFunction XML element.

<SQLFunctionSpec
	xmlns="bb_appfx_sqlfunction"
	xmlns:common="bb_appfx_commontypes"
	ID="20332741-2b72-4519-9e0e-1025418a1d0c"
	Name="USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL"
	Description="USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL Custom"
	Author="Technical Training"
	DBFunctionName="USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL">
  <CreateFunctionSQL>
    <![CDATA[
			create function dbo.USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL
			(
				@BATCHCONSTITUENTID uniqueidentifier
			)
			returns table
			as
			return
			(
				select
					ID,PHONETYPECODEID, NUMBER,
					ISPRIMARY, SEQUENCE,STARTTIME,
					ENDTIME,STARTDATE,
					COUNTRYID,
          				SEASONALSTARTDATE, 
          				SEASONALENDDATE,
         				BESTTIMETOCALLCODE
				from
					dbo.USR_BATCHCONSTITUENTPHONE
				where
					BATCHCONSTITUENTID = @BATCHCONSTITUENTID
			);
		] ]>
  </CreateFunctionSQL>
  <CreateChildCollectionFunction
  RootElementName="EXTPHONES"
  BaseTableName="USR_BATCHCONSTITUENTPHONE"
  ParentIDColumn="BATCHCONSTITUENTID"/>
</SQLFunctionSpec>

Looking at the CreateChildCollectionFunction XML element above, you must provide three attributes.

  • RootElementName provides the name to identify the root element of the XML document that represents the data from the collection field within the Batch Extension Spec.

  • BaseTableName is the base table from which the collection of data as defined by the collection form field element within the BatchTypeExtensionSpec.

  • ParentIDColumn is the parent ID column in the base table. In our case, the base table is USR_BATCHCONSTITUENTPHONE and it contains a parent ID of BATCHCONSTITUENTID that relates the table back to its parent table: BATCHCONSTITUENT.

When the spec is loaded, the following database objects are created:

The CreateChildCollectionFunction XML element creates additional database objects.
Nickname DB Object Type Example and Description
SQLFunctionSpec Table Valued User Defined Function The table valued user-defined function created from the SQLFunctionSpec. Accepts the unique identifier for the batch and retrieves the records from the batch extension table for the given unique identifier. For an example, see USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL above.
ADDFROMXML Stored Procedure USR_USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_ADDFROMXML adds data into the batch extension staging table. Accepts in the batch ID and the XML for the batch extension rows from the batch collection grid user interface, calls upon the FROMITEMLISTXML table valued function to translate the incoming XML into a temporary table, and finally uses an INSERT INTO statement to add the rows from the temporary table into the batch extension staging table.
UPDATEFROMXML Stored Procedure USR_USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_UPDATEFROMXML updates data into the batch extension staging table from incoming XML. Accepts in the batch ID and the XML for the batch extension rows from the batch collection grid user interface. Just like the ADDFROMXML stored procedure, a temporary table is built containing the values from the XML. Updating the rows is done in three phases. First, rows are deleted from the batch extension staging table that no longer exist in the XML table. Second, rows are updated within the batch extension staging table that exist within the XML table. Finally, any new items are inserted into the batch extension staging table.
TOITEMLISTXML Scalar Valued User Defined Function

USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_TOITEMLISTXML is a scalar-valued user-defined function that accepts a unique identifier and leverages the user-defined function defined by the SQLFunctionSpec to retrieve the records from the batch extension table as XML. The CreateChildCollectionFunction element's RootElementName attribute value is used within this user-defined function to structure the XML returned from the function.

CREATE FUNCTION [dbo].[USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_TOITEMLISTXML](
@BATCHCONSTITUENTID uniqueidentifier
	)
returns xml
as
BEGIN

RETURN (
SELECT [BESTTIMETOCALLCODE], 
[COUNTRYID], [ENDTIME], [ID], [ISPRIMARY], 
[NUMBER], [PHONETYPECODEID], [SEASONALENDDATE], 
[SEASONALSTARTDATE], [SEQUENCE], [STARTDATE], [STARTTIME]
 FROM dbo.[USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL](@BATCHCONSTITUENTID)
 for xml raw('ITEM'),type,elements,root('EXTPHONES'),BINARY BASE64)
END
FROMITEMLISTXML Table Valued User Defined Function

USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_FROMITEMLISTXML ADDFROMXML is a table-valued user-defined function used to translate incoming XML into a table of phone records. This function is used by the ADDFROMXML stored procedure to add the incoming rows into the batch extension table. The CreateChildCollectionFunction element's RootElementName attribute value is used within this user-defined function to correctly reference the XML nodes containing the incoming rows of data.

CREATE FUNCTION [dbo].[USR_UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_FROMITEMLISTXML](@ITEMLISTXML xml) RETURNS TABLE AS
RETURN (
SELECT
T.c.value('(BESTTIMETOCALLCODE)[1]','tinyint') AS 'BESTTIMETOCALLCODE',
T.c.value('(COUNTRYID)[1]','uniqueidentifier') AS 'COUNTRYID',
T.c.value('(ENDTIME)[1]','char(4)') AS 'ENDTIME',
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(ISPRIMARY)[1]','bit') AS 'ISPRIMARY',
T.c.value('(NUMBER)[1]','nvarchar(100)') AS 'NUMBER',
T.c.value('(PHONETYPECODEID)[1]','uniqueidentifier') AS 'PHONETYPECODEID',
T.c.value('(SEASONALENDDATE)[1]','char(4)') AS 'SEASONALENDDATE',
T.c.value('(SEASONALSTARTDATE)[1]','char(4)') AS 'SEASONALSTARTDATE',
T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
T.c.value('(STARTDATE)[1]','datetime') AS 'STARTDATE',
T.c.value('(STARTTIME)[1]','char(4)') AS 'STARTTIME'
FROM @ITEMLISTXML.nodes('/EXTPHONES/ITEM') T(c)
The CreateChildCollectionFunction element’s RootElementName attribute value also corresponds to the FieldID value of the form field holding the collection element within the BatchTypeExtensionSpec. 
<common:FormMetaData>
   <common:FormFields>
      <common:FormField FieldID="EXTPHONES" Caption="PhonesExt"   CaptionResourceKey="$$phones">
        <common:Collection SequenceFieldID="SEQUENCE">
          <common:Fields>
            <common:FormField FieldID="ID" Caption="ID" DataType="Guid" Hidden="true" />
            <common:FormField FieldID="PHONETYPECODEID" Caption="Phone type" DataType="Guid" CaptionResourceKey="$$phone_type">
              <common:CodeTable CodeTableName="PHONETYPECODE" />
            </common:FormField>
            <common:FormField FieldID="NUMBER"