Skip to main content

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.

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.

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.


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.

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.


Thanks for the clarification! In your case, it looks like you’re actually two tables away from the original dates:



  • Records in the table from your screenshot—I’ll call it —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]

  • Those final records in oTable C] have the original dates


    • To 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 ATable 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 sTable 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.

    Reply


    Terms & Conditions