We need to have a list filtered on day & time. This view is used to automate sending of emails on or after a specific time.
So we have a field filled with date and time. Every 15 minutes we want to check and see if the time is passed, if so the automation is called to send an email.
How can we filter on time too? Is this possible without a calculated field?
in the view filters we currently have no filters by time.
I am pretty sure that a workaround via the Calculation field can be found but I would be interested if it would work natively with a trigger of the automations or a small customization.
Would it be theoretically possible to run the “On shedule” trigger every 15 minutes and then send the emails depending on a status that is set by the automation again?
The most efficient way to solve your problem would probably be to add “On minute” to the “When a date arrives” trigger as a smaller unit when the date field is due? Currently “On day” is the smallest unit. But I would have to check with the development how complex the extension would be.
I know the workarround with the calculation field, but it should not be needed and I do not want to much calculation fields just for filtering data.
What is possible: create a view with all items to be send (these will have a date/time and a status send/not send).
Every 15 minutes this list is populated (it will contain all items for today and status not send). Next is call a manual workflow. Here you also can not filter on time, so you should make a script to see it the time is already passed. If this is true, the item is send and the status is updated. If not, the item remains in the list for the next run.
My current solition is more complex: all items are synchronised to a mySQL database. On a server I have a job running. Here I select the same but now based on time too. If an item is not send and the date is passed, I call a webhook with the app_record_id. This will send the item and update it to status Send.
All solutions are doing far more transactions then needed. So far never had performance issues, but if you do this on a large scale, it might slow down. If you could just make a view with the time included you would have your source list. Now you only have to send each item in the list and update the status to send.
Background: the process is so fast that we send 2 emails almost at the same time. So you might receive the acknowledgement (we send bij email upon create) after your receive a process update (also send bij email). We want to make sure the acknowlegdement is send immidiatly and the update after one hour.
@knobel I have already briefly discussed today with @tim about a possible solution that could solve your use case as simply as possible.
It depends a bit on how many records have to be processed every 15 minutes with status “not send”.
As background info, we have 2 types of filters in the Tape automations. When filtering larger amounts of data, filters must be SQL based like in the views. These can’t be converted to script filters either, as no JS is possible here.
But as soon as smaller amounts are filtered, like often 1 record with “Record create” and “Record update” trigger but also up to 1.000 records in a collection with the “Filter collected” action we can use the more powerful JS filters which you can convert to a script filter in the 3-dot menu and then you can indeed filter by time.
Therefore the following approach: In the first step with an “On shedule” trigger every 15 minutes with the “Get view” action all records from the view which are today on status “not send” are collected to check with the action “Filter collected” with a script filter if the time is expired so that only the relevant records are remaining to send with this collection either directly with “Send email to collected” or with “Call automation on collected” via another automation.
@Tim provides you here the code snippet for the filter. If you have under 1.000 records every 15 minutes with the status “not send” it would be definitely very efficient and robust solved.
Thanks for sharing this advanced use case @knobel.
Indeed, @Leo came up with the great idea to leverage the powerful Filter collected action. You could create a view that includes your “Today’s” records and then filter consecutively on the date field using a script filter, which will then evaluate down to a millisecond whether that record’s date field is in the past.
The script neccessary is straight forward and uses dateFns isPast utility function (we create a JS date instance from the date and time string variables available in workflow context):
date_fns.isPast(new Date (collected_date_calc_field_date_start_date_utc + ' ' + collected_date_calc_field_date_start_time_utc))
Please note that your variable names will be different, so kindly check and use the ones provided in your workflow context after adding the Get view action.
The result could look something like this:
We get the view’s records, and filter using our script filter to only get those records where the datetime field is in the past. It worked on first try for my test scenario
Hope that solves your use case - let us know how it goes.