Skip to main content
Solved

Grouping By Date not working

  • June 11, 2020
  • 4 replies
  • 36 views
M

I have 2 different lookup fields:

  • Release1 = lookup individual item release date from another table
  • ReleaseMulti = rollup multiple items release date based on condition (if its a multi-item) using MIN(Values)

I then have a formula field:

  • RELEASE =
    IF(Release1="", ReleaseMulti, Release1)

Without grouping, this RELEASE field appears correct… for example showing 9/30/2020 in several records.

However… if I group by RELEASE, I find that it will split into different groups if the 9/30/2020 is from Release1 vs ReleaseMulti in a different group

This is very problematic.
I know ReleaseMulti is an array value, and on a similar text based field I used &’’ trick I read on these boards which solved that issue. But in this date field adding the &’’ to the end of ReleaseMulti in my formula does not work.

I also tried many combinations of arrayflatten, arraycompact, date parse, datetimeformat, etc… to no avail

Can anyone advise how to get a single date from the ReleaseMulti array and convert it to be the exact same date format as the Release1 so that I can group them together properly?

Best answer by Justin_Barrett

You actually have one lookup and one rollup. The two types may seem similar on the surface, but operate differently especially when it come to their output.

Lookup fields always create and return arrays, whereas a rollup’s output may be an array sometimes, or a string, or a date, all depending on the aggregation formula used.

My gut says that the cause of the grouping inconsistency is because {Release1} is a lookup and is returning an array, which is causing Airtable to see it differently than the output of {ReleaseMulti}, which will be a date (not an array) thanks to the MIN(values) aggregation formula you’re using. To clarify: values on its own will be an array if the collected data contains more than one item, which it sounds like it is in this case. MIN(values) always returns a single item, however, by taking the items in the array and finding the lowest one (or earliest one in the case of dates).

You could actually change {Release1} to a rollup even though it’s only pulling a single date, using values as the aggregation formula, which will leave it as a single date and not a date inside an array.

Also, your {RELEASE} formula would be better structured like this:

IF(Release1, Release1, ReleaseMulti)

That way if {Release1} has a date, that date is used; otherwise the date from {ReleaseMulti} will be used.

    4 replies

    Justin_Barrett
    Forum|alt.badge.img+21
    • Justin_Barrett
    • Inspiring
    • 4647 replies
    • Answer
    • June 11, 2020

    You actually have one lookup and one rollup. The two types may seem similar on the surface, but operate differently especially when it come to their output.

    Lookup fields always create and return arrays, whereas a rollup’s output may be an array sometimes, or a string, or a date, all depending on the aggregation formula used.

    My gut says that the cause of the grouping inconsistency is because {Release1} is a lookup and is returning an array, which is causing Airtable to see it differently than the output of {ReleaseMulti}, which will be a date (not an array) thanks to the MIN(values) aggregation formula you’re using. To clarify: values on its own will be an array if the collected data contains more than one item, which it sounds like it is in this case. MIN(values) always returns a single item, however, by taking the items in the array and finding the lowest one (or earliest one in the case of dates).

    You could actually change {Release1} to a rollup even though it’s only pulling a single date, using values as the aggregation formula, which will leave it as a single date and not a date inside an array.

    Also, your {RELEASE} formula would be better structured like this:

    IF(Release1, Release1, ReleaseMulti)

    That way if {Release1} has a date, that date is used; otherwise the date from {ReleaseMulti} will be used.

    M
    • matt_stewart
    • Author
    • Participating Frequently
    • 6 replies
    • June 11, 2020

    You actually have one lookup and one rollup. The two types may seem similar on the surface, but operate differently especially when it come to their output.

    Lookup fields always create and return arrays, whereas a rollup’s output may be an array sometimes, or a string, or a date, all depending on the aggregation formula used.

    My gut says that the cause of the grouping inconsistency is because {Release1} is a lookup and is returning an array, which is causing Airtable to see it differently than the output of {ReleaseMulti}, which will be a date (not an array) thanks to the MIN(values) aggregation formula you’re using. To clarify: values on its own will be an array if the collected data contains more than one item, which it sounds like it is in this case. MIN(values) always returns a single item, however, by taking the items in the array and finding the lowest one (or earliest one in the case of dates).

    You could actually change {Release1} to a rollup even though it’s only pulling a single date, using values as the aggregation formula, which will leave it as a single date and not a date inside an array.

    Also, your {RELEASE} formula would be better structured like this:

    IF(Release1, Release1, ReleaseMulti)

    That way if {Release1} has a date, that date is used; otherwise the date from {ReleaseMulti} will be used.


    I did not know lookup returns an array… are you sure of that? The Release1 lookup is pulling a manual date field from another table.

    The ReleaseMulti rollup is pulling an array of those manual dates, but then giving me the “Min(Value)”.

    I have tried changing the Release1 to a rollup, but then no matter what option I choose it gives me an empty result.

    Justin_Barrett
    Forum|alt.badge.img+21
    • Justin_Barrett
    • Inspiring
    • 4647 replies
    • June 11, 2020

    I did not know lookup returns an array… are you sure of that? The Release1 lookup is pulling a manual date field from another table.

    The ReleaseMulti rollup is pulling an array of those manual dates, but then giving me the “Min(Value)”.

    I have tried changing the Release1 to a rollup, but then no matter what option I choose it gives me an empty result.


    In a lot of the tests I’ve run up to this point, every lookup I’ve used has returned an array. However, a test I ran just a few minutes ago appeared to disprove that theory, so I definitely need to do more tests.

    What’s your setup for the {Release1} field as a rollup? Could you screenshot the setup dialog for the rollup field, as well as the current setup where it’s a lookup? The only difference should be the presence of the aggregation formula for the rollup field. The other two options should be identical between the two types.

    M
    • matt_stewart
    • Author
    • Participating Frequently
    • 6 replies
    • June 11, 2020

    In a lot of the tests I’ve run up to this point, every lookup I’ve used has returned an array. However, a test I ran just a few minutes ago appeared to disprove that theory, so I definitely need to do more tests.

    What’s your setup for the {Release1} field as a rollup? Could you screenshot the setup dialog for the rollup field, as well as the current setup where it’s a lookup? The only difference should be the presence of the aggregation formula for the rollup field. The other two options should be identical between the two types.


    never mind, im an idiot
    I was pulling the wrong linked data since I have to link 2 different ways to get the single items vs the multi items from the other table

    the change for Release1 to a Rollup using MIN(values) does in fact solve the problem.

    Thanks for the help!


    Terms & ConditionsAccessibility statement