SBX - Search With Button

SBX - Forum Post Title

Different Results from Quick Filter and Added filter in NAV 2017 CU11

Microsoft Dynamics NAV Forum

Dan Eckhoff asked a question on 14 Nov 2017 12:33 PM
My Badges

Question Status

Verified

I have a custom page based on a custom linked table in NAV 2017 CU11.  

If I set an Added filter I only get one record:

If I set a Quick Filter of the same value I get several records(expected result).

Reply

The screenshot is not visible ((, can you re-post it?

Reply
Dan Eckhoff responded on 27 Nov 2017 8:06 AM
My Badges

I'd like to be able to post screenshots, but I can't figure out how to fill the required field for "Description" when I use the rich formatting option.

Reply
Dan Eckhoff responded on 27 Nov 2017 8:10 AM
My Badges

Result using Added filter: Sell-to Customer No. = ‘000011’

Result using Quick filter with same value: Sell-to Customer No. = ‘000011’

Reply
Dan Eckhoff responded on 27 Nov 2017 8:14 AM
My Badges

Result using Added filter: Sell-to Customer No. = ‘000011’

Result using Quick filter with same value: Sell-to Customer No. = ‘000011’

Reply
Robertas Rackauskas responded on 27 Nov 2017 1:10 PM
My Badges

Hi Dan,

those filters work differently. The Quick Filter applies '*' wildcards and is NOT case sensitive. Whereas Advanced filter works as-is, and is case-sensitive. For example, put 'sample' in the Quick Filter for Description field, and system will show all results that description contains word 'sample' regardless of lower-case or capital letter. The same search in Advanced filter would be '@*sample*' - here symbol '@' takes away the case-sensitive and '*' means that description can contain anything before or anything after word 'sample'.

Hope that helps.

Robertas

Reply
Dan Eckhoff responded on 27 Nov 2017 1:54 PM
My Badges

Robertas, this doesn't seem to be the answer.  In my example the filter is all numeric and the result sets for the expected outcome is homogenous for the filter (all are 000011).

Reply
Dan Eckhoff responded on 27 Nov 2017 1:57 PM
My Badges

The list page is developed on a linked table of a SQL view.  Am I getting strange behavior due to that?

vSalesTransactions.RESET;

vSalesTransactions.SETRANGE("Sell-to Customer No_","No.");

IF vSalesTransactions.FINDSET THEN BEGIN

 CLEAR(vSalesTransactionsList);

 vSalesTransactionsList.SETTABLEVIEW(vSalesTransactions);

 vSalesTransactionsList.RUN;

END;

Reply
Robertas Rackauskas responded on 27 Nov 2017 2:21 PM
My Badges

Hi Dan, it is not THE answer, but I believe it leads you where to look :) If the Advanced Filter just gives you a single entry, and the Quick filter gives you several - most likely the Customer No field (which is NOT numerical - it's a string) in those additional entries has some hidden characters in it - a space, 'end of line' symbol etc. So I would question how the 'Sales Transaction' entries are created and whether there is a possibility to have hidden characters in it.

Reply
Dan Eckhoff responded on 27 Nov 2017 3:11 PM
My Badges

I still don't think that's it...I've included the SQL view creation code.  The fields should be identical as both are originally derived from Sales Line before posting:

CREATE VIEW [dbo].[Healy Awards$vSales Transactions]

AS

SELECT        dbo.[Healy Awards$Sales Invoice Line].[Sell-to Customer No_], 0 as [Document Type], dbo.[Healy Awards$Sales Invoice Line].[Document No_],

dbo.[Healy Awards$Sales Invoice Line].Type, dbo.[Healy Awards$Sales Invoice Line].No_, dbo.[Healy Awards$Sales Invoice Line].Description,

                        dbo.[Healy Awards$Sales Invoice Line].[Description 2], dbo.[Healy Awards$Sales Invoice Line].Quantity, dbo.[Healy Awards$Sales Invoice Line].[Unit of Measure Code],

                        dbo.[Healy Awards$Sales Invoice Line].[Amount], dbo.[Healy Awards$Sales Invoice Header].[Posting Date]

FROM            dbo.[Healy Awards$Sales Invoice Line] LEFT JOIN

                        dbo.[Healy Awards$Sales Invoice Header] ON dbo.[Healy Awards$Sales Invoice Line].[Document No_] = dbo.[Healy Awards$Sales Invoice Header].No_

WHERE dbo.[Healy Awards$Sales Invoice Line].[Sell-to Customer No_]<>''

UNION ALL

SELECT        dbo.[Healy Awards$Sales Cr_Memo Line].[Sell-to Customer No_], 1 as [Document Type], dbo.[Healy Awards$Sales Cr_Memo Line].[Document No_],

dbo.[Healy Awards$Sales Cr_Memo Line].Type, dbo.[Healy Awards$Sales Cr_Memo Line].No_, dbo.[Healy Awards$Sales Cr_Memo Line].Description,

                        dbo.[Healy Awards$Sales Cr_Memo Line].[Description 2], dbo.[Healy Awards$Sales Cr_Memo Line].Quantity, dbo.[Healy Awards$Sales Cr_Memo Line].[Unit of Measure Code],

                        dbo.[Healy Awards$Sales Cr_Memo Line].[Amount], dbo.[Healy Awards$Sales Cr_Memo Header].[Posting Date]

FROM            dbo.[Healy Awards$Sales Cr_Memo Line] LEFT JOIN

                        dbo.[Healy Awards$Sales Cr_Memo Header] ON dbo.[Healy Awards$Sales Cr_Memo Line].[Document No_] = dbo.[Healy Awards$Sales Cr_Memo Header].No_

WHERE dbo.[Healy Awards$Sales Cr_Memo Line].[Sell-to Customer No_]<>''

Reply
Robertas Rackauskas responded on 28 Nov 2017 1:26 AM
My Badges
Verified Answer

Please try following: put advanced filter '@*000011*'. Do you see the same entries as Quick Filter showed you? If you still see just one then I rest my case - there is something else wrong. If you see the same as with Quick Filter then go to the Customers list and put a Quick Filter on No. field '000011', or Advanced Filter '@*000011*'. How many customers do you see?

Robertas

Reply
Dan Eckhoff responded on 28 Nov 2017 6:16 AM
My Badges

Well, I'll be...you're right!

The next challenge was setting the record correctly.  SETRANGE doesn't work and I had to use SETFILTER instead to get the wildcards set.

Thanks, Robertas

Reply
Robertas Rackauskas responded on 28 Nov 2017 6:36 AM
My Badges

Happy to have helped Dan!

Reply
Robertas Rackauskas responded on 28 Nov 2017 1:26 AM
My Badges
Verified Answer

Please try following: put advanced filter '@*000011*'. Do you see the same entries as Quick Filter showed you? If you still see just one then I rest my case - there is something else wrong. If you see the same as with Quick Filter then go to the Customers list and put a Quick Filter on No. field '000011', or Advanced Filter '@*000011*'. How many customers do you see?

Robertas

Reply

SBX - Two Col Forum

SBX - Migrated JS