There is no value entry within the Filter

Well this is the error we received at one of the customer sites when they were executing the famous adjust cost batch job. Did some real head breaking into this one and discovered that when the Inventory setup has been set as:
1. Average Cost Calc. Type = Item
2. Average Cost Period = Month

The error was something like:
There is no Value entry within the Filter
Filters : Item No: 1100053749, Valution Date: 01/01/09..31/12/08

On debugging we found the error was being caused by an FINDSET; statement which was issues in the following Codeunit:

Codeunit : 5895, Inventory Adjustment
function : AvgValueEntriesToAdjustExist(OutbndValueEntry;ExcludedValueEntry,AvgCostAdjmtEntryPoint)


Got around the issue after a detailed profiling of the code and found that when the valuation date changes and a new filter is being evaluation in the code it somehow caused caused a wrong filter on the Value Entry Table the code snipped was as shown below

IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

How i managed to get around the issue was to add one line here as shown below:
IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint."Valuation Date" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

I finally concluded that the problem was encountered when a value entry record was found for a new financial year (2009 in this case) and there were no records for the previous year end (December 2008).

If we look at the code GetValuationPeriod(CalendarPeriod) in Table 5804 "Avg. Cost Adjmt. Entry Point" at the end of the function the following lines were setting the values to be used to set the filter :

//*****************************************************************************
IF FiscalYearAccPeriod."Starting Date" IN [CalendarPeriod."Period Start"..CalendarPeriod."Period End"] THEN
IF "Valuation Date" < FiscalYearAccPeriod."Starting Date" THEN
CalendarPeriod."Period End" := CALCDATE('<-1D>',FiscalYearAccPeriod."Starting Date")
ELSE
CalendarPeriod."Period Start" := FiscalYearAccPeriod."Starting Date";
//*****************************************************************************
All we did was to set the valuation date so that it does not evaluate to less then the Financial Period."Start Date"

Navision Filters

We all know the basics of Filters now lets turn to some advanced ways of using and understanding filters.

? which can be used while building a filter to substitute one unknown character pretty much like the Wildcard Characters used in DOS days

@ which can be used to ignore the case of the text being searched for
thus @co* would search for anything beginning with Co , cO, CO or co followed by any character as indicated by an asterix.

There has been some other types of filters which are referred to in Navision code with commands like FIND('=><') OR FIND('=<>')

1. '=><' when the above three expressions are combined it would stand for. If equal '=' rec is not found, search for a record which is smaller '<' and if smaller rec is not found search for a rec which is bigger '>' basically means find anything which was used in olden days to check if the rec filter was empty or not.

2. '=<>' this would translate to find something "equal to" or "not equal to" which means "find anything" similar to the command above

Today we also have ISEMPTY commmand to determine if a recordset is empty.

Procedure to Update Statistics for All indexes of a Table

sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.


Find the user defined procedures to run it for a table.

/*
select 'usp_update_statistics [' + name + ']' + char(13) + char(10)
+ 'go' + char(13) + char(10)
from sysobjects where type = 'U'
and not ascii( right ( [name], 1 ) ) between 48 and 57
*/


Alter procedure usp_update_statistics
@Table_Name varchar(255)
as
declare @Index_Name varchar(255)
declare @SQL nvarchar(500)


declare index_cursor cursor for ---Getting index name
select name from sysindexes
where id = object_id(@Table_Name)
and indid > 0
and indid < 255
and (status & 64)=0
order by indid

open index_cursor

fetch next from index_cursor
into @Index_Name

while @@fetch_status=0
begin
set @SQL = ''
set @SQL = @SQL + 'update statistics [' + @Table_Name + '] ( [' + @Index_Name + '] )'
set @SQL = @SQL + ' WITH FULLSCAN, NORECOMPUTE'
exec sp_executesql @SQL

print @SQL
print ''

fetch next from index_cursor
into @Index_name
end
close index_cursor
deallocate index_cursor
print 'Complete.'

sp_cursorfetch

Had this terrible issue with Navision Value Entries form. Whenever the form was opened from the Item Card Navision would freeze did a profile in SQL for all the SQL statements and found that the system was taking time to execute a command which started like sp_cursorfetch.

Some RND and found that the sp_cursorfetch is implemented by the database library to manage the cursors at the server side. Before a cursor fetch can be issue a cursor open statement has to be declared which would contain the base statement for the cursor. Once i found the statement being used for the cursor i did a execution plan display and found that it was not using the correct index.

I then updated the statistics for the index using the Update Statistics command we all know that statistics decide the selectivity of an index once this was done the cursor started using this index and the issue was resolved.