I have tested the code below, it works perfectly in Native Nav database but will not work in SQL. In fact, I get a total blank for all records instead of a filter of just some. I need to exclude some of the Purchase Orders for some of the users. Can anyone help with a fix for this please?
UserSetup.GET(USERID);
IF UserSetup.“Confirm Req. Line Insert” THEN BEGIN
FILTERGROUP(10);
SETFILTER(“No.”, ‘<%1’, ‘P-SNET*’);
FILTERGROUP(11);
SETFILTER(“No.”, ‘<%1’, ‘PO-RMA*’);
FILTERGROUP(0);
END;
This is because SQL sorts differently then Navision.
Try this:
UserSetup.GET(USERID);
IF UserSetup.“Confirm Req. Line Insert” THEN BEGIN
FILTERGROUP(10);
IF RECORDLEVELLOCKING THEN
SETFILTER(“No.”, ‘<%1’, ‘P-SNET*’)
ELSE
SETFILTER(“No.”, ‘<%1’, ‘PO-RMA*’);
FILTERGROUP(0);
END;
Or if it this doesn’t work, try to invert both SETFILTERS
No good.
Is it possible that there is no way of excluding records?
Maybe I should have several filters to include?
BTW, what do you mean by inverting the filters?
Well, after much testing decided that the * is not good in this situation, I found that if the whole value is used such as PON-123456 then all is fine.
Have found a workaround by populating a spare field using a non-printing report and filtering on that (’%1’, ‘’
In the meantime if anyone can throw some light on the use of the * or ? in such filters it will be very useful.
UserSetup.GET(USERID);
IF UserSetup.“Confirm Req. Line Insert” THEN BEGIN
FILTERGROUP(10);
IF RECORDLEVELLOCKING THEN
SETFILTER(“No.”, ‘<%1’, ‘PO-RMA*’)
ELSE
SETFILTER(“No.”, ‘<%1’, ‘P-SNET*’);
FILTERGROUP(0);
END;
What about this one (just guessing):
UserSetup.GET(USERID);
IF UserSetup.“Confirm Req. Line Insert” THEN BEGIN
IF RECORDLEVELLOCKING THEN BEGIN
FILETRGROUP(10);
SETFILTER(“No.”, ‘<%1&<%2’, ‘PO-RMA*’, ‘P-SNET*’)
END ELSE BEGIN
FILTERGROUP(10);
SETFILTER(“No.”, ‘<%1’, ‘P-SNET*’);
FILTERGROUP(11);
SETFILTER(“No.”, ‘<%1’, ‘PO-RMA*’);
END;
FILTERGROUP(0);
END;
The general problem with FILTERGROUPs is, that with SQL Server it is not possible to have different “Filter-layers” on the same field/column. All filters are combined to one WHERE clause.
Another issue: as you are using Wildcards, you are “raising hell” in SQL Server: Here it has no chance at all to pick the right index - in this case the Primary Key Clustered - and it will for sure perform a full Clustered Index Scan; resulting in longer duration and potential blocking problems …
Joerg, thanks for this, I never realized Navision did this on SQL, but it definitely explains a problem I had some years back, and never resolved. Its actually quite interesting, since this clearly shows that there are some things that just work differently on Native and SQL.
Even on Native, “*” is not a great idea. Though at least having it at the end of the string means that its not too bad.
Filter layers : strange that SQL has problems with it, Navision should just send an AND statement with the same field but different values
“*” : check this: http://www.mibuso.com/forum/viewtopic.php?t=16185
Hmm that is a neat solution.
Though in this partiucular case, since its already “Hard coded”, I would just hard code the entire purchase order number.