Query Design in NAV 2015 - Filter Date Syntax

Normajm

We are new to NAV 2015 and query building. I built a simple query on the sales invoice header table. We want to see all orders posted this month grouped by salesperson. One of the fields selected is the Posting_Date field. I need to filter this field starting with the first day of the current month. I don't see an option to do that on the column filter. The option for C/AL Globals is greyed out.

So I tried the following code in the OnBeforeOpen () level: SETFILTER(Posting_Date, >=CALCDATE('CM-1M+1D', Today));

The code fails with "A value or expression was expected". What am I missing? Is there a better way to handle this?

Thank you

mohana_cse06

you are mossing %1 in SETFILTER
https://msdn.microsoft.com/en-us/library/dd354919.aspx

kine

1) Wrong SETFILTER syntax...

SETFITLER(<field name>,'filter string',parameters...)

2) When using CALCDATE do not forget to use <> around your fixed values, else you are not multi-language compatible....

CALCDATE('<-CM>',TODAY)

Normajm

Thank you for the help...changed the code to read as follows:

SETFILTER("Posting_Date", %1, >=CALCDATE('<CM-1M+1D>',TODAY));

Now I'm getting a syntax error. The MSN sample shows a Record.SetFilter syntax, but because this is a query, I'm not clear on how to put the record part in. When I look at the list, the first value is CurrQuery. The value for the DataItem, which is the sales invoice line, isn't listed.

Rob_Hansen

Anyone interested in using NAV queries should look at our ReportPath product as an output mechanism. The product does a lot more than this, but here is a link to the page showing how it enables query output to be exposed through the NAV user interface easily: dynamicspath.com/products/reportpath/nav-query-output/.

The best part...we've made the add-on ABSOLUTELY FREE for all NAV 2015+ users until January 31, 2016. Contact info is on the website if you're interested.

I'll reply to your filter issue separately below.

Rob_Hansen

Try:

SETFILTER("Posting Date", '>=%1', CALCDATE('<CM-1M+1D>',TODAY));

(You may want to use WORKDATE rather than TODAY depending on how you want to support dates)

Normajm

Thank you Rob. That worked as planned, now I see how the syntax works. I am reaching out regarding ReportPath as well.

Rob_Hansen

Glad to help Normajm. You can email me at rob@dynamicspath.com for more info on ReportPath. It really is free until the end of January...not a crippled version and no upsells or anything. We think every NAV install should have it!

vaprog

Your CalcFormula does not work in all cases. Try what you get on e.g. Feb., 28 or June, 30.
The correct formula for what you stated you want is

'<-CM>'

<Cx> is the same as <+Cx> and gives you the last day in the period x. <-Cx> gives you the first day of the period.

And concerning the SETFILTER, always remember: The filter expression with all the operators goes to the second parameter. It's type is a string, so use quotes if you use a literal. The values to use in your filter expression follow beginning from the third parameter.