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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Using a calculated fie...
Microsoft Dynamics GP (Archived)

Using a calculated field for a restriction in SmartList Builder (get records from last month)

(0) ShareShare
ReportReport
Posted on by Rob Klaproth 1,730

I want to use add a date restriction to the SmartList to show Last Month's transactions since most of the time people are looking at last month's transactions when closing a month, etc.  One of the things I noticed on date restrictions built into SmartList is that it only has first day of current month and last day or current month, but they don't have last month.

I created a calculated field like this that calculates the first day of last month and the last day of last month (TSQL functions):

FirstDayofLastMonth

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

LastDayOfLastMonth

DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

However, when I use a restriction on the SmartList, and I select the Document Date field from the RM Open File, I don't see a way for me to select the calculated field.  

Does anyone know how to do this without creating SQL views?   Our client doesn't have access to their back end, so we can't really do that without some major red tape.

Thanks for the help in advance!

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Josh P Profile Picture
    Josh P 2,895 on at
    RE: Using a calculated field for a restriction in SmartList Builder (get records from last month)

    Hi Rob,

    When you add the calculated field, you should select it and place check marks in Display. I believe they are not checked by default after being created. Then you can find the field in your columns listing in Smartlist.

    Thanks,

    Joshua Pelkola

  • Rob Klaproth Profile Picture
    Rob Klaproth 1,730 on at
    RE: Using a calculated field for a restriction in SmartList Builder (get records from last month)

    Hi Joshua, I understand how that works - the question is can I actually run a restriction on my SmartList using the data from the calculated field, i.e. a date range.  

  • Verified answer
    Josh P Profile Picture
    Josh P 2,895 on at
    RE: Using a calculated field for a restriction in SmartList Builder (get records from last month)

    Hi Rob,

    Thanks for clarifying. The field comparison would allow this, but you would need to be able to add the field to the existing report to use as a comparison. Unfortunately, modifying a system report is not possible in SL Builder, but you could certainly create a custom report and add the restrictions as necessary there.

    Thanks,

    Joshua Pelkola

  • Rob Klaproth Profile Picture
    Rob Klaproth 1,730 on at
    RE: Using a calculated field for a restriction in SmartList Builder (get records from last month)

    Joshua,

    Thank you so much for your response back!  It took me a few minutes to sort it all out but I figured out what you meant by comparisons - you do it in SmartList itself, not in SLB, so what I did was added the calculated fields to the report and display by default in SLB, then I went back to SmartList, and added a field comparison:

    Document Date is between FirstDayofLastMonth and LastDayOfLastMonth and saved a favorite called "All AR Transactions from last month".  

    Hopefully some day Microsoft will build the last month function into SmartList, but for now we can work around that. :)

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…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans