Skip to main content
Answer

Creating GI that lists Sales Orders from 13 months ago

  • October 28, 2024
  • 5 replies
  • 90 views
R
Freshman I

Hi all, I’m trying to write a GI that lists Sales Orders from 13 months ago.

 

For example, if I run the GI successfully with a business date of today, it will show me all Sales Orders in September 2023.

 

I think this is best achieved by setting some conditions in the Conditions tab of the GI, but I am not sure of the formula to use. Does anyone have any suggestions or can point me in the right direction?

 

 

Best answer by lauraj46

Hi @rhemerik ,

I was testing this too when @craig2 posted 😁.  Just as he said:

Formulae:

=DateAdd(CDate(CStr(month([BusDate])) + '/1/' + CStr(year([BusDate])-1)), 'm', -1)

=DateAdd(CDate(CStr(month([BusDate])) + '/1/' + CStr(year([BusDate])-1)), 'd', -1)

Hope that helps!

Laura

    5 replies

    lauraj46
    Captain II
    Forum|alt.badge.img+8
    • lauraj46
    • Captain II
    • October 28, 2024

    Hi @rhemerik ,

    You can add a condition that SOOrder.OrderDate is between:

    =DateAdd(CDate(CStr(month(Today())) + '/1/' + CStr(year(Today())-1)), 'm', -1)

    =DateAdd(CDate(CStr(month(Today())) + '/1/' + CStr(year(Today())-1)), 'd', -1)

    Hope this helps!

    Laura

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    Laura03
    Chris Hackett
    R
    R
    Freshman I
    • rhemerik
    • Author
    • Freshman I
    • October 28, 2024

    Thanks Laura, that works. An issue that I now have is that the system doesn’t look at the business date.

    So if I change the business date to, say, August 2024 the above GI will still display orders based on the actual date today.

    craig2
    Pro I
    Forum|alt.badge.img+3
    • craig2
    • Pro I
    • October 28, 2024

    Hi @rhemerik ,

    User @bheyns05 solved a similar issue, see the post below.  I didn’t know this either, but using @Today in a Parameter apparently looks at the Business Date, while Today() in a Data Field formula looks at the system date.  I tested this real quick to verify, and sure enough, it worked!

    Basically you would create a date parameter using @Today as the Default value, then set up the conditions similar to how @lauraj46 listed above.  Hope that helps!
     

     

    lauraj46
    lauraj46
    Captain II
    Forum|alt.badge.img+8
    • lauraj46
    • Captain II
    • Answer
    • October 28, 2024

    Hi @rhemerik ,

    I was testing this too when @craig2 posted 😁.  Just as he said:

    Formulae:

    =DateAdd(CDate(CStr(month([BusDate])) + '/1/' + CStr(year([BusDate])-1)), 'm', -1)

    =DateAdd(CDate(CStr(month([BusDate])) + '/1/' + CStr(year([BusDate])-1)), 'd', -1)

    Hope that helps!

    Laura

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    craig2
    Chris Hackett
    R
    Freshman I
    • rhemerik
    • Author
    • Freshman I
    • November 21, 2024

    Hi everyone, 

    A belated Thank You to you all!  Some very valuable insights and knowledge nuggets.

    lauraj46
    Chris Hackett
    Terms & ConditionsCookie settingsAccessibility statement
    About Acumatica ERP system
    Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
    © 2008 — 2024  Acumatica, Inc. All rights reserved