Sales Order Delivery Contact Name garbage vale

[Riyaz]

Hi There

We are building a plugin to have custom search filter but what it does is that it fills up the Delivery tab Contact Name with weird values which I think is a concatenation of DB_Main.CreditLimit, DB_Main.CurrentBalance, (DB_Main.CreditLimit - DB_Main.CurrentBalance) , pls see the code below and the issue attached. Kindly advise

Code: Select all

Private Sub Search_Showing(sender As Object, e As System.EventArgs)
      'System.Diagnostics.Debugger.Launch
      Dim searchObject  As JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.clsSearch = sender
      
      With searchObject.Manager.Search 'JiwaApplication.Manager.Instance.Search
         If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswDebtors Then
            
            ' Add "Debtor and Financials" search to Inventory search options
            Dim AOption As New JiwaApplication.JiwaSearch.SearchOption
            With AOption
               .Title = "Debtor and Financials"
               .SQLStr = "SELECT DB_Main.DebtorID, DB_Main.AccountNo, DB_Main.AccountOnHold, DB_Main.Name, DB_Main.ProprietorsName, DB_Main.CreditLimit, DB_Main.CurrentBalance, (DB_Main.CreditLimit - DB_Main.CurrentBalance) AS Available, DB_Main.Phone " +
          "FROM DB_Main "
               .OrderBy = "ORDER BY Name"
                    .AddColumn ("DebtorID", vbString, "", 0, 1)
                 .AddColumn ("Account No.", vbstring, "", 10, 2)
               .AddColumn ("On Hold", vbString, "", 6, 3)
                 .AddColumn ("Name",  vbString, "", 20,4)
                 .AddColumn ("Proprietor", vbString, "", 20,5)
                 .AddColumn ("Credit Limit", vbDecimal, "", 7, 6)
               .AddColumn ("Current Bal", vbDecimal, "", 7, 7)
               .AddColumn ("Availble", vbDecimal, "", 7, 8)
               .AddColumn ("Phone",  vbString, "", 14,9)
            End With
               
            .Options.Add(AOption)                     
         End If
         
          Dim sm As JiwaApplication.JiwaSearch.clsSearch.searchModes = searchObject.Manager.Search.CurrentSearchMode
'******  This section added for testing only to include in sales orders -debtors lookup
         If .CurrentSearchMode = JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswSalesOrder Then
               
            ' Add "Debtor and Financials" search to Inventory search options
            Dim AOption As New JiwaApplication.JiwaSearch.SearchOption
            With AOption
               .Title = "Debtor and Financials"
               .SQLStr = "SELECT dbo.SO_Main.InvoiceID, dbo.SO_Main.InvoiceNo, dbo.DB_Main.AccountNo, dbo.DB_Main.AccountOnHold, dbo.DB_Main.Name, dbo.DB_Main.ProprietorsName, dbo.DB_Main.CreditLimit, dbo.DB_Main.CurrentBalance, dbo.DB_Main.CreditLimit - dbo.DB_Main.CurrentBalance As Available, dbo.DB_Main.Phone " +
                " FROM  dbo.DB_Main INNER JOIN dbo.SO_Main On dbo.DB_Main.DebtorID = dbo.SO_Main.DebtorID "
               .OrderBy = "ORDER BY Name"
                    .AddColumn ("InvoiceID", vbString, "", 0, 1)
               .AddColumn ("Invoice No.", vbstring, "", 10, 2)
                 .AddColumn ("Account No.", vbstring, "", 10, 3)
               .AddColumn ("On Hold", vbString, "", 6, 4)
                 .AddColumn ("Name",  vbString, "", 20,5)
                 .AddColumn ("Proprietor", vbString, "", 20,6)
                 .AddColumn ("Credit Limit", vbDecimal, "", 7, 7)
               .AddColumn ("Current Bal", vbDecimal, "", 7, 8)
               .AddColumn ("Availble", vbDecimal, "", 7, 9)
               .AddColumn ("Phone",  vbString, "", 14, 10)
            End With
               
            .Options.Add(AOption)                     
         End If   
         
         
         
         If .CurrentSearchMode = JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswUnknown And .FilterNo = 95 Then
            '   MessageBox.Show("1")
            ' Add "Debtor and Financials" search to Inventory search options
            Dim AOption As New JiwaApplication.JiwaSearch.SearchOption
            With AOption
               .Title = "Debtor and Financials"
               .SQLStr = "SELECT DB_Main.DebtorID, DB_Main.AccountNo, DB_Main.AccountOnHold, DB_Main.CreditLimit, DB_Main.CurrentBalance, (DB_Main.CreditLimit - DB_Main.CurrentBalance) AS Available, DB_Main.Name, DB_Main.ProprietorsName,DB_Main.Phone " +
                         "FROM DB_Main "
               .OrderBy = "ORDER BY Name"
                    .AddColumn ("DebtorID", vbString, "", 0, 1)
                 .AddColumn ("Account No.", vbstring, "", 10, 2)
               .AddColumn ("On Hold", vbString, "", 6, 3)
               .AddColumn ("Credit Limit", vbDecimal, "", 7, 6)
               .AddColumn ("Current Bal", vbDecimal, "", 7, 7)
               .AddColumn ("Availble", vbDecimal, "", 7, 8)
                 .AddColumn ("Name",  vbString, "", 20,4)
                 .AddColumn ("Proprietor", vbString, "", 20,5)                                
               .AddColumn ("Phone",  vbString, "", 14,9)
            End With
               
            .Options.Add(AOption)                     
         End If            
         
         
         
         
         
'******
      End With
   End Sub   

[SBarnes]

Your problem I am pretty sure is you have the wrong first column in your search, below is the default search SQL when the lookup is hit it has contact id first and you have Debtor ID, the lookup is looking for a contact not the debtor.

Code: Select all

SELECT CN_Contact.ContactID, CN_Contact.Title, CN_Contact.FName, CN_Contact.SName FROM CN_Contact JOIN CN_Main  ON (CN_Main.ProspectID = CN_Contact.ProspectID)  WHERE CN_Main.DebtorID = '000000000B0000000028'
ORDER BY CN_Contact.FName


[Riyaz]

Hi Stuart

Thanks for your reply, the filter search is for the new sales order popup, it works fine there, just not sure why it’s affecting this Delivery tab also

[SBarnes]

In that case I would suspect possibly the condition of your if statement are the same of both i.e. in both cases the mode is sales order, you could possibly add a check for the SQL on the search containing ContactId and do nothing if it does is the only advice I can give here without seeing the plugin in it's entirety I am guessing.

Or more importantly in the SetupBeforeHandlers of the sales form you made need to add a handler for the button click of DeliveryContactNameEditableLookup and remove the option that you added or clear the options because the form is using the search on the manager of the form as a whole and there it is finding your option.

Again here I am guessing without seeing the code.

[Riyaz]

Hi Stuart

I have attached the plugin here, I have commented out the MessageBox.Show("3") , this is what gets triggered for this and works fine for the debtor search in sales order. But why it would affect the Delivery tab on sales order is a puzzle.

[SBarnes]

Hi Riyaz

I suspect you have discovered a bug or more accurately a side effect, there is some code in the form shown below that fills in some values assuming that they can set contact as well because the search is the debtor with contact so why go back to the database for the data again, your option full fills the condition as well.

I would wait for Mike or Scott to confirm it but I am pretty sure I am correct, so changing you plugin is not going to fix it.

Code: Select all

                      if (search.ColumnCount > 5 & search.CurrentOption != 4)
                        {
                            this.NewTitle = Conversions.ToString(NewLateBinding.LateGet(search[6], null, "FieldValue", new object[0], null, null, null));
                            this.NewFirstName = Conversions.ToString(NewLateBinding.LateGet(search[7], null, "FieldValue", new object[0], null, null, null));
                            this.NewSurname = Conversions.ToString(NewLateBinding.LateGet(search[8], null, "FieldValue", new object[0], null, null, null));
                        }


[Scott.Pearce]

It's some legacy code that uses particular search fields to fill business logic properties. These days we just rely on the first column of the search being the ID.

The offending code look like this:

Code: Select all

 If .Show(Me) = Windows.Forms.DialogResult.OK Then
   If .Results.Count > 0 Then
      NewDebtorID = .Fields(1).FieldValue
      If .ColumnCount > 5 And .CurrentOption <> 4 Then
         NewTitle = .Fields(6).FieldValue
         NewFirstName = .Fields(7).FieldValue
         NewSurname = .Fields(8).FieldValue
      End If
   Else
      If NewRecordPending OrElse NoRecords Then
         Close()
      Else
         ' user has cancelled debtor selection, re-read the last invoice                           
         ReadRecord(JiwaApplication.IJiwaNavigable.ReadModes.Actual)
         Exit Sub
      End If
   End If
Else
   'Nothing has changed so don't need to re-read
   Throw New JiwaApplication.Exceptions.ClientCancelledException
End If


So you can see, if the search contains more than 5 columns, then it is assumed that column 6 is the contact title, column 7 is the contact first name, and column 8 is the contact surname. These values are then pushed through to the appropriate business logic property (SalesOrder.SalesOrderHistorys(SalesOrder.CurrentHistoryNo).DelContactName). Yuck!

You can deal with this in your plugin code though. Add 3 dummy fields to you search, i.e. in the SELECT list put '', '', '' in positions 6, 7, and 8. and then add some 0-width columns in your search, i.e.:

Code: Select all

If .CurrentSearchMode = JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswUnknown And .FilterNo = 95 Then
      'MessageBox.Show("3")
   ' Add "Debtor and Financials" search to Inventory search options
   Dim AOption As New JiwaApplication.JiwaSearch.SearchOption
   With AOption
      .Title = "Debtor and Financials"
      .SQLStr = "SELECT DB_Main.DebtorID, DB_Main.AccountNo, DB_Main.AccountOnHold, DB_Main.Name, DB_Main.ProprietorsName, '', '', '', DB_Main.CreditLimit, DB_Main.CurrentBalance, (DB_Main.CreditLimit - DB_Main.CurrentBalance) AS Available, DB_Main.Phone " +
              "FROM DB_Main "
      .OrderBy = "ORDER BY Name"
      .AddColumn ("DebtorID", vbString, "", 0, 1)
      .AddColumn ("Account No.", vbstring, "", 10, 2)
      .AddColumn ("On Hold", vbString, "", 6, 3)
      .AddColumn ("Name",  vbString, "", 20,4)
      .AddColumn ("Title",  vbString, "", 0, 6)
      .AddColumn ("FName",  vbString, "", 0, 7)
      .AddColumn ("SName",  vbString, "", 0, 8)               
      .AddColumn ("Proprietor", vbString, "", 20,9)
      .AddColumn ("Credit Limit", vbDecimal, "", 7, 10)
      .AddColumn ("Current Bal", vbDecimal, "", 7, 11)
      .AddColumn ("Availble", vbDecimal, "", 7, 12)
      .AddColumn ("Phone",  vbString, "", 14, 13)
   End With
      
   .Options.Add(AOption)                     
End If      


I've taken the liberty of modifying your plugin in this regard. Here:

Plugin Atronics - Search Debtors and show financials.xml

Modified

(36.79 KiB) Downloaded 687 times

[Riyaz]

Hi Scott

Perfect, that worked like a charm, thanks again