Problem Filtering on Data in a Linked Object / Table

matttrax

Interesting problem with a Linked Object table in NAV. I have created a view in SSMS and linked a table to it. This by itself is working just fine. The code I used to create the view is listed below.

CREATE VIEW [Order Search]
WITH SCHEMABINDING AS
SELECT 
      Hdr.[Bill-to Customer No_],
      Hdr.[Sell-to Customer No_],
      Line.[Document No_],
      Line.[Line No_]
FROM dbo.[Company$Sales Header] AS Hdr JOIN
     dbo.[Company$Sales Line] AS Line ON
     Hdr.[No_] = Line.[Document No_] AND
     Hdr.[Document Type] = Line.[Document Type];

CREATE UNIQUE CLUSTERED INDEX INDX_V1
  ON dbo.[Order Search] ([Document No_], [Line No_]);
GO
  
CREATE INDEX IDX_V2
    ON dbo.[Order Search] ([Bill-to Customer No_]);
GO

The three images in the attachment show various filters. The first is how the form looks upon opening it. All data is displayed with the correct record count and no filters applied.

The second is filtering on an exact value. While the record count is correct, the form will only display one line. Updates or Finds to not correct the issue.

The third is filtering on that same value with an additional *. This displays all of the records and works even though there are no characters (even invisible ones) after the exact value. If there were additional characters I couldn't see I would not expect the second image to show even the one result that it does.

Does anyone have a clue as to why the record count listed would be correct, but the number of records displayed on the form is incorrect?

[Edit] It appears this is only happening on the Bill-to Customer No. column, and it happens whether there is an index on that field or not. If I filter the same way on Sell-to Customer No. it works just fine.

Belias

maybe, there's a [space] after the code value?
I've got a problem some days ago with data coming directly from an external application to a nav interface table:
1. 3rd party application inserts a space in a field directly in nav table (only one single space)
2. in my code i filtered mytable.setfilter(myfield,'<>%1','')
3. no records where fulfilling my filter: i've had to do a delchr on space characters at the end

EDIT: you're getting one record? maybe you've done an F2 on the field of that record and this removes the last space :-k

matttrax

There's no space. If I do a query in SQL against the view with the same filter in the WHERE clause I get all results.

SELECT * FROM [Order Search]
WHERE [Bill-to Customer No_] = 'C0109'

returns 114 records

SETFILTER("Bill-to Customer No.", 'C0109'')

returns 1 record, but has 114 as the count.

I think the problem must be at the form level and not the table level as the record count is right.

Belias

as i've never do a COUNT on my own table where i've had the problem, i didn' know if it was the count VS setfilter to behave different...who knows? I was just guessing
what about SETRANGE instead? (i know you probably need stefilter to allow wildcards, but can you give it a try?)

bbrown

Does your select statement guarentee a unique record? All records in NAV must be unique. This applies also to the views for linked tables.

matttrax

SETRANGE does not work either, I have tried.

This is my first adventure into creating indexes on views. I thought by creating the unique clustered index I was essentially making sure each record was unique. Do I need to do something with the keys in the table in NAV? Bill-to Customer No. is the primary key by default since it is the first field in the table. I get an error about the table no existing with the required object name in the first company listed. Tried with DataPerCompany = No, but that led to the error that the table does not exist.

kapamarou

Have you tried setting the Nav PK exactly as the PK of your view?

matttrax

Yes, it will not allow me to save the table when I do this. I get the error about the table not existing. So if there are Company 1, Company 2, and Company 3 in the database, I would get something like "The table does not exist with the required object name Company 1$Order Search"

kapamarou

If I remember correctly last time I did this and got the same error I had to delete the Nav table and recreate it with the correct key.

matttrax

That has at least solved the issue for now (don't know if I will run into anything else).

Had to delete the table in NAV, then recreate and specify the correct primary key before saving the table for the first time. Interesting to note that there does not have to be a unique clustered index on the view for this to work, there just has to be a compatible primary key specified at the NAV table level.

Thanks for your help everyone! No more ](*,) for me. Merry Christmas / Happy Holidays

kapamarou

Nice to have helped. Merry Christmas!