Discussions

 View Only
  • 1.  Reminder Emails

    Posted 11-08-2022 16:53
    Hello!

    I have created an application to track employees leaving the company.  I currently have a reminder email being sent out 3 days before the employee's last day at the company.  The problem is sometimes the when the entry into the QB table is completed, it's already less than 3 days away, which won't trigger the reminder email.  

    Is there a way to set up a reminder email that will capture those that were entered with only 2, 1, or 0 days remaining, without sending multiple emails for the same entry? 

    I had thought about creating 4 different reminder emails to capture 3, 2, 1, and 0 days away, but I don't want to end up having 4 emails regarding the same employee.  The only other thing I can think of is if I do a report instead, but again I'd have to send that out daily to capture any possible date an employee is leaving the company.

    Thank you!

    ------------------------------
    V W
    ------------------------------


  • 2.  RE: Reminder Emails

    Posted 11-08-2022 17:01
    V W

    A Formula Check Box should suffice with two conditions

    The Departure Day is today or later
    The Departure Day is less than 3 days away.

    If ( Today()>=[Departure Day] and Today()-[Departure Day]<Days(3), True, False)

    ------------------------------
    Don Larson
    ------------------------------



  • 3.  RE: Reminder Emails

    Posted 11-08-2022 17:09
    Hi Don,

    How would i then integrate this to generate the reminder emails?  As far as I understand, I still have to set the 3 day time frame before the employee's last day at the company.

    The only way I can think of to utilize your suggestion is if I have a daily report go out with that formula included.

    Thank you,

    VW

    ------------------------------
    V W
    ------------------------------



  • 4.  RE: Reminder Emails

    Posted 11-09-2022 11:36
    Edited by Don Larson 11-09-2022 14:47
    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
    ------------------------------



  • 5.  RE: Reminder Emails

    Posted 11-09-2022 14:49
    Thanks, Don!  I'll give this a try.

    ------------------------------
    V W
    ------------------------------