Cash Sale Search - Sales Order & Quotations  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Cash Sale Search - Sales Order & Quotations

Postby Luehman » Tue Mar 17, 2020 11:08 am

Just upgrading from JIWA6 to JIWA7 and come across a few items that we use in 6 that are no longer there.

Previously using "manage breakouts" we had a custom search installed so we were able to search using the Cash Sale Company name as below

SELECT SO_CashSale.Company, SO_Main.InvoiceID, SO_Main.InvoiceInitDate, SO_Main.InvoiceNo, DB_Main.AccountNo, DB_Main.Name
FROM SO_Main
JOIN DB_Main ON SO_Main.DebtorID = DB_Main.DebtorID
JOIN SO_CashSale ON SO_CashSale.InvoiceID = SO_Main.InvoiceID

ORDER BY SO_CashSale.Company DESC

Being a little bit new to all this I have modified the SQL usp_JIWA_SearchItem_SalesOrder to suit as below;
UNION
SELECT SO_Main.InvoiceID [SourceID], 'Sales Order' [SourceType], SO_Main.InvoiceNo [SourceDocumentNo], IN_Physical.Description [PhysicalWarehouse], IN_Logical.Description [LogicalWarehouse], 'Cash Sale Company' [MatchedOn], SO_CashSale.Company [MatchValue], CASE SO_Main.InvoiceType WHEN 0 THEN 'JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm' ELSE 'JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.BackToBackSalesOrderEntryForm' END [SY_Forms_ClassName]
FROM SO_Main
JOIN SO_CashSale ON SO_CashSale.InvoiceID = SO_Main.InvoiceID
JOIN IN_Logical ON IN_Logical.IN_LogicalID = SO_Main.IN_LogicalID
JOIN IN_Physical ON IN_Physical.IN_PhysicalID = IN_Logical.IN_PhysicalID
WHERE SO_CashSale.Company LIKE '%' + @SearchTerm + '%'

I had believed this would insert the new search line but was unsuccessful. How do i go about getting the search to appear in the search drop down for Sales Orders?
Currently not playing with live data as we are still live with JIWA6 but trying to get everything into JIWA7 prior to converting over.
Any help appreciated.
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 31
Joined: Tue Mar 17, 2020 9:48 am

Re: Cash Sale Search - Sales Order & Quotations

Postby Scott.Pearce » Tue Mar 17, 2020 11:41 am

Hi,

The usp_JIWA_SearchItem_xxx stuff is for the "Global Search". The global search is the little box that appears under the side menu in Jiwa 7. It allows you to search for something across all documents in Jiwa.

As you mentioned, what you actually want to achieve is to add a search option to the "normal" search for sales orders. In Jiwa 6 this was done by inserting VBScript code into the appropriate "breakout" point. In Jiwa 7 it is done by creating a plugin. I've attached a plugin that does what you want (import it via the "XML Import" button on the "Utilities" ribbon tab of the form System Settings->Plugins->Plugin Maintenance):

Plugin Custom Search - Cash Sale Company.xml
(27.2 KiB) Downloaded 63 times


Further, you should read this thread for more information about custom search plugins (a very common plugin requirement).

Good luck with the upgrade and remember you can always post in these forums to get help from other users of Jiwa, particularly when it comes to plugin / customisation work.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Cash Sale Search - Sales Order & Quotations

Postby Luehman » Tue Mar 17, 2020 12:21 pm

Thank you very much, i will install this now.

Will this appear in both quotes and sales orders?
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 31
Joined: Tue Mar 17, 2020 9:48 am

Re: Cash Sale Search - Sales Order & Quotations

Postby Scott.Pearce » Tue Mar 17, 2020 12:26 pm

Quote support added:

Plugin Custom Search - Cash Sale Company.xml
Quote support added
(28.34 KiB) Downloaded 69 times
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Cash Sale Search - Sales Order & Quotations

Postby Luehman » Tue Mar 17, 2020 1:05 pm

Fantastic, you are a life saver.

Should have asked first rather than play around with the SQL query for the hour and half.

Thank you, I will be sure to jump on the forum next time i have any issues.
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 31
Joined: Tue Mar 17, 2020 9:48 am

Re: Cash Sale Search - Sales Order & Quotations

Postby Scott.Pearce » Tue Mar 17, 2020 1:33 pm

Luehman wrote:Should have asked first rather than play around with the SQL query for the hour and half.


It's all a good learning exercise.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Cash Sale Search - Sales Order & Quotations  Topic is solved

Postby Scott.Pearce » Tue Mar 17, 2020 4:55 pm

Plugin updated to properly filter by warehouse:

Plugin Custom Search - Cash Sale Company.xml
Warehouse filtering added
(28.43 KiB) Downloaded 74 times
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221


Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 4 guests