Reminder Emails

DonLarson
Qrew Elite
3 years ago
VW

That is correct. The formula check box would be a filter in your report.

However, the formula fails to solve the problem that you want it to go only once per person and not each day from 3 days out to the final day.

To solve that problem we need one more piece of information. This falls into a class of problems that I call "The Date of the Date." We need to know on what date was the Departure Date added to the system. Lets call this new field [Departure Date Entry]. There are several ways to add it, a Form Rule is probably the simplest, but a Pipeline could do it as well.

Now the criteria becomes

How many days until the Departure Date?
How many days were in the the window between the Departure Date Entry and Departure Day?

Now we compare them for 3, 2, 1 and 0 days until the Departure Day

// Variables

var number DaysTillDeparture = ToNumber(ToDays([Departure Day]-Today()));

var number DepartureDateWindow = ToNumber( ToDays([Departure Day]-[Departure Date Entry]));

// Check Box Logic

Case ( $DaysTillDeparture,

3, If( $DepartureDateWindow>3, true, false ),

2, If ($DepartureDateWindow=2, true, false ),

1, If ($DepartureDateWindow=1, true, false ),

0, If ($DepartureDateWindow=0, true, false ) , false

)

The result is that your check box is true only once depending on when the Departure Date is entered.

You can download the formula here
https://github.com/mcfindustries/Magic/blob/master/Community/DepartureDateFC.quickbase

------------------------------
Don Larson
------------------------------
- VW
  Qrew Trainee
  3 years ago
  Thanks, Don! I'll give this a try.
  
  ------------------------------
  V W
  ------------------------------

Forum Discussion