Connector call to get the latest date of the available records

Support, Troubleshooting, & Help
1

Hi,

how can I get the latest date from a set of filtered records, which have a date/time field?

Aggregation functions currently only seem to support int/numer columns…?

in SQL terms

SELECT MAX(date_column) as latest_date
FROM your_table
WHERE filter_condition;
2

Do I currently need to save the datetime as a unix timecode integer to make this happen?

3

Hey Seb, its a bit funky, but you can use the Mode aggregation to do this.

Create a query which returns only one row, sorted by the date field. Then get the mode aggregation of the Date field.

This is a super confusing workaround which Tulip has effectively sanctioned as the best way to “Get First Value” for any type.

3 Likes
4

+1 to @danielpomeranz’s point. The product team is working through how to best integrate this functionality into the platform. For now, the “mode hack” as a lot of folks refer to it is the best way to do things like lookup functions

1 Like
5

:grimacing: ok… thank you for the hint.

Does this also work with the connector version of the call… I am getting an error here…

6
  1. Just confirming, but you will have to set up the runAggregation API call to use a limit of 1 and be filtered and sorted accordingly.
  2. I also would not be surprised if there were some bugs with this as it is new functionality. If you can isolate this example and send it to Tulip support, I would not be surprised if an edge case was missed such as the ability to have the function return Timestamp values.