Using dates in triggers and filters (Also JSONata and more useful record URL's)

🏗️ Showcases & Guides
,
1

Working with Dates in Tape Automations

It’s been a while since I shared a showcase, and I’ve seen a few questions recently about using date triggers and filters. So I thought I’d put together a quick guide with three practical examples.

We’ll cover:

  1. A simple “on date” trigger – running an automation at a set time on a date stored in a field.
  2. Searching for records that match criteria, including a date filter.
  3. Building a collection of nearly 200 records for a given day and turning it into a summary email report.

1. When a Date Arrives

View Screenshot
View Screenshot1200Ă—1145 73.1 KB

This trigger is really powerful and often forgotten. Say you want to send a birthday email to a client, or automatically change the status of a project on a set date – this is the trigger you need.

Simply pick it, select the date field, choose the time you want the automation to run, and off you go:

View Screenshot
View Screenshot1200Ă—828 83.5 KB

It really is that simple. If it were a birthday, part of the automation could even update the year by adding one with date_fns.addYears().

2. Using a Date in a Search

Let’s say I want to send an email every Monday with a list of all the contacts I should be contacting that week:

View Screenshot
View Screenshot1200Ă—1197 118 KB

Here I run a search for contacts whose Next Engagement Date meets certain criteria – including that it’s before the end of the week.

3. Building a Summary Report for Data in a Time Window

Now imagine we’re storing a lot of data in Tape and want an email every morning summarising yesterday’s entries.

In this example, I use an app that tracks crypto prices. The automation generates a report like this:

View Screenshot
View Screenshot1200Ă—528 83.1 KB

It’s a simple-looking report, but the data comes from 192 records (two every 15 minutes for 24 hours).

The app looks like this:

View Screenshot
View Screenshot1200Ă—853 139 KB

And the automation itself is just three blocks:

View Screenshot
View Screenshot1200Ă—1179 136 KB

Steps:

  1. Trigger the report every day at 08:00.
  2. Add an after filter for “current date minus 2 days” (yes, two not one – more on that below).
  3. Exclude today’s records. Since the automation runs at 8am, today’s early records would sneak in otherwise.
  4. Use a script block to process the collection.
  5. Build the email report using the variables from step 4.

:bulb: Why minus 2 days not 1?
The “after” filter is exclusive. Subtracting only one day could miss late records from yesterday, so we subtract two and then explicitly exclude today.

Although the example is crypto, just imagine KPI reports for managers – the same method applies and can be set up very quickly.

The Script

From the date filter we have 192 records, but they include both Bitcoin and Ethereum. We want to:

  1. Filter for only Ethereum records.
  2. Find the ID of the record with the highest price.
  3. Calculate the average price for the day.
  4. Create variables for our email.

Believe it or not, JSONata can do this in just two lines of code:

const ev = `(
  $subset := $[fields[external_id="type"].values.value.text = "ethereum"];
  $maxVal := $max($subset.fields[external_id="pound"].values.value);
  $r := $subset[fields[external_id="pound"].values.value = $maxVal].record_id;
  {'r':$r, "average": $formatNumber($average($subset.fields[external_id="pound"].values.value), "ÂŁ#,##0.00"), "date": $subset[record_id=$r].fields[external_id="date"].values.start, "value": $subset[record_id=$r].fields[external_id="pound"].values.formatted, "url": 'https://tapeapp.com/jmc/workspaces/crypto/apps/c-tracker/record/' & $r & '?view=130721'}
)`;
const { r, average, date, value, url } = jsonata(ev).evaluate(record_collection_c_tracker);

The fact that Tape includes JSONata is, in my view, is one of the things that makes Tape such a powerful tool. What would take dozens of blocks or even separate apps can be achieved in a couple of lines.

We then format the output into variables for our email block:

// Build the variables to pass into the email
var_date = date_fns.format(date_fns.parseISO(date), 'E do MMM y');
var_time = date_fns.format(date_fns.parseISO(date), 'HH:mm');
var_url = url;
var_value = value;
var_average = average;

And that’s it.

In two lines of JSONata plus a little formatting, we:
• Filtered 192 records to a subset.
• Found the highest price.
• Calculated the average.
• Extracted the relevant record details.
• Built a user-friendly record link.
• Output everything into variables for a clean email.

Why have I built my own URL

The record details include a record url "record_url": "https://tapeapp.com/jmc/record/166331315", at the same level as the record_id so some of you maybe wondering why do I not take the simpler approach of utilising this and instead build my own the reason is that I find it very frustrating to close a record from a link and be placed back in the Organisation home page, I never want to be there at that time I either want to close the tab completely or be in the default view of the records app, and that is what happens when using the link I have built in this part of the evaluate string:

"url": 'https://tapeapp.com/jmc/workspaces/crypto/apps/c-tracker/record/' & $r & '?view=130721'

Wrapping Up

This was supposed to be just a guide to date filters and triggers, but it naturally turned into a JSONata showcase too. That’s no bad thing – the two go hand in hand when dealing with large datasets in Tape.

Hopefully this gives you some ideas for your own setups.

7 Likes