web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Purchase Record
Microsoft Dynamics NAV (Archived)

Purchase Record

(0) ShareShare
ReportReport
Posted on by snoppy 4,824

Hi expert, I want to create a report for the customers who haven't purchased for 30 days, 60 days, 180 days, and 1 year etc. Currently my report is working, but the problem is 30 days record also includes those who haven't purchased for over 30 days such as over 2 years. This is not accurate. How to exactly retrieve those records, not roughly? Thank you in advance!

*This post is locked for comments

I have the same question (0)
  • Alex A Profile Picture
    Alex A 2,913 on at

    Hello, please clarify a little... when you say, "30 days record also includes those who haven't purchased for over 30 days", are you saying that the customer name appears on your 30 day list but has a $0 Sales total? Please describe your objective more and also what you have done so far?

  • snoppy Profile Picture
    snoppy 4,824 on at

    Yes, you are right. This is what I have done.

    Dim HowLong as options

    HowLong::"30 Days":

    CustLedgEntry.SETRANGE("Posting Date",TODAY-30,TODAY);

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 61,005 Super User 2025 Season 2 on at

    Use CALCDATE function to calculate the date

    msdn.microsoft.com/.../dd301368.aspx

    CALCDATE('<-15D>',TODAY)

  • snoppy Profile Picture
    snoppy 4,824 on at

    Thank you expert! I tried this: CustLedgEntry.SETRANGE("Posting Date",CALCDATE('<-15D>',TODAY));

    But the data remain the same. Thank you!

  • Suggested answer
    Alex A Profile Picture
    Alex A 2,913 on at

    The problem you are facing is that for some customers it is true that they have not purchased in the last 30 days, AND they have not purchased in the last 60 days, AND they have not purchased in the last 180 days... which causes that customer to appear in each of the time frames.

    It's easy to do a report that shows what customers haven't ordered in the last 30 days, the last 60 days, OR the last 90 days (one bucket), however even when I work out the logic to have the customer names only appear (if they haven't ordered) in ONLY one of these columns (30, 60, 90, 180, 365) the report doesn't look right because for example it would show the customer name in the 90 day column but nulls in the 30 and 60 day column though they also haven't ordered in the 30 and 60 day time frames.

    In all practicality, instead it may be better to have the name of the customer appear in the columns (30, 60, 90, 180, 365) for when they HAVE placed an order. Then you can infer which customers have not ordered for specific time frames by the null values that will show in the columns for which they haven't ordered.

    If you have Visual Studio or can run this query in SQL Management Studio then you can see an example of what I'm referring to with the following query:

    Note: *Replace the XXXXXX with the prefix of your NAV production database table names

    tempdelete.png

    This screen shot above shows when the customer last ordered. NULL values indicate they have not ordered within the last 30/60 days. Here is the T-SQL code for this:

      SELECT DISTINCT
    CASE
      WHEN [C].[No_] IN
      (
        SELECT [Sell-to Customer No_]
        FROM [XXXXXX$Sales Invoice Header]
        WHERE [Order Date] BETWEEN DATEADD(day , -30 , GETDATE()) AND GETDATE()
      ) THEN [C].[Name]
      END AS [Purchased 0-30 Days],
    CASE
      WHEN [C].[No_] IN
      (
        SELECT [Sell-to Customer No_]
        FROM [XXXXXX$Sales Invoice Header]
        WHERE [Order Date] BETWEEN DATEADD(day , -60 , GETDATE()) AND DATEADD(day , -31 , GETDATE())
      ) THEN [C].[Name]
      END AS [Purchased 31-60 Days],
    CASE
      WHEN [C].[No_] IN
      (
        SELECT [Sell-to Customer No_]
        FROM [XXXXXX$Sales Invoice Header]
        WHERE [Order Date] BETWEEN DATEADD(day , -90 , GETDATE()) AND DATEADD(day , -61 , GETDATE())
      ) THEN [C].[Name]
      END AS [Purchased 61-90 Days],
    CASE
      WHEN [C].[No_] IN
      (
        SELECT [Sell-to Customer No_]
        FROM [XXXXXX$Sales Invoice Header]
        WHERE [Order Date] BETWEEN DATEADD(day , -180 , GETDATE()) AND DATEADD(day , -91 , GETDATE())
      ) THEN [C].[Name]
      END AS [Purchased 91-180 Days],
    CASE
      WHEN [C].[No_] IN
      (
        SELECT [Sell-to Customer No_]
        FROM [XXXXXX$Sales Invoice Header]
        WHERE [Order Date] BETWEEN DATEADD(day , -365, GETDATE()) AND DATEADD(day , -181 , GETDATE())
      ) THEN [C].[Name]
      END AS [Purchased 181-365 Days],
    CASE
      WHEN [C].[No_] IN
      (
        SELECT [Sell-to Customer No_]
        FROM [XXXXXX$Sales Invoice Header]
        WHERE [Order Date] > DATEADD(day , -365, GETDATE())
      ) THEN [C].[Name]
      END AS [Purchased 365+ Days]

  FROM [XXXXXX$Customer] [C]
  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,245 Super User 2025 Season 2 on at

    You need to use from date and to date so try this

     CustLedgEntry.SETRANGE("Posting Date",CALCDATE('<-30D>',TODAY),TODAY);

  • snoppy Profile Picture
    snoppy 4,824 on at

    CustLedgEntry.SETRANGE("Posting Date",CALCDATE('<-30D>',TODAY),TODAY);

    the data remain the same. There is no change. Thank you!

  • snoppy Profile Picture
    snoppy 4,824 on at

    Thank you! How to write a report instead of doing from the SQL?

  • Suggested answer
    Tharanga Chandrasekara Profile Picture
    Tharanga Chandrasekara 23,118 on at

    To develop a report you need to have developer license for Microsoft Dynamics NAV and  knowledge on the C/AL development.  Report you are planning to develop is bit complex and might need some support.

    If you have the developer license let us know, we can guide you to some extent.

  • Suggested answer
    Tharanga Chandrasekara Profile Picture
    Tharanga Chandrasekara 23,118 on at

    Refer below links : 

    Video : https://msdn.microsoft.com/en-us/dynamics/nav/dn783472.aspx

    Designing Reports for the RoleTailored Client : https://msdn.microsoft.com/en-us/library/dd355370.aspx

    Designing a Customer List Report : https://msdn.microsoft.com/en-us/library/dd355179.aspx

    Designing a Customer Sales Order Report : https://msdn.microsoft.com/en-us/library/dd339014.aspx

    Creating a Link from a Report to a Page : https://msdn.microsoft.com/en-us/library/dd301304.aspx

    Walkthrough: Designing a Report with Images, Interactive Sorting, and Visibility Toggle : https://msdn.microsoft.com/en-us/library/dd301353.aspx

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
HoangNam Profile Picture

HoangNam 7

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans