Popup reminder 2 workdays before a set date

[caman74]

I have a field in my DB that contains the shipdate for a shipment.

Is there a way to set up a popup reminder, let's say two workdays before the actual shipdate?

If the actual shipdate is on a thursday, the popup will show itself on a tuesday.
If the shipdate is on a tuesday, the popup will show on friday.
If the shipdate is on monday, the popup will show on thursday.

Thanks for all help!

Chris

 

[JeanW]

Basically you want the popup 2 days before the shipmentday, but not in a weekend.

You didn't mention if the shipmentday can be a weekendday...

In plain English: reminderDate = shipmentDate - 2.
If reminderDate = Sunday give Friday; if reminderDate = Saturday give Thursday.

You could do that with the DayName function but that's not a very goos idea. If the OS or the FM language is not English, it will not work.

Brings us at the calculation level.

Basiccalc is reminderDate = shipmentDate -2.
To check if the reminderDate is a weekend day you can use the DayOfWeek function, which will give you a number from 1 to 7, where 1 is for Sunday, 2 for Monday etc.

When you plug this knowledge into your formula with a Case statement, you can have something along these lines:

Case(
DayOfWeek ( dateShipment ) - 2 = 1;dateShipment - 4;
DayOfWeek ( dateShipment ) - 2 = 0;dateShipment - 3 ;
dateShipment - 2
)


If you want a more technical formula, but also shorter, result date:
dateShipment - Middle("4342222"; Mod(dateShipment - Date(1;3;1904); 7) + 1;1)

Now you can plug this result into the remindertext you want and tied that to whatever script you want in wathever layout you want.
To play with 1 day more or less, just change the value of the calc day.
I think (didn't check the calcs) the result will be a Friday if the shipmentday is a Monday....
But this will set you on the way...

HTH