Custom Search Query

[Atronics]

I have a plugin that returns data for a debtor lookup. I need the plugin to also work when a new sales order is created. It works for a New Credit Note but not new sales order. Here is the code in question

Private Sub Search_Showing(sender As Object, e As System.EventArgs)
With JiwaApplication.Manager.Instance.Search
If (.CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswDebtors Or .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswSalesOrder ) Then

' Add "Debtor and Financials" search to Debtor 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

[Scott.Pearce]

It's a bit icky, but I think this will work for you:

Code: Select all

With JiwaApplication.Manager.Instance.Search
  If (.CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswDebtors Or .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswSalesOrder) Or (.CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswUnknown And .FilterNo = 95) Then
    ' Add "Debtor and Financials" search to Debtor 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 If
End With


The only change above was the if statement.

I figured this out by msgbox-ing out the .CurrentSearchMode value for the search I was interested in. Alas, for a new sales order the CurrentSearchMode is -1 (which is jswUnknown in the Searchmodes enumeration). I needed to find something else to uniquely identify the search. This came in the form of the search .FilterNo property. Every search screen has it's own "filter no" assigned. This allows settings such as search screen size and position to be stored against individual search screens - for example, the debtor search when creating a credit note, whilst being a debtor search, has it's own settings stored against it as opposed to the debtor search screen that appears when searching for debtors in debtor maintenance. I can see what the .FilterNo is for a given search screen by clicking on the "User Settings" icon on the search screen of interest, and then noting down the "Search Filter No." that is displayed on the resultant "User Settings" dialog.

So, by combining .CurrentSearchMode of jswUnknown (-1) with a .FilterNo of 95, I have a way of identifying when the debtor search for new sales order creation is being shown.

[Atronics]

Thanks, Scott. All works OK. The filter in question was No 41. I like the concept.