Find last month orders

[bens]

hi

I have a field order date

I would like to make a script that omit the record if the order date is from the same month and year as the current timeStamp

I will trigger the script on the last day of the month.

My format in order date is 26/01/2005

it should not be 30 days back, but all the record from the same month

How do i make the script ?

[CyborgSam]

The calculation you'll need will compare the month and year of today's date with the month and year of the order's date. Here's a boolean calculation that can be used in a number field so 1 means it's in the month and 0 if not. OrderDate is the field of type Date with the Order Date in it.

Let (

[

TodaysDate = Get ( CurrentDate ) ;

TodaysMonth = Month ( TodaysDate ) ;

TodaysYear = Year ( TodaysDate ) ;

OrdersMonth = Month ( OrderDate ) ;

OrdersYear = Year ( OrderDate )

] ;

( TodaysMonth = OrdersMonth ) and ( TodaysYear = OrdersYear )

)

[Raybaudi]

Hi

you can make a script like this:

Enter Find Mode[omit records]

Set field [OrderDate;Date ( Month(Get ( CurrentDate )) ; 1 ; Year(Get ( CurrentDate )) ) & ".." & Date ( Month(Get ( CurrentDate ) )+ 1 ; 1 ; Year(Get ( CurrentDate )) ) - 1]

Perform Find

[bens]

Hi

you can make a script like this:

Enter Find Mode[omit records]

Set field [OrderDate;Date ( Month(Get ( CurrentDate )) ; 1 ; Year(Get ( CurrentDate )) ) & ".." & Date ( Month(Get ( CurrentDate ) )+ 1 ; 1 ; Year(Get ( CurrentDate )) ) - 1]

Perform Find

Thanks, could someone please explain the what the script does ?

[Raybaudi]

Hi

if I have understood you correctly, this script MUST do what you needed !

Doesn't it ?

[Lee Smith]

Thanks, could someone please explain the what the script does ?

What it does is to create a range for the find. In this case, it is creating 12/01/2005..12/31/2005

I'll presume that you know what Find is, so the basic calculation is saying set the field "OrderDate" with the range when in Find Mode.

The first part of the calculation is saying to use the current's month and Year, and the "1" as the day, and the second part is saying to use the current month and year and the 1 again, but to add "+1" to the current month (equalling 13), and from that then, subtract "-1", which will return the end of the 12th month.

HTH

Lee

[bens]

hi

The script is perfect, thanks a lot raybaudi

I was trying to learn how it works, Thank you for the explanation Lee Smith.

how should it look if you want to trigger it the 1. of the next month instead?

[Raybaudi]

how should it look if you want to trigger it the 1. of the next month instead?

Enter Find Mode[omit records]

Set field [OrderDate;Date ( Month(Get ( CurrentDate ))-1 ; 1 ; Year(Get ( CurrentDate )) ) & ".." & Date ( Month(Get ( CurrentDate ) ) ; 1 ; Year(Get ( CurrentDate )) ) - 1]

Perform Find

and you can trigger it in whatever day of the next month (not only the first).

If you fire this script now, you will omitt all the records of November

[bens]

Thank you raybaudi :thankyou: