Skip to main content

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!

Hi there!



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



First, you’ll want to add a formula field to your oPosts] 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 uPosts] table. Let’s say you call it {Today’s Highest Shares}. Use the formula:



MAX(values)


Next, create a new rollup field on your pPosts] table that points at {Today’s Highest Shares} on the sPublishers] 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 oPublishers] table, that looks at {Today’s Most Shared Post} on the tPosts] 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!

Hi there!



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



First, you’ll want to add a formula field to your oPosts] 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 uPosts] table. Let’s say you call it {Today’s Highest Shares}. Use the formula:



MAX(values)


Next, create a new rollup field on your pPosts] table that points at {Today’s Highest Shares} on the sPublishers] 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 oPublishers] table, that looks at {Today’s Most Shared Post} on the tPosts] 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!

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


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

Reply


Terms & Conditions