V175 Dr Trans Search

[2can2]

Hi, I have created a stored proc to be able to search Drs Trans as well. The client specifically wanted to be able to look for amounts on payments that they can allocate to the correct Invoice.
The proc seems to be working in principle but there are 2 issues :
1. The Drill downs don't work. Gives 'Error:Debtor batch Transaction not found' I presumably need to pass the SOURCE parameter to the drill down, how can this be done. I have checked the other procs 'SO Lines' etc but they don't show the Drs selection.
2. The last select statement for 'Amount' gives an error converting numeric? 'Error:Error converting data type varchar to numeric'
I have added the convert option :
WHERE (CONVERT(varchar(MAX), DB_Trans.Amount) LIKE '%' + @SearchTerm + '%')
which runs fine in management studio BUT errors in Jiwa?
Any help would be appreciated. St proc and screenshots attached.

[Mike.Sheen]

Hi Doug,

1. This is caused by 2 things :

#1 - The Source for every row being returned as 'JiwaFinancials.Jiwa.JiwaDebtorInvoicesUI.MainForm' - that's the Debtor Invoices form, which is not the sales order entry form. DB_Trans already knows it's source form - DB_Trans.Source has this - so in your queries, change 'JiwaFinancials.Jiwa.JiwaDebtorInvoicesUI.MainForm' to DB_Trans.Source.
#2 - The SourceID being returned should be DB_Trans.SourceID, not DB_Trans.TransID.

2. This is caused by the DB_Trans.Amount column not being cast to VARCHAR in the last query when being returned as the [MatchedOn] column.

I changed your stored procedure to be as follows, and it now seems to work ok:

Code: Select all

ALTER PROC [dbo].[usp_JIWA_SearchItem_DebtorsTrans] @SearchTerm AS VARCHAR(max)
AS
SET NOCOUNT ON
SELECT DB_Trans.SourceID [SourceID], 'Debtor Trans' [SourceType], DB_Trans.InvRemitNo [SourceDocumentNo], '' [PhysicalWarehouse], '' [LogicalWarehouse], 'Inv/Remit No.' [MatchedOn], DB_Trans.InvRemitNo [MatchValue], DB_Trans.Source [SY_Forms_ClassName]
FROM  DB_Trans
WHERE DB_Trans.InvRemitNo LIKE '%' + @SearchTerm + '%'
UNION
SELECT DB_Trans.SourceID [SourceID], 'Debtor Trans' [SourceType], DB_Trans.InvRemitNo [SourceDocumentNo], '' [PhysicalWarehouse], '' [LogicalWarehouse], 'Reference' [MatchedOn], DB_Trans.Ref [MatchValue], DB_Trans.Source [SY_Forms_ClassName]
FROM   DB_Trans
WHERE DB_Trans.Ref LIKE '%' + @SearchTerm + '%'
UNION
SELECT DB_Trans.SourceID [SourceID], 'Debtor Trans' [SourceType], DB_Trans.InvRemitNo [SourceDocumentNo], '' [PhysicalWarehouse], '' [LogicalWarehouse], 'Amount' [MatchedOn], CONVERT(VARCHAR(MAX), DB_Trans.Amount) [MatchValue], DB_Trans.Source [SY_Forms_ClassName]
FROM   DB_Trans
WHERE     (CONVERT(varchar(max), DB_Trans.Amount) LIKE '%' + @SearchTerm + '%')
GO

[2can2]

Thanks Mike much appreciated!
I love this search feature.

[2can2]

Hi, Mike how can I get the actual Dr trans to display as source, they don't need to go to the original document, namely SO or Batch entry.

Thanks

[Mike.Sheen]

Hi, Mike how can I get the actual Dr trans to display as source, they don't need to go to the original document, namely SO or Batch entry.

Thanks

Do you want to drill to the debtor maintenance form for that debtor? If so, then change the [SourceID] to return the DebtorID, and change the [SY_Forms_ClassName] to be 'JiwaFinancials.Jiwa.JiwaDebtorsUI.frmDebtor', for example:

Code: Select all

SELECT DB_Trans.DebtorID [SourceID], 'Debtor Trans' [SourceType], DB_Trans.InvRemitNo [SourceDocumentNo], '' [PhysicalWarehouse], '' [LogicalWarehouse], 'Inv/Remit No.' [MatchedOn], DB_Trans.InvRemitNo [MatchValue], 'JiwaFinancials.Jiwa.JiwaDebtorsUI.frmDebtor' [SY_Forms_ClassName]
FROM  DB_Trans
WHERE DB_Trans.InvRemitNo LIKE '%' + @SearchTerm + '%'

Mike

[2can2]

Hi, Thanks Mike. That returns to the Debtor main form fine, is there anyway to get it to go to the Transactions tab? I can't see any ClassName that would achieve that?
Cheers

[Mike.Sheen]

That returns to the Debtor main form fine, is there anyway to get it to go to the Transactions tab? I can't see any ClassName that would achieve that?

There won't be a ClassName because they're the class names of the forms in Jiwa. The transaction tab is not a form, just part of the debtor maintenance form.

A plugin could, however replace the standard search form, intercept the drill down, and provided the stored proc is further modified to supply the DB_Trans.TransID instead of DB_Main.DebtorID, figure out the debtorID from the DB_Trans.TransID and then load the debtor maintenance form, select the transactions tab and highlight the transaction for the user.

It's not really what the search was intended for - it was designed to drill to the source document - so that's why we'd have to build via plugin an alternate search form. But, if your customer is willing to pay for a few hours of work to do this, then it can be done.

If it Is it worth $600 to $800 to the customer, then we can do it for you.

Mike

[2can2]

Hi, Thanks for the informative answer. I guessed the search probably wasn't designed to drill to the Trans tab, however it will still work for them just an extra step.
I will put the custom option to them but I don't think they would take it up.
I still think the stored procedure ability in the searches is brilliant, as it is in several other places.Thanks again.