Dynamics NAV - LS Retail - Scheduler Job Record Filter

• Edit Subjob>Navigate>From Table Filters
• Use Filter Type "Filter" its the only one that works
• It will insert this value directly into the SQL query
• It can only hold 12 characters
• Eg. Filter1 value to filter a date should be
• ='20160120'
• If using the sql filter
• Also try '2016-6-20' because the field only accepts 12 characters in the filter

Example - How to set the filter to the current date for a subjob using CAL code

SJFilt."Value 1" := '='+ '''' +  FORMAT(DATE2DMY(TODAY,3),4)+
CONVERTSTR(FORMAT(DATE2DMY(TODAY,2),2),' ','0')+ CONVERTSTR(FORMAT(DATE2DMY(TODAY,1),2),' ','0') + ''''


SJFilt."Value 1" := '='+ '''' + FORMAT(DATE2DMY(CALCDATE('<1D>',TODAY),3),4)+ CONVERTSTR(FORMAT(DATE2DMY(CALCDATE('<1D>',TODAY),2),2),' ','0')+ CONVERTSTR(FORMAT(DATE2DMY(CALCDATE('<1D>',TODAY),1),2) ,' ','0')+ '''' ;

Example2 - How to set the filter to the current date for a subjob using sql

update [Cronus].[dbo].[Cronus$Scheduler Subjob Filter] set [Value 1] = '>'+'''' + cast(year(getdate()) as varchar) +  right('0' + cast(month(getdate()) as varchar),2) +  right('0'+case when day(getdate()) = 1 then cast(day(getdate()) as varchar) else cast(day(getdate())-1 as varchar) end,2) + ''''  FROM [Cronus].[dbo].[Cronus$Scheduler Subjob Filter] where [Subjob ID] = 'ITEM_DATE_FILTER'