Jan 24, 2022 07:13 PM
I am trying to create a to do list view. Some items are linked to multiple days and are grouped together when sorted by date. I am looking for a formula that woulds sort them by the first date only. I have tried a rollup with the MIN function and just get a result of 0.
Jan 24, 2022 09:40 PM
Welcome to the community, @Michael_Cory! :grinning_face_with_big_eyes: If the MIN()
function isn’t working to find the earliest date, then chances are your source dates aren’t actual datetimes. What’s the type of the source field that the rollup is retrieving? Is the table that it’s rolling up from a “native” table or a synced table? Any other details that you can share about the setup would be helpful.
Jan 24, 2022 10:49 PM
Thank you for the help. The source is a schedule column formatted with US style dates like 1/25/2022. I am attaching a screenshot. I am new to writing formulas so there may be a few mistakes. There may be a better way around this problem I have not thought of as well.
Jan 25, 2022 01:43 PM
Thanks for the clarification! In your case, it looks like you’re actually two tables away from the original dates:
[Table A]
—are linked to another table, [Table B]
[Table B]
has a field—the one that you’re looking/rolling up—containing linked records to yet another table, [Table C]
[Table C]
have the original datesTo pull all of these through, you’ll need to use rollup fields all the way. It might seem like the linked record field itself would make that unnecessary because it already contains the dates, but unfortunately that won’t work.
It’s easy to assume that a linked record field would return an array when queried by a formula, but that’s not the case. Airtable takes the text from that linked record field—not the original data values of the linked records’ primary fields—inserts a comma between each item, and serves up the result as a single string. That’s just one of a few reasons why your current system doesn’t work: you’re rolling up a string, not a collection of actual datetime values, and only the latter will work with MIN()
and MAX()
In [Table B]
, add a rollup field that retrieves the dates from the primary field in {Table A}
, assuming that the primary field is actually a date field, not a formula field that’s formatting dates from yet another field. (If it’s the latter, then point to whichever field is a true date field.) This rollup field can use the MIN(values)
aggregation formula to output the earliest of the listed dates. Then in [Table A]
, do the same thing, rolling up the output of that other rollup field using the same aggregation formula.
If I’ve misinterpreted something about the table arrangement, let me know, but I’m pretty sure that should get you what you want.