Forum Discussion

VW's avatar
VW
Qrew Trainee
2 years ago

Reminder Emails

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
------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    2 years ago
    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
    ------------------------------
    • VW's avatar
      VW
      Qrew Trainee
      2 years ago
      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
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        2 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
        ------------------------------