Custom Fields on Lines

[Mike.Sheen]

In this topic, I'm going to try to explain how we put custom fields on grids. For this example, I'll show how we put custom fields on the Bill Maintenance stage - these are custom fields on the stages grid of the Bill Maintenance form.

First we need to create a table to define the custom fields. All custom field tables should have the same structure - just the table, constraint and index names are different. Column names, types, length, etc should be as follows:

Code: Select all

CREATE TABLE [dbo].[BM_StageCustomFields](
   [RecID] [char](36) NOT NULL,
   [SettingName] [varchar](255) NOT NULL,
   [SettingDescription] [varchar](255) NULL,
   [CellType] [int] NULL,
   [FieldParameter] [varchar](255) NULL,
   [SY_Plugin_RecID] [uniqueidentifier] NOT NULL,
   [LastSavedDateTime] [datetime] NOT NULL,
   [DisplayOrder] [int] NULL,
CONSTRAINT [PK_BM_StageCustomFields] PRIMARY KEY CLUSTERED ([RecID] ASC)
)

ALTER TABLE [dbo].[BM_StageCustomFields]  WITH CHECK ADD  CONSTRAINT [FK_BM_StageCustomFields_SY_Plugin_RecID] FOREIGN KEY([SY_Plugin_RecID])
REFERENCES [dbo].[SY_Plugin] ([RecID])
ON DELETE CASCADE

CREATE UNIQUE NONCLUSTERED INDEX [IX_BM_StageCustomFields_SettingName] ON [dbo].[BM_StageCustomFields] ([SettingName] ASC)

We don't need to ever worry about inserting into this table - the plugin maintenance form handles that when you add custom fields on the custom fields tab of the form.

Next we need to create a table to store the values associated with each line (stage):

Code: Select all

CREATE TABLE [dbo].[BM_StageCustomValues](
   [RecID] [char](36) NOT NULL,
   [BM_Stages_RecID] [char](36) NOT NULL,
   [BM_StageCustomFields_RecID] [char](36) NOT NULL,
   [Contents] [varchar](max) NULL,
   [LastSavedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_BM_StageCustomValues_RecID] PRIMARY KEY CLUSTERED ([RecID] ASC)
)

ALTER TABLE [dbo].[BM_StageCustomValues]  WITH CHECK ADD  CONSTRAINT [FK_BM_StageCustomValues_BM_StageCustomFields_RecID] FOREIGN KEY([BM_StageCustomFields_RecID])
REFERENCES [dbo].[BM_StageCustomFields] ([RecID])
ON DELETE CASCADE

ALTER TABLE [dbo].[BM_StageCustomValues]  WITH CHECK ADD  CONSTRAINT [FK_BM_StageCustomValues_BM_Stages_RecID] FOREIGN KEY([BM_Stages_RecID])
REFERENCES [dbo].[BM_Stages] ([RecID])
ON DELETE CASCADE

CREATE NONCLUSTERED INDEX [IX_BM_StageCustomValues_BM_StageCustomFields_RecID] ON [dbo].[BM_StageCustomValues] ([BM_StageCustomFields_RecID] ASC)

CREATE NONCLUSTERED INDEX [IX_BM_StageCustomValues_BM_Stages_RecID] ON [dbo].[BM_StageCustomValues] ([BM_Stages_RecID] ASC)

CREATE NONCLUSTERED INDEX [IX_BM_StageCustomValues_RecID] ON [dbo].[BM_StageCustomValues] ([RecID] ASC)

Next, an entry is needed in the SY_PluginCustomSettingModules table to provide information about the tables and columns to store the custom field data. It's the entries from this table which we use in the plugin maintenance form to populate the modules grid on the custom fields tab.

Column Name	Description
RecID	UniqueID
ModuleName	Unique name
TableName	Table holding the custom field definitions
IDColumnName	The name of the column which is the ID column in the above table
ItemNo	Item No. (used for ordering)
RowHash	Row version timestamp (automatically set by SQL)
ValuesTableName	The table name to store the values
ValuesIDColumnName	The ID column name in the above table
ValuesSettingIDColumnName	The column name in the values table which links to the custom field definition
ValuesFKIDColumnName	The column name in the values table which links to the Bill stage

The entry in this table for the Bill Maintenance stages looks like this:

RecID	ModuleName	TableName	IDColumnName	ItemNo	RowHash	ValuesTableName	ValuesIDColumnName	ValuesSettingIDColumnName	ValuesFKIDColumnName
C63F0D90-B1E7-4E73-B601-D61FAB2FFA35	Work Order Stages	BM_WorkOrderStageCustomFields	RecID	25	0x0000000000004177	BM_StageCustomValues	RecID	BM_StageCustomFields_RecID	BM_Stages_RecID

Now that we have the data structure done, the business logic needs to have some changes made to be custom field aware. There are 3 classes affected - the Bill Maintenance class, the Stage Class and the StageCollection class.

In the Bill Maintenance class, we add a field to store the Custom fields for the stage:

Code: Select all

Private WithEvents m_StageCustomFields As JiwaApplication.CustomFields.CustomFieldCollection

And add a public read-only property:

Code: Select all

Public ReadOnly Property StageCustomFields() As JiwaApplication.CustomFields.CustomFieldCollection
   Get
      Return m_StageCustomFields
   End Get
End Property

In the constuctor (New) of the Bill Maintenance class, we initialise the stage custom field collection defined above:

Code: Select all

m_StageCustomFields = New JiwaApplication.CustomFields.CustomFieldCollection(JiwaApplication.Manager.Instance.PluginCollection.CustomFieldModuleCollection.GetItem("ModuleName", "Bill Stages"), Me)

Note the "Bill Stages" we pass matches the "Bill Stages" in the SY_PluginCustomSettingModules table entry.

In the Stage class, we need to implement IJiwaLineCustomFieldValues:

Code: Select all

Public Class Stage
   Inherits JiwaFinancials.Jiwa.JiwaApplication.JiwaCollectionItem(Of Stage)
   Implements JiwaApplication.IJiwaLineCustomFieldValues

Public Event CustomFieldValueChanged(HostItem As JiwaApplication.IJiwaCustomFieldValues, item As JiwaApplication.CustomFields.CustomFieldValue, e As System.ComponentModel.PropertyChangedEventArgs) Implements JiwaApplication.IJiwaLineCustomFieldValues.CustomFieldValueChanged

' This is where we store the custom field values for the stage
Private WithEvents _CustomFieldValues As JiwaApplication.CustomFields.CustomFieldValueCollection

Public Property CustomFieldValues As JiwaApplication.CustomFields.CustomFieldValueCollection Implements JiwaApplication.IJiwaLineCustomFieldValues.CustomFieldValues
   Get
      Return _CustomFieldValues
   End Get
   Friend Set(value As JiwaApplication.CustomFields.CustomFieldValueCollection)
      _CustomFieldValues = value
   End Set
End Property

Public ReadOnly Property CustomFieldValuesHostCollection As JiwaApplication.IJiwaLineCustomFieldValuesHostCollection Implements JiwaApplication.IJiwaLineCustomFieldValues.CustomFieldValuesHostCollection
   Get
      Return Stages
   End Get
End Property

Public ReadOnly Property CanEditCustomFieldValues As Boolean Implements JiwaApplication.IJiwaLineCustomFieldValues.CanEditCustomFieldValues
   Get
      Return True
   End Get
End Property

In the SaveRecord method of the Stage, we need to tell the customfieldvalues to save:

Code: Select all

Public Overrides Sub SaveRecord()
    ...
   CustomFieldValues.Save(RecID)
End Sub

We need to handle the change event of the custom field values, so we set our change flag and also cause the collection to raise a change event

Code: Select all

Private Sub _CustomFieldValues_Changed(item As JiwaApplication.CustomFields.CustomFieldValue, e As ComponentModel.PropertyChangedEventArgs) Handles _CustomFieldValues.Changed
   If Not Stages Is Nothing AndAlso Stages.BillMaintenance.IsReading = False Then
      ChangeFlag = True
      Stages.RaiseCustomFieldValueChangedEvent(Me, item)
   End If
End Sub

In the Stage Collection class we need to implement IJiwaLineCustomFieldValuesHostCollection:

Code: Select all

Public Class StageCollection
    Inherits JiwaApplication.JiwaCollection(Of Stage)
    Implements JiwaApplication.IJiwaLineCustomFieldValuesHostCollection

    Public Event CustomFieldValueChanged(Item As JiwaApplication.IJiwaLineCustomFieldValues, CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaLineCustomFieldValuesHostCollection.CustomFieldValueChanged
    Public Event HostItemAdded(Item As JiwaApplication.IJiwaLineCustomFieldValues) Implements JiwaApplication.IJiwaLineCustomFieldValuesHostCollection.HostItemAdded

   Public Overloads ReadOnly Property Collection As System.Collections.IEnumerable Implements JiwaApplication.IJiwaLineCustomFieldValuesHostCollection.Collection
      Get
         Return Me
      End Get
   End Property

   Public ReadOnly Property CollectionItem(ByVal Key As String) As JiwaApplication.IJiwaLineCustomFieldValues Implements JiwaApplication.IJiwaLineCustomFieldValuesHostCollection.CollectionItem
      Get
         Return Me(Key)
      End Get
   End Property

We need to add the RaiseCustomFieldValueChangedEvent sub that we call from the stage when a custom value changes

Code: Select all

Public Sub RaiseCustomFieldValueChangedEvent(ByVal Item As Stage, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue)
   RaiseEvent CustomFieldValueChanged(Item, CustomFieldValue)
End Sub

We also need to override the OnAdding of the collection to create a custom value whenever a new stage is added

Code: Select all

Protected Overrides Sub OnAdding(item As Stage)
   If item.CustomFieldValues Is Nothing Then
      item.CustomFieldValues = New JiwaApplication.CustomFields.CustomFieldValueCollection(BillMaintenance.StageCustomFields)

      ' Add custom field values
      If Not Reading Then
         For Each customField As JiwaApplication.CustomFields.CustomField In BillMaintenance.StageCustomFields
            Dim customFieldValue As New JiwaApplication.CustomFields.CustomFieldValue
            customFieldValue.CustomField = customField
            item.CustomFieldValues.Add(customFieldValue)
         Next
      End If
   End If

   MyBase.OnAdding(item)
End Sub

In the Read method, we need to read the custom field values

Code: Select all

stage.CustomFieldValues = New JiwaApplication.CustomFields.CustomFieldValueCollection(BillMaintenance.StageCustomFields)
                            stage.CustomFieldValues.Read(stage.RecID)

In the form we need to have a CustomLineFieldController:

Code: Select all

Private _StageCustomFieldsController As JiwaApplication.CustomFields.CustomLineFieldController

In the form setup, we need to initialise the controller:

Code: Select all

_StageCustomFieldsController = New JiwaApplication.CustomFields.CustomLineFieldController(Me, Me, StagesJiwaGrid, BillMaintenance, BillMaintenance.StageCustomFields, BillMaintenance.Stages, "RecID")

And in the handler for the business logic Read, CreateEnd and CopyEnd - tell the controller to display the custom field values

Code: Select all

_StageCustomFieldsController.DisplayCustomSettingValues()