How to create a workflow to send a notification?

Workflows
1

Hi any tip to create a workflow to send automatic notification on this use case:

  1. i got a vehicle management system, when i add new vehicle i add a field named Insurance Renewal date.

  2. I would like to send automatically a notification email when renewal date is close to date, lets say:

  3. send 1 notification 60 days before

  4. send 1 notification 30 days before

  5. send 1 notification 15 days before

is that possible? i got my db on postgresql where i save expiration dates in the format MM-DD-YYYY

tried the following

image

i setup some sample data and in workflow i triied this query

select * from vehiculos where seguro_expiracion = now() - interval '5 day'

but returns zero results

thank you

2

@agaitan026
Are you actually using Workflows? or are you running this directly in the application?

1 Like
3

i tried a workflow like this

image
image1510×765 74.1 KB

but what im not sure, is how i can configure this notifications, i asume should be a query in postgresql to get the values? or should i create one workflow per notification?

  • send 1 notification 60 days before

  • send 1 notification 30 days before

  • send 1 notification 15 days before
4

or should i do this at app level instead of workflow?

5

Hey @agaitan026 !

Seems like I'm replying to you in several threads this morning haha

Wanted to link @sophie's video here Retool Workflows - as I think some of the data problems might be resolved after copying some of the syntax in the video.

Could you potentially use a web hook? I also think it'd be nice if there were multiple start blocks

1 Like
6

im not sure how to use webhook for this @ScottR any ideas ? :slight_smile:

7

@agaitan026 Sorry but I haven't had a chance to dig into workflows....

8

and with the app itself? you think is possible? or at least how i can create a query to get or to know if something if expiring 60 days before or 30 days etc?

9

You can certainly write a query based on date as you initially tried - it may be that the query itself isn't correct...I will try to look further into this for you as soon as I can.

1 Like
10

thank you

11

i tried this

SELECT
  seguro_expiracion
FROM
  vehiculos
WHERE
  AGE(seguro_expiracion) <= INTERVAL '60 day';

and i will change 60day for 30 day 15 day etc

and works, but how can i implement that with workflow or case statement, i got all queries ready but not sure whats next :confused:

12

You could write the query in a way that it looks at today's date and compares it to the seguro_expiracion date value and say if greater than or equal to 60 send a notification...
So for example....
select * from vehiculos where {{moment()}} - seguro_expiraction = 60

or try
select * from vehiculos where seguro_expiraction = (current_date - interval '60 days')

1 Like
13

that doesnt works

i tried with this data

image

one is 30 days from now, and the other 60 days

image
image529×571 18.8 KB

it should return something i think

14

and for the others should i use a case statement?

if i use this AGE it works for example

SELECT
seguro_expiracion
FROM
vehiculos
WHERE
AGE(seguro_expiracion) <= INTERVAL '59 day';

i only shows me
image
one record

if i put 60 day

image

both that is ok right?

15

also tried this but doesnt works


SELECT
  seguro_expiracion_date,
  case when seguro_expiracion_date between now() - Interval ' 30 Day'
  and now() then 'Last 30 days' when seguro_expiracion _date between now() - Interval ' 60 Day'
  and now() then 'Last 60 days'
  
  from vehiculos
16

any tip on workflow? @Kabirdas @victoria ? my goal is to get notifications when i got documents that expires

  1. 60 days before and send email
  2. 30 days before and send notification email
  3. 15 days before and send notification email
17

I think the CASE statement needs work...

SELECT seguro_expiracion_date,
CASE 
when seguro_expiracion_date between now() - Interval ' 30 Day'
  and now() then 'Last 30 days' 
when seguro_expiracion _date between now() - Interval ' 60 Day'
  and now() then 'Last 60 days' 
END
  from vehiculos
1 Like
18

image
image498×567 33 KB

i think im confused :frowning:

2022-11-23 was month before 30 days before from today no?

2022-10-22 should be 60 days ago right?

19 
SELECT seguro_expiracion_date,
CASE 
when seguro_expiracion_date between CURRENT_DATE - Interval ' 30 Day'
  and CURRENT_DATE then 'Last 30 days' 
when seguro_expiracion_date between CURRENT_DATE - Interval ' 60 Day'
  and CURRENT_DATE then 'Last 60 days' 
END
  from vehiculos

Screenshot 2022-12-23 at 10.20.55 AM
Screenshot 2022-12-23 at 10.20.55 AM457×586 41.5 KB

20

:thinking: I think select * from vehiculos where seguro_expiraction = (current_date - interval '60 days') should work but October has 31 days so 2022-10-22 is actually 61 days before 2022-12-22. You could do something like

select
  *
from
  vehiculos
where
  seguro_expiraction = (current_date - interval '60 days')
  or seguro_expiraction = (current_date - interval '30 days')
  or seguro_expiraction = (current_date - interval '15 days')

(Note: If you want to send these notifications before the expiration date you might need to switch from - to + I could me misunderstanding here but just want to make sure :sweat_smile: )

If you only want to send one notification it might be important to use = instead of <= or between so you don't end up with duplicate notifications... Unless you have some way to mark which notifications have already been sent for a particular renewal date!

1 Like