Custom fields - celltype lookup

[DannyC]

Hi,

Can you provide an example to create a new custom field with a celltype of lookup?

In my specific requirement, I am wanting a debtor custom field which looks up the Branch (sales division) search window.

In your answer, can you also give a list of what different lookups are available? Back in the old days, I got this list several years ago (I note it doesn't include the branch!)
jswInventory = 1
jswDebtors = 2
jswSalesOrder = 3
jswTransfer = 4
jswGeneralLedger = 5
jswLedgerJournals = 6
jswDebtorbatchtrans = 7
jswDebtorAutoTrans = 8
jswCreditorBatchTrans = 9
jswCreditorAutoTrans = 10
jswCreditor = 11
jswCostChange = 12
jswPurchaseOrder = 13
jswDeliveryDocket = 14
jswPurchaseInvoice = 15
jswAssembly = 16
jswCashbook = 17
jswLedger = 30
jswStaff = 31
jswgroup = 32
jswContactPostion = 33
jswCredPayments = 34
jswCreditorClassification = 35
jswFXRate = 36
jswTaxRate = 37
jswWarehouseTransfer = 38
jswGLAutoJournals = 39
jswGLCategory = 40
jswBackOrderAlloc = 41
jswInventoryClassification = 42
jswDebtorClassification = 43
jswInventoryCatagory = 44
jswDuty = 45
jswBinLocation = 46
jswFreight = 47
jswQuotes = 48
jswSupplierWarehouse = 49
jswPhysicalWarehouse = 50
jswContacts = 51
jswShipments = 52
jswShipmentBookIns = 53
jswSystemPeriodRollOvers = 54
jswStockTake = 55
jswBankReconciliation = 56
jswStandardCost = 57
jswLogicalWarehouse = 58
jswMenus = 59
jswIcons = 60
jswProfiles = 61
jswJobCostCode = 62
jswPriceSchemes = 63
jswQuoteBatches = 64
jswB2BSalesOrder = 65
jswInventoryCatagory1 = 66
jswInventoryCatagory2 = 67
jswInventoryCatagory3 = 68
jswInventoryCatagory4 = 69
jswInventoryCatagory5 = 70
jswSkillLevels = 71
jswSkills = 72
jswDepartments = 73
jswWarehouseTransferOut = 74
jswWarehouseTransferin = 75
jswWarrantyItem = 76
jswMaintenanceAgreement = 77
jswServiceItem = 78
jswJobMaintenance = 79
jswSupplierReturnCredit = 80
jswSupplierReturnShipping = 81
jswSupplierReturnRequest = 82
jswManufacturingRequisitions = 83
jswBillOfMaterials = 84
jswServiceManager = 85
jswPaymentTypes = 86
jswDebtorSystemProperties = 87
jswDebtorCategory1 = 88
jswDebtorCategory2 = 89
jswDebtorCategory3 = 90
jswDebtorCategory4 = 91
jswDebtorCategory5 = 92
jswDebtorSystemTemplates = 93
jswDebtorSystems = 94
jswSupportPacks = 95
jswEmailMessages = 96

[Mike.Sheen]

Can you provide an example to create a new custom field with a celltype of lookup?
In my specific requirement, I am wanting a debtor custom field which looks up the Branch (sales division) search window.

A plugin demonstrating this is attached.

In your answer, can you also give a list of what different lookups are available? Back in the old days, I got this list several years ago (I note it doesn't include the branch!)
jswInventory = 1
...

Yes, I can provide that list, but if you look at the plugin you'll note I use the branch.Search method rather than using the SearchModes enumeration. There is no search for the branch in the SearchModes, but you'll see in the plugin I put some code which is commented out on how to do a search manually - in fact that code is a copy and paste from the branch.search method I used.

Anyway - the SearchModes enumeration you asked for is as follows:

Code: Select all

    Public Enum SearchModes
            jswUnknown = -1
            jswInventory = 1
            jswDebtors = 2
            jswSalesOrder = 3
            jswStockTransfer = 4
            jswGeneralLedger = 5
            jswLedgerJournals = 6
            jswDebtorBatchTrans = 7
            jswDebtorAutoTrans = 8
            jswCreditorBatchTrans = 9
            jswCreditorAutoTrans = 10
            jswCreditor = 11
            jswCostPriceUpdates = 12
            jswPurchaseOrder = 13
            jswInventoryReceivals = 14
            jswPurchaseInvoice = 15
            jswAssembly = 16
            jswCashbook = 17
            jswPlugins = 18
            jswStaff = 31
            jswGroup = 32
            jswContactPostion = 33
            jswCreditorPayments = 34
            jswCreditorClassification = 35
            jswFXRate = 36
            jswTaxRate = 37
            jswGLAutoJournals = 39
            jswGLCategory = 40
            jswBackOrderAlloc = 41
            jswInventoryClassification = 42
            jswDebtorClassification = 43
            jswInventoryCatagory = 44
            jswDuty = 45
            jswBinLocation = 46
            jswFreight = 47
            jswQuotes = 48
            jswSupplierWarehouse = 49
            jswPhysicalWarehouse = 50
            jswContacts = 51
            jswShipments = 52
            jswShipmentBookIns = 53
            jswSystemPeriodRollOvers = 54
            jswStockTake = 55
            jswBankReconciliation = 56
            jswStandardCost = 57
            jswLogicalWarehouse = 58
            jswMenus = 59
            jswIcons = 60
            jswProfiles = 61
            jswJobCostCode = 62
            jswPriceSchemes = 63
            jswQuoteBatches = 64
            jswB2BSalesOrder = 65
            jswInventoryCatagory1 = 66
            jswInventoryCatagory2 = 67
            jswInventoryCatagory3 = 68
            jswInventoryCatagory4 = 69
            jswInventoryCatagory5 = 70
            jswSkillRatings = 71
            jswSkills = 72
            jswDepartments = 73
            jswWarehouseTransferOut = 74
            jswWarehouseTransferIn = 75
            jswJobMaintenance = 79
            jswSupplierReturnCredit = 80
            jswSupplierReturnShipping = 81
            jswSupplierReturnRequest = 82
            jswWorkOrder = 83
            jswBillMaintenance = 84
            jswServiceManager = 85
            jswPaymentTypes = 86
            jswDebtorSystemProperties = 87
            jswDebtorCategory1 = 88
            jswDebtorCategory2 = 89
            jswDebtorCategory3 = 90
            jswDebtorCategory4 = 91
            jswDebtorCategory5 = 92
            jswDebtorSystemTemplates = 93
            jswDebtorSystems = 94
            jswPrepaidLabourPacks = 95
            jswEmailMessages = 96
            jswOrderLocations = 97
            jswWorkflows = 98
            jswTasks = 99
            jswTodos = 100
            jswCreditorAllocationBatch = 101
            jswRoles = 102
            jswScheduledScripts = 103
            jswRegions = 104
            jswSOBatchPrintSettings = 105
            jswEmailTemplates = 106
            jswSerialNumbers = 107
            jswDebtorPricingGroups = 108
            jswInventoryPricingGroups = 109
            jswCarriers = 110
            jswInTransitLogicalWarehouses = 111
            jswPurchasingClassifications = 112
            jswJobCostingStages = 113
            jswServiceManagerTasks = 114
            jswSalesOrdersBackToBack = 115
            jswSalesOrdersMultiBackToBack = 116
            jswGeneralLedgerTransactionCode1 = 117
            jswGeneralLedgerTransactionCode2 = 118
            jswIncidents = 119
            jswDebtorContacts = 120
            jswInvoiceLines = 121
            jswJobCostingJobs = 122
            jswJobCostingTemplates = 123
            jswJobCostingEstimates = 124
            jswJobCostingFinishedGoods = 125
            jswJobCostingCostEntry = 126
            jswArchivingProviders = 127
            jswArchivingJobs = 128
            jswCarrierMaintenance = 129
            jswJobCostingTimesheet = 130
            jswDepartmentCategories = 131
            jswServiceManagerPriorities = 132
            jswServiceManagerStatuses = 133
            jswDebtorDeliveryAddresses = 134
            jswUserGroups = 135
            jswStockTransferReasons = 136
            jswStockOnHand = 137
            jswLabour = 138
            jswStoredProcedures = 139
            jswSalesOrderRuns = 140
            jswDebtorInvoices = 141
            jswDebtorAdjustments = 142
            jswToDoTypes = 143
            jswToDoStatuses = 144
            jswToDoPriorities = 145
            jswBillOfMaterialsWorkCentres = 146
            jswBillOfMaterialsProductionLines = 147
            jswBillOfMaterialsProductionAreas = 148
            jswBillOfMaterialsProductionLineWorkCentres = 149
            jswIncidentTypes = 150
            jswPurchaseOrderGenerationBatches = 151
            jswLanguages = 152
            jswManualBackOrderReleaseBatch = 153
            jswBillOfMaterialsWarehouseWorkCentres = 154
            jswBillOfMaterialsWarehouseProductionLineWorkCentres = 155
            jswBillOfMaterialsWarehouseProductionLines = 156
            jswBillOfMaterialsWarehouseProductionAreas = 157
            jswReportSections = 158
            jswSalesOrderLineDeliveryProcessingBatch = 159
            jswBillsInProductionLineAndWarehouse = 160
            jswForms = 161
            jswReports = 162
            jswLogicalPrinters = 163
            jswJobCostingStaff = 164
            jswJobCostingResources = 165
            jswShifts = 166
            jswFXTransactionRates = 167
            jswSupplierReturnCodes = 168
            jswFXForwardCoverContracts = 169
            jswLandedCostCostType = 170
            jswPrintGroup = 171
            jswSale = 172
            jswSalesOrderExportBatch = 173
            jswForwardOrderSchedulingBatch = 174
            jswForwardOrderProcessingBatch = 175
            jswCreditorChequePaymentsEmailTemplates = 176
            jswBankAccount = 177
            jswJobCostingInvoices = 178
        End Enum


[DannyC]

That's awesome, thanks for the fast reply.

Two questions:
1. If I have multiple custom fields which are lookups, how does the plugin know which one I am on? Would I have a different plugin for each different lookup?
2. The example works a treat in JiwaDemo but I think that might be because this is a new custom field and therefore has no data yet in DB_CustomSettingValues.
If I already have data in my database from 6.5.13,
i) contents is not displayed
ii) When I click the button to display the search window, it doesn't.


Thanks

[Mike.Sheen]

1. If I have multiple custom fields which are lookups, how does the plugin know which one I am on? Would I have a different plugin for each different lookup?

No need for separate plugins - in the ReadData and ButtonClick methods, just examine the custom field name and branch your code based on that - eg:

Code: Select all

Public Sub ReadData(ByVal BusinessLogicHost As JiwaApplication.IJiwaBusinessLogic, ByVal GridObject As JiwaApplication.Controls.JiwaGrid, ByVal FormObject As JiwaApplication.IJiwaForm, ByVal Row As Integer, ByVal HostObject As JiwaApplication.IJiwaCustomFieldValues, ByVal CustomField As JiwaApplication.CustomFields.CustomField, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaCustomFieldPlugin.ReadData
      If CustomField.PluginCustomField.Name = "Branch" Then
         ' The Contents of the custom field is the Branch ID.  We need to read the branch description and put that into the display contents.
         Dim branch As New JiwaApplication.Entities.Sales.Branch
         
         If CustomFieldValue.Contents.Trim.Length > 0 Then
            branch.ReadRecord(CustomFieldValue.Contents.Trim)
            CustomFieldValue.DisplayContents = branch.Description
         End If      
      ElseIf CustomField.PluginCustomField.Name = "SomethingElse" Then
      End If      
    End Sub

    Public Sub ButtonClicked(ByVal BusinessLogicHost As JiwaApplication.IJiwaBusinessLogic, ByVal GridObject As JiwaApplication.Controls.JiwaGrid, ByVal FormObject As JiwaApplication.IJiwaForm, ByVal Col As Integer, ByVal Row As Integer, ByVal HostObject As JiwaApplication.IJiwaCustomFieldValues, ByVal CustomField As JiwaApplication.CustomFields.CustomField, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaCustomFieldPlugin.ButtonClicked
      If CustomField.PluginCustomField.Name = "Branch" Then
         ' Search Branch
         Dim branch As New JiwaApplication.Entities.Sales.Branch
         branch.Search(FormObject.Form, "")
         CustomFieldValue.Contents = branch.BranchID       
         CustomFieldValue.DisplayContents = branch.Description
      ElseIf CustomField.PluginCustomField.Name = "SomethingElse" Then
      End If
    End Sub


2. The example works a treat in JiwaDemo but I think that might be because this is a new custom field and therefore has no data yet in DB_CustomSettingValues.
If I already have data in my database from 6.5.13,
i) contents is not displayed

You might need to cleanse your data - if you have any custom field values which don't link to a branch then you can either fix the data to point to a default, remove the invalid data or simply catch the ReadData failing on RecordNotFound exception and do nothing.

ii) When I click the button to display the search window, it doesn't.

This is highly likely because the ReadData threw an exception before the custom field controller had a chance to unlock / lock grid cells appropriately - fixing the ReadData or swallowing all exceptions in ReadData will fix this.

I've attached a revised plugin which incorporates all discussed above.

Plugin Debtor Custom Field Lookup Sample.xml

Revised Plugin

(36.19 KiB) Downloaded 128 times

Mike

[DannyC]

Legend.
I'll review the SQL data and see what manipulation/cleansing I can do to retain the current values and also allow the button to work.

Thx again

[Mike.Sheen]

I'll review the SQL data and see what manipulation/cleansing I can do to retain the current values and also allow the button to work.

The revised plugin I attached to my previous post does handle the existing data pointing to invalid branches - I put a Try Catch around the branch.ReadRecord and if the exception was of type RecordNotFoundException it simply ignores it - resulting in the invalid data being ignored, but also the button now working despite the erroneous data.

Mike

[DannyC]

Next question...

Different scenario but essentially question is suited to this thread. This time I am doing a lookup of debtor classifications. The list of classes are:
A123
A124
A125
A126
B123
B124
B125
B126
B127
C123
C124
C125

I want the custom lookup to just get the "A" classifications, or the "B"s, or the "C"s.
Where can I add in the search clause WHERE LEFT(Description,1) = "A" ? Or something similar.

I thought it might be in the line (using the example above of branch lookup)

Code: Select all

branch.Search(FormObject.Form, "")

but not sure if that is correct for debtor classification. The 2nd parameter I needed to remove.


cheers

Danny

[Mike.Sheen]

I want the custom lookup to just get the "A" classifications, or the "B"s, or the "C"s.
Where can I add in the search clause WHERE LEFT(Description,1) = "A" ? Or something similar.

Some of our entity searches have an optional parameter for a Filter to be applied - but this is not the case with Debtor Classifications. Not to worry though - you just need to create your own debtor classification entity, inherit from our debtor classification entity and overload the search method.

E.G.:

Code: Select all

Public Class MyDebtorClassification
   Inherits JiwaApplication.Entities.Debtor.Classification
   
   Public Overloads Sub Search(ByVal OwnerForm As System.Windows.Forms.Form, ByVal FilterSQL As String)
            With JiwaApplication.Manager.Instance.Search
                .Clear()
                .Caption = "Classification"
                .FilterNo = 100
                .SetDefaultSearch(JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswDebtorClassification)
            
            If FilterSQL.Trim.Length > 0 Then
                    For Each searchOption As JiwaApplication.JiwaSearch.SearchOption In .Options
                        If Not UCase(searchOption.SQLStr).Contains("WHERE") Then
                            searchOption.SQLStr += " WHERE "
                        Else
                            searchOption.SQLStr += " AND "
                        End If

                        searchOption.SQLStr += FilterSQL
                    Next
                End If

                If .Show(OwnerForm) = DialogResult.OK Then
                    If .Results.Count > 0 Then
                        ReadRecord(.Fields(1).FieldValue)
                    Else
                        Throw New JiwaApplication.Exceptions.ClientCancelledException
                    End If
                End If
            End With
        End Sub
End Class

Attached is a revised plugin which demonstrates this - it adds to the debtor maintenance form another custom field "Debtor Classification", and when the lookup/search button is pressed it filters the classifications to only show those where the description starts with the letter 'A'.

Plugin Debtor Custom Field Lookup Sample.xml

Revised Plugin

(34.2 KiB) Downloaded 117 times

[DannyC]

Hi,
I am doing a variation on this for version 7.1
Simple scenario is that in Sales Orders custom fields I want a lookup to Debtor Classifications.
And as a new order is created I want to get that debtors classification and put it in the custom field - but I think that should be easy. It's the lookup I have having trouble with.

Using the previous code doesn't work for me - as soon as you try to search via the arrow, it immediately gives an error Object Reference not set to an instance of an object.

My code:

Code: Select all

Public Class CustomFieldPlugin
    Inherits System.MarshalByRefObject
    Implements JiwaApplication.IJiwaCustomFieldPlugin

    Public Overrides Function InitializeLifetimeService() As Object
        ' returning null here will prevent the lease manager
        ' from deleting the Object.
        Return Nothing
    End Function

    Public Sub FormatCell(ByVal BusinessLogicHost As JiwaApplication.IJiwaBusinessLogic, ByVal GridObject As JiwaApplication.Controls.JiwaGrid, ByVal FormObject As JiwaApplication.IJiwaForm, ByVal Col As Integer, ByVal Row As Integer, ByVal HostObject As JiwaApplication.IJiwaCustomFieldValues, ByVal CustomField As JiwaApplication.CustomFields.CustomField, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaCustomFieldPlugin.FormatCell
    End Sub

    Public Sub ReadData(ByVal BusinessLogicHost As JiwaApplication.IJiwaBusinessLogic, ByVal GridObject As JiwaApplication.Controls.JiwaGrid, ByVal FormObject As JiwaApplication.IJiwaForm, ByVal Row As Integer, ByVal HostObject As JiwaApplication.IJiwaCustomFieldValues, ByVal CustomField As JiwaApplication.CustomFields.CustomField, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaCustomFieldPlugin.ReadData
      If CustomField.PluginCustomField.Name = "DBClass" Then
         Dim DBClass As New JiwaApplication.Entities.Debtor.Classification   
         msgbox(CustomFieldValue.Contents.Trim)
         If CustomFieldValue.Contents.Trim.Length > 0 Then
            Try
               DBClass.ReadRecord(CustomFieldValue.Contents.Trim)
               CustomFieldValue.DisplayContents = DBClass.Description
            Catch ex As JiwaApplication.Exceptions.RecordNotFoundException
               ' We do nothing here because a bad classification should not result in the custom field grid not getting populated correctly.
            End Try
         End If      
      End If   
   End Sub

    Public Sub ButtonClicked(ByVal BusinessLogicHost As JiwaApplication.IJiwaBusinessLogic, ByVal GridObject As JiwaApplication.Controls.JiwaGrid, ByVal FormObject As JiwaApplication.IJiwaForm, ByVal Col As Integer, ByVal Row As Integer, ByVal HostObject As JiwaApplication.IJiwaCustomFieldValues, ByVal CustomField As JiwaApplication.CustomFields.CustomField, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaCustomFieldPlugin.ButtonClicked
      If CustomField.PluginCustomField.Name = "DBClass" Then
         Dim DBClass As New JiwaApplication.Entities.Debtor.Classification
         DBClass.Search(FormObject.Form, "")
         CustomFieldValue.Contents = DBClass.RecID      
         CustomFieldValue.DisplayContents = DBClass.Description
      End If
   End Sub

End Class

[Scott.Pearce]

Try changing:

Code: Select all

Dim DBClass As New JiwaApplication.Entities.Debtor.Classification

to:

Code: Select all

Dim DBClass As JiwaApplication.Entities.Debtor.Classification = BusinessLogicHost.Manager.EntityFactory.CreateEntity(Of JiwaApplication.Entities.Debtor.Classification)()

You should always use our factories to create objects from 7.1 onwards.