Need a little help with an expression for record selection for append

[chrisdoane]

I need to run an append from one table to another based on a date field called mail_date.

But the append needs to run one way Wednesday-Friday (we don�t run it Saturday or Sunday), but if it is Monday or Tuesday it needs to run different. Here is the example:

Wednesday-Friday � mail_date=date()-3 (todays date � 3 days)
Monday � mail_date=date()-5 (todays date � 5 days)
Tuesday � mail_date=date()-6 (todays date � 6 days)

The append is run as part of a script run from a button push. Hope that was clear enough.

Any help would be appreciated.

Chris

[Stan Mathews]

Re: Need a little help with an expression for record selection for append

If I understand correctly the append definition transaction filter expression would be.


case(between(dow(date()),4,6),mail_date = date()-3,dow(date()) = 2,mail_date = date()-5,dow(date()) = 3,mail_date = date()-6)

You could add a nonsense "otherwise" test to ensure nothing was appended if run on Sat/Sun

case(between(dow(date()),4,6),mail_date = date()-3,dow(date()) = 2,mail_date = date()-5,dow(date()) = 3,mail_date = date()-6,.T.,mail_date= {12/31/2099})

[Tom Henkel]

Re: Need a little help with an expression for record selection for append

As usual, I got to this a little slower than Stan.

You might want to set a "Case" statement in the x_basic to determine the appropriate date

something like this:

DIM mdate as D
'DOW() function returns the numeric day of the week: Sunday =1, Saturday = 7
Case (between(dow(date()),5,7),mdate = date()-2,between(dow(date(),2,3),mdate=date()-5)
'Friday -> Wednesday
'Thursday-> Tuesday
'Wednesday-> Monday
'Tuesday-> Friday
'Monday->Thursday

then use mdate in the filter for the records you want to append.

Tom

[Tom Henkel]

Re: Need a little help with an expression for record selection for append

You also may want to think about what to do with Holidays...

[chrisdoane]

Re: Need a little help with an expression for record selection for append

I have a manual run for the exceptions. Just needed to make the normal daily stuff easier. AS always gentlemen.... Thank you so much for all your help!