Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

Setting a Date Range in Calcformula in Development Environment

(0) ShareShare
ReportReport
Posted on by Microsoft Employee

Hi

I am in the development environment of a "cue" I have created and want to create a stack for a role to show the current weeks sales quotations.

I am aware that within NAV 2013 for all cue's two date formulas are available as standard to use which are "Date Filter" & "Date Filter2"  These are hard coded in the system and when used within any of the objects calcformulas they show records for either the current day or up to the prior day.

I want to create a stack within a cue to show all sales quotations for the current week.  I have created this field but in the properties of the calcformula I can't get the filter to recognise the current week "CW" so that it filters these records.

I would appreciate anyone's help on this as it must be something so simple I am missing.

Thanks

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Setting a Date Range in Calcformula in Development Environment

    Hi

    Just a thought...

    In the design view of the cue, can I not insert any code into the calcformula section of the properties for it to recognise the current week?  At present what is shown in here at present is: -

    Count("Sales Shipment Header" WHERE (Posting Date=FIELD(Date Filter2)))  Obviously Date filter 2 shows all posted sales shipments for that day

    I can also change this calcformula to show this weeks posted shipments as follows: -

    Count("Sales Shipment Header" WHERE (Posting Date=FILTER(20/07/15..26/07/15)))

    however this filter is then held for this week with the dates specified so when we get to next week it will not refresh.

    Is there nothing I can enter into this area for the system to recognise each CW current week?

    Please advise.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Setting a Date Range in Calcformula in Development Environment

    Hi

    I am not sure I can get to the code??

    If I go into the design view of i.e page 9050 or even my page what do I then press to get to the code.  When I press (F9) nothing happens.  Does this mean I don't have permission?

    I do have administrator rights so would I need to ask our support team to alter my permissions or is there another way to get around this?

    Please advise.

  • Suresh Kulla Profile Picture
    Suresh Kulla 43,878 on at
    RE: Setting a Date Range in Calcformula in Development Environment

    Design the page and look at the code not sure if you have access to it. ?

    For your cue you need to add a field in the table, and for that field use calcformulae with date filter 2 field or new field Date Filter 3 then in the on open page you will add the code i have shown above.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Setting a Date Range in Calcformula in Development Environment

    Where abouts in the page 9050 am I to look?  I am in the object design of Page 9050 and not sure where you are looking.

    Please advise.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Setting a Date Range in Calcformula in Development Environment

    Thanks for your quick reply.

    If I try and use the standard NAV Date Filter2 field, within the cue, how do I get to the "Setrange" as I can't get into the formula as it is hard coded.  This is why I thought I needed to create a new one?

    Please advise.

  • Suresh Kulla Profile Picture
    Suresh Kulla 43,878 on at
    RE: Setting a Date Range in Calcformula in Development Environment

    Try to use setrange on DateFilter2

    StartDate := CALCDATE('<CW+1D-1W>',TODAY);

    EndDate :=  CALCDATE('<CW>',TODAY);

    SETRANGE("Date Filter2",StartDate,EndDate)

    You have to add the above filter on the open page trigger of the page. For example look at page 9050

    Then you use the Date Filter 2 in the field

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans