Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formatting roll up arrays with more than 2 bits of data as dates to use in an automation trigger

445 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Fergus_Ford
4 - Data Explorer
4 - Data Explorer

Hello,

I have been googling this for a few days now and really need some help!

I am trying to set up an existing customer email reminder automation that uses date the next time we are going to visit a venue. The trigger will start a month or so before the visit date. 

A new customer fills out a form and a record is created. This gets linked to our Class bookings table with the venue that they have visited. 

The bookings table is linked to a classes table which shows each venue and the 4 dates we visit that venue.

 

So far i have managed to

Lookup the dates from Classes in the class bookings table - this gives me an entry with all 4 dates in the same cell. The format of this row is a date. (perfect)

In the customers table i am able to use a rollup to get this date across and then use an ARRAYSLICE to get the individual dates i need. They come in a really long format with the time 

2024-02-01T00:00:00.000Z.

I can clean this up using some variation of DATETIME_FORMAT but the roll up won't let me format it as a date. (not perfect...)

If i use ARRAYMAX or MIN you can format it but i have 4 bits of date in my array so i can't get to the middle bits of date. 

This means i can't set up the automation triggers as i would like and is driving me up the wall. 

 

I hope that explains my problem. Any help would be grateful. 

 

Thanks

1 Reply 1

Could you provide some screenshots of the relevant tables or, even better, a copy of your base?  It's hard to visualize the way you've got your data is setup and as a result I can't recreate your base structure to figure out a solution, sorry!