Skip to main content

How do I filter to a single row of a grouped record?

  • July 7, 2019
  • 3 replies
  • 23 views
J

Hello Airtable Community,

I’m building a database where I’m continually ranking content based on a given criteria. In my database there are 30 publishers with each publisher having many blog posts assigned to them. I want to know, of the posts published today, what URLs had the most shares. Secondly, I want to limit the table to one per publisher.

— Data —

Publisher A

  • Post A; 100 shares
  • Post B; 50 shares
  • Post C; 20 shares

Publisher B

  • Post D; 1,000 shares
  • Post E; 55 shares

Publisher C

  • Post F; 10 shares

— Desired Table —

Publisher | Title | Share Count
Publisher B Post D 1,000
Publisher A Post A 100
Publisher C Post F 55

Any ideas on how to achieve the desired table? I had been doing this in Google Sheets with two tables. The first was a pivot table sorting on social shares. The second was a lookup table off of the publisher record.

Thanks in advance for any insights you can offer!

3 replies

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • July 7, 2019

Hi there!

If I’m understanding correctly, you can achieve this using rollup fields. The following assumes you have a table called [Posts] with fields {Publisher}, {Date}, and {Share Count}; as well as a table of [Publishers].

First, you’ll want to add a formula field to your [Posts] table that shows only today’s share counts. Call it {Today’s Share Counts} and use the formula…

IF(IS_SAME(TODAY(), {Date}), {Share Count})

Next, on your table of [Publishers], add a rollup field that points at {Today’s Share Count} on the [Posts] table. Let’s say you call it {Today’s Highest Shares}. Use the formula:

MAX(values)

Next, create a new rollup field on your [Posts] table that points at {Today’s Highest Shares} on the [Publishers] table. Call it something like {Today’s Most Shared Post}, and use the formula:

IF(MAX(values) = {Today's Share Counts}, {Post Name})

This will give you the name of the post with the most shares today.

Finally, add a rollup field to your [Publishers] table, that looks at {Today’s Most Shared Post} on the [Posts] table. The following formula will give you today’s most shared post, and will trim it to just the first one it finds if more than one post happen to have the same amount of shares.

IF(FIND(",", ARRAYJOIN(ARRAYCOMPACT(values))), LEFT(ARRAYJOIN(ARRAYCOMPACT(values)), FIND(",", ARRAYJOIN(ARRAYCOMPACT(values))) - 1), ARRAYJOIN(ARRAYCOMPACT(values)))

You may have to switch some things around depending on your base set-up, but I hope that helps to get you started!

    J
    • Jared11
    • Author
    • New Participant
    • July 9, 2019

    Hi there!

    If I’m understanding correctly, you can achieve this using rollup fields. The following assumes you have a table called [Posts] with fields {Publisher}, {Date}, and {Share Count}; as well as a table of [Publishers].

    First, you’ll want to add a formula field to your [Posts] table that shows only today’s share counts. Call it {Today’s Share Counts} and use the formula…

    IF(IS_SAME(TODAY(), {Date}), {Share Count})

    Next, on your table of [Publishers], add a rollup field that points at {Today’s Share Count} on the [Posts] table. Let’s say you call it {Today’s Highest Shares}. Use the formula:

    MAX(values)

    Next, create a new rollup field on your [Posts] table that points at {Today’s Highest Shares} on the [Publishers] table. Call it something like {Today’s Most Shared Post}, and use the formula:

    IF(MAX(values) = {Today's Share Counts}, {Post Name})

    This will give you the name of the post with the most shares today.

    Finally, add a rollup field to your [Publishers] table, that looks at {Today’s Most Shared Post} on the [Posts] table. The following formula will give you today’s most shared post, and will trim it to just the first one it finds if more than one post happen to have the same amount of shares.

    IF(FIND(",", ARRAYJOIN(ARRAYCOMPACT(values))), LEFT(ARRAYJOIN(ARRAYCOMPACT(values)), FIND(",", ARRAYJOIN(ARRAYCOMPACT(values))) - 1), ARRAYJOIN(ARRAYCOMPACT(values)))

    You may have to switch some things around depending on your base set-up, but I hope that helps to get you started!


    I just wanted to provide an update. This solution worked beautifully. Thank you!

      A
      Forum|alt.badge.img+19
      • AlliAlosa
      • Inspiring
      • July 9, 2019

      I just wanted to provide an update. This solution worked beautifully. Thank you!


      Yay! You are entirely welcome, and thank you for the update!

      Terms & ConditionsAccessibility statement