Send an email a specific number of days before a certain date - Triggered action?

[roattw]

Hello everyone.

I have a new employee app that includes a  Start Date field.   Often, the new employee Start Date is may be in the future but data is entered slowly in advance over weeks of onboarding and as new info comes in.   So thier Start Date might actually be 2-3 months in the future even though ther new employee data is all entered.  We would like to send an email announcing a  new employee 2 weeks before the date of thier Start Date.  Would that be better as a Task or Triggered action?  And not sure if this would be an #insterted situation.  Was considering this, which did Valiadte.

[CoopperBackpack]

Hello @roattw,

As I can see, the issue is related to the 'less then or equal' compassion .

For example, the Start_date = 02/15/2023 and today is 02/17/2023.

Date difference in days  between 02/17/2023 and 02/15/2023 = -2. And -2 is less then 2 so the condition is met. 

With the setup from your screenshot, this Task today(02/17/2023) sends emails if Start_date is 02/19/2023 or less.

Usually, the comparison should be 'equal'. In this case, the Task will send emails once when this exact condition is met. 

For example, on 02/17/2023 emails are sent for the records with Start_date = 02/19/2023, 

                              on 02/18/2023 emails are sent for the records with Start_date = 02/20/2023, etc.

[Kronos]

HI @roattw,

You may use Task to run it daily.

You can refer to the examples in this howto post since you have a similar outcome:

https://howto.caspio.com/tasks/#:~:text=last run changes.-,Examples of tasks,-Example 1%3A A

 

I hope this helps!

[NiceDuck]

Task it is,

Oh and unless you want the task to send an email everyday until you manually remove the record on the table, I suggest that you stick on just 'Equal' instead of 'Less than or Equal'

[roattw]

Actually having trouble getting this to fire.  I tried 2 days setting time to run close to time I was testing.  It fired finally but set at "2 days or less" it gave me one correct start date within 2 days but two others that were significantly greater than 2 days.

[NiceDuck]

@roattw

You may want to be careful about the placing of your fields.

I checked the dates you are using and I got -75 which is indeed less than or equal 2, hence why they were included on the email that were sent.

 

[NiceDuck]

I suggest switching the place of the 'start_date' field and the timestamp, or change the condition into greater than or equal.


On the side unrelated note, your username kinda reminds me of 'Tale of Two Wasteland' mod of FO3 and FONV

[roattw]

Thanks for the reply!  Will swap those two date fields.

Username not from tale of Two Wasteleands.  Its from the early Stephen King series The Dark Tower.  The Gunslinger was the main character.  

https://www.amazon.com/Dark-Tower-I-Gunslinger/dp/1501143514/ref=pd_lpo_3?pd_rd_w=iuFkS&content-id=amzn1.sym.196193c7-f80f-4550-90d9-c8be3a442748&pf_rd_p=196193c7-f80f-4550-90d9-c8be3a442748&pf_rd_r=K4Z68QWM6TM4P2N22YEQ&pd_rd_wg=19qRn&pd_rd_r=45da0722-6e03-4471-b596-9210bfcef4c9&pd_rd_i=1501143514&psc=1

[roattw]

Lol.  Now I received a mailbox full of emails for this setup below.  I must be missing something basic