Error on MIN() function from arary

Hello, I am unable to get the minimum date value from an array of dates that are being returned by a formula.

Here is my complete formula:

  • MIN(NotesTasks.lookupRecords(Contact=rec , ActiveTask=TRUE()).DueDt)

The first part of the Complete Formula returns an array of dates. In other words, this part of the formula:

  • NotesTasks.lookupRecords(Contact=rec , ActiveTask=TRUE()).DueDt

returns (as an example) the following data:

  • [2021-08-31T05:00:00.000Z, 2021-08-30T05:00:00.000Z]

Why doesn’t the MIN( ) function return the minimum date when paired with the first part of my formula, especially when the first part of my formula returns an array of dates?

Thanks in advance!

Matt

PS , Here is a description of the data being mined:

  • NotesTasks is a Table of notes and tasks
    • ActiveTask is a Boolean Field - it is calculated
    • Contact is a Reference Field - referencing a single record in the AllContactsTable
  • AllContacts is a Table of contact information

Contact is a column that is a reference to my Contacts Table

ActiveTask is a calculated value that is either true or false

2 Likes

https://support.getgrist.com/functions/#min

Returns the minimum value in a dataset, ignoring non-numerical values.
Each argument may be a value or an array. Values that are not numbers, including logical and blank values, and text representations of numbers, are ignored.

I think the reason it ignores numbers is probably to mimic the excel function, but I agree I found this surprising.

Try min (which is the builtin python function and allows other types such as dates) instead of MIN.

2 Likes

Thank you so much for solving this! I love GetGrists formulas and use of python to set default values. Also, the native GetGrist formulas are a big help so not everything has to be written in pure Python.

Anyway, here is the working version of the formula:

IFERROR(min(NotesTasks.lookupRecords(Contact=rec , ActiveTask=TRUE()).Notification_DueDt),"")

1 Like