Need help with a Formula please!! Am I over thinking this??

[tlbrown88]

Hi there Ü hoping to get some help-

I have a report I am developing where I need it to run M-F (actually will run BOE and dist via email) and I need my open orders to pull in 2 days ahead of the cusotmer requested delivery date i.e,

Ran on Mon pulls in Wed's orders due {VBAK.VDATU}
Ran on Tue pulls in Thur orders due {VBAK.VDATU}
Ran on Wed pulls in Friday orders due {VBAK.VDATU}
Ran on Thur pulls in Sat, Sun and Mon orders due {VBAK.VDATU}
Ran on Fri pulls in Tues orders due {VBAK.VDATU}

I've been messing around with a few different ideas but none will pullin the Sat, Sun. Mon range into Thursday's report, I only get Monday.
Here is what I currently have

Formula name = @Adj Current Date
IF DayOfWeek (currentdate)=5 THEN currentdate+4 ELSE
IF DayOfWeek (currentdate)=6 THEN currentdate+4 ELSE
IF DayOfWeek (currentdate)=7 THEN currentdate+3 ELSE currentdate+ 2

and then this Boolean
{@Adj Current Date}={VBAK.VDATU} is a True/False where it = True

If I were running adhoc I would just say where {VBAK.VDATU}= (date(s) I was trying to pullin), easypeasy.....but this needs to run via BOE so it needs to be published.
Any help would be greatly appreciated. I have been at this for days and think I am just over thinking this whole thing....uggggg

Thanks!! Ü

 

[tlbrown88]

I should clarify that I only get Monday when I run it on Thursday.
All other days (M,T, W,F) pull in the orders correctly.....I just can't get the Sat, Sun, Mon all in one report.

Thanks! Ü
TB

 

[xxyyzz49]

This formula creates a date range in a global variable dr which can be used in selection formulas etc. The boolean return value indicates a weekday.

Local DateVar d := cdate ({Command.case_begin_dt});
Global DateVar Range dr;

// Monday - Wednesday
if WeekDay({Command.case_begin_dt}) in (2 to 4) Then 
(
   dr := DateSerial (Year(d), Month(d), day(d) + 2 ) To
         DateSerial (Year(d), Month(d), day(d) + 2 );
   true;
) // Thursday
else if WeekDay({Command.case_begin_dt}) = 5 Then 
(
   dr := DateSerial (Year(d), Month(d), day(d) + 2 ) To
         DateSerial (Year(d), Month(d), day(d) + 5);
   true;
) // Friday
else if WeekDay({Command.case_begin_dt}) = 6 Then 
(
   dr := DateSerial (Year(d), Month(d), day(d) + 3 ) To
         DateSerial (Year(d), Month(d), day(d) + 3);
   true;
) // default Sat and Sun to same day
else
(
   dr := DateSerial (Year(d), Month(d), day(d) ) To
         DateSerial (Year(d), Month(d), day(d) );  
   false; 
)

 

[pmax9999]

I would use the following Record Selection formula to return the data you need:

(
DayofWeek(CurrentDate) = 5 and
{VBAK.VDATU} in [(CurrentDate + 2) to (CurrentDate + 4)]
) 
or
(
DayofWeek(CurrentDate) = 6 and
{VBAK.VDATU} = (CurrentDate + 4)
)
or 
(
DayofWeek(CurrentDate) in [2, 3, 4] and
{VBAK.VDATU} = (CurrentDate + 2) 
)

Cheers
Pete

 

[xxyyzz49]

Some clarification of my formula above:
1. The global range variable "dr" is intended to be used in a record selection formula and will allow a range of dates through based on your criteria. "dr" returns Saturday, Sunday and Monday on Thursdays.
2. A formula has to return something but ranges are not allowed. This case TRUE will be returned if the processing day is a weekday. Other possibilities exist that are useful like a day number.
3. If the week start number is changed the formula as it is will need to be change.
4. The formula the needs "dr" for record selection will have to declare the variable as GLOBAL as in the response.

I should clarify that I only get Monday when I run it on Thursday.
All other days (M,T, W,F) pull in the orders correctly.....I just can't get the Sat, Sun, Mon all in one report.

Based on your prior statement a date range was used to return Saturday, Sunday and Monday when the processing day is Thursday. Otherwise a simple adding a number to date would have been sufficient.

 

[tlbrown88]

Thanks pmsawyer - I am trying to use the formula you suggested but I am not too familiar with variables.
I've used them a few times in subreports, passing info from sub to main but not for a dating formula such as this.
I have left my afternoon open to work on this.
Thanks again!

 

[pmax9999]

Unless I am missing something here, it is not actually necessary to use variables using the approach I suggested. Just add the code I provided above to the beginning of your record selection, enclosed in paretheses and followed by "and".

 

[xxyyzz49]

pmax9999 - I don't understand your last post. Can you provide an example?

tlbrown88 - your record selection would look something like:

Global DateVar Range dr;
//{some_date} from the current record
{some_date} in dr

 

[tlbrown88]

Going to give this a shot, running this adhoc for the remainder of the week to verify Thurs will pull in Sat-Mon. Hopefully it works, so far it seems to. I'll post back tomorrow with yay or nay. Thanks!

Formula: RDD Begin
IF DayOfWeek (currentdate)= 2 THEN currentdate + 2 ELSE //If Monday start on Wednesday
IF DayOfWeek (currentdate)= 3 THEN currentdate + 2 ELSE //If Tuesday start on Thursday
IF DayOfWeek (currentdate)= 4 THEN currentdate + 2 ELSE //If Wednesday start on Friday
IF DayOfWeek (currentdate)= 5 THEN currentdate + 2 ELSE //If Thursday start on Saturday (thru Monday)
IF DayOfWeek (currentdate)= 6 THEN currentdate + 6 ELSE //If Friday start on Tuesday
currentdate

Formula: RDD End
IF DayOfWeek (currentdate)= 2 THEN currentdate + 2 ELSE //If Monday start on Wednesday
IF DayOfWeek (currentdate)= 3 THEN currentdate + 2 ELSE //If Tuesday start on Thursday
IF DayOfWeek (currentdate)= 4 THEN currentdate + 2 ELSE //If Wednesday start on Friday
IF DayOfWeek (currentdate)= 5 THEN currentdate + 4 ELSE //If Thursday start on Saturday (thru Monday)
IF DayOfWeek (currentdate)= 6 THEN currentdate + 6 ELSE //If Friday start on Tuesday
currentdate

Record Selection
Where {VBAK.VDATU} in {@RDD-Begin} to {@RDD-End}

 

[pmax9999]

OK. Here goes for my final attempt to convey the solution:

Add the following code to the beginning of the existing Record Selection formula:

(
	(
	DayofWeek(CurrentDate) = 5 and
	{VBAK.VDATU} in [(CurrentDate + 2) to (CurrentDate + 4)]
	) 
	or
	(
	DayofWeek(CurrentDate) = 6 and
	{VBAK.VDATU} = (CurrentDate + 4)
	)
	or 
	(
	DayofWeek(CurrentDate) in [2, 3, 4] and
	{VBAK.VDATU} = (CurrentDate + 2) 
	)
)	and