Skip to main content
Solved

Creating GI that lists Sales Orders from 13 months ago


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

View original
Did this topic help you find an answer to your question?

5 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 481 replies
  • 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


  • Author
  • Freshman I
  • 2 replies
  • 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+1
  • Pro I
  • 85 replies
  • 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
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 481 replies
  • 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


  • Author
  • Freshman I
  • 2 replies
  • November 21, 2024

Hi everyone, 

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


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings