Skip to main content
Solved

Using Rollups to Return Text, Specific Records

  • June 17, 2021
  • 5 replies
  • 104 views
A

Hi Airtable Community!

In my Airtable base, I’ve got two main tables: one for stories written by kids, and another for notes that volunteers write to the authors. These tables are related through a one-to-many linked record setup: there can be multiple notes for a story, but not multiple stories for a note.

I’m trying to make two rollup fields: one that displays the text of the first two notes a story receives and one that displays the text of every note after the first two. I’d like to email notes back to authors after they have two notes and then again when they’ve accumulated several more, and would use each rollup field as part of the automation for the email body. The additional wrinkle is that the rollup should only count notes that haven’t been flagged (and therefore shouldn’t be returned), and there are handwritten notes too.

My thought was to add an Autonumber field in the notes table–each note will have to have a higher number than the last. Then, the aggregation formula of the first rollup would find the second number in the array, and display the note text field of any note equal to or below that value. The other rollup would display note text for any notes with a value above that number.

And of course, the rollup would only display results where a note has not been flagged.

Am I on the right track here? Is there any easier way to pull this off? Here is an example table of my setup.

Best answer by Rohit_Gandrakot

They’re accepted through a form that is given to volunteers. They’re able to view the stories through a gallery view link and then submit their feedback via the form. Based on that submission method, what’s the best way to assign those serial numbers to notes? They would have to count up the same way for each individual story for the view to work, right?

So looking at the example in my base, when a note gets linked to story #5929, it gets assigned note #1, then note #2, and the process is the same for each story?


Hi Andrew, you have need 2 new fields and 1 new lookup field. 1 field called note number for notes. 1 field called, max note number for each story which is a roll up field with max of all note number from notes that are not blank. The max note number is also looked up in the note table from story table.

At the end of this setup,
Note table will have 2 new fields. 1 is new field called note number which is an integer. Second is a lookup field called max note number, which is looked up from story table.

Story table will have 1 new field called max note number which is a rollup field which shows maximum number of note number. Also only look for non-blank records. This is the field which is also looked up on note table.

Now create a view in notes table when note number is empty. In this view display note number, max note number. Now set note number = max note number+1.

This should help setup the entire note numbering process.

Let me know if you have further questions.

5 replies

R

Can you give more details on how the notes are entering the table in the first place? Are they manually added or are they accepted in a form? I think it may be beneficial to have a field called “Note number”, which specifies the serial number of the note within each story. So each story will have notes, 1,2,3,4,5 etc. This serial number within the note can be added manually or through automation depending on how the values are coming into the note table in the first place. After you get the note number, it should be easy to do the rest. Like have a view where note number <=2 and another view with note number >=2 and so on.

A
  • Andrew_Miller2
  • Author
  • New Participant
  • June 21, 2021

Can you give more details on how the notes are entering the table in the first place? Are they manually added or are they accepted in a form? I think it may be beneficial to have a field called “Note number”, which specifies the serial number of the note within each story. So each story will have notes, 1,2,3,4,5 etc. This serial number within the note can be added manually or through automation depending on how the values are coming into the note table in the first place. After you get the note number, it should be easy to do the rest. Like have a view where note number <=2 and another view with note number >=2 and so on.


They’re accepted through a form that is given to volunteers. They’re able to view the stories through a gallery view link and then submit their feedback via the form. Based on that submission method, what’s the best way to assign those serial numbers to notes? They would have to count up the same way for each individual story for the view to work, right?

So looking at the example in my base, when a note gets linked to story #5929, it gets assigned note #1, then note #2, and the process is the same for each story?

R

They’re accepted through a form that is given to volunteers. They’re able to view the stories through a gallery view link and then submit their feedback via the form. Based on that submission method, what’s the best way to assign those serial numbers to notes? They would have to count up the same way for each individual story for the view to work, right?

So looking at the example in my base, when a note gets linked to story #5929, it gets assigned note #1, then note #2, and the process is the same for each story?


Hi Andrew, you have need 2 new fields and 1 new lookup field. 1 field called note number for notes. 1 field called, max note number for each story which is a roll up field with max of all note number from notes that are not blank. The max note number is also looked up in the note table from story table.

At the end of this setup,
Note table will have 2 new fields. 1 is new field called note number which is an integer. Second is a lookup field called max note number, which is looked up from story table.

Story table will have 1 new field called max note number which is a rollup field which shows maximum number of note number. Also only look for non-blank records. This is the field which is also looked up on note table.

Now create a view in notes table when note number is empty. In this view display note number, max note number. Now set note number = max note number+1.

This should help setup the entire note numbering process.

Let me know if you have further questions.

A
  • Andrew_Miller2
  • Author
  • New Participant
  • June 22, 2021

Hi Andrew, you have need 2 new fields and 1 new lookup field. 1 field called note number for notes. 1 field called, max note number for each story which is a roll up field with max of all note number from notes that are not blank. The max note number is also looked up in the note table from story table.

At the end of this setup,
Note table will have 2 new fields. 1 is new field called note number which is an integer. Second is a lookup field called max note number, which is looked up from story table.

Story table will have 1 new field called max note number which is a rollup field which shows maximum number of note number. Also only look for non-blank records. This is the field which is also looked up on note table.

Now create a view in notes table when note number is empty. In this view display note number, max note number. Now set note number = max note number+1.

This should help setup the entire note numbering process.

Let me know if you have further questions.


Thanks @Rohit_Gandrakota ! I have a question for you, but first, for anyone following this thread, I want to rewind for a second and share my solution for numbering notes via Automation (something Rohit suggested).

  1. First, I made a Count field that counts the number of notes in the “Story Note” field, and created a lookup for it in the Notes table.
  2. Next, I created an automation that fires whenever a new record is created in the Notes table.
  3. The automation sets the note number to whatever value the count field has when the note comes in:

@Rohit_Gandrakota , where I’m lost is with retroactively assigning numbers to notes using the view in the notes table. Here, you’re suggesting that note number be set to max note number +1 in the view where any note has a blank integer value, but if all notes previous to the automation all have blank values, wouldn’t that set all of the notes to “1”? I ask because in reality, I have a couple hundred notes in my database. I can live with not being able to number these retroactively, but maybe there’s another way or I’m missing something?

And then just to close the loop here, it sounds like with this set up, I can then create a rollup in the Stories tab that displays note text for all note records whose note # value is less than or equal to 2?

R
R

Thanks @Rohit_Gandrakota ! I have a question for you, but first, for anyone following this thread, I want to rewind for a second and share my solution for numbering notes via Automation (something Rohit suggested).

  1. First, I made a Count field that counts the number of notes in the “Story Note” field, and created a lookup for it in the Notes table.
  2. Next, I created an automation that fires whenever a new record is created in the Notes table.
  3. The automation sets the note number to whatever value the count field has when the note comes in:

@Rohit_Gandrakota , where I’m lost is with retroactively assigning numbers to notes using the view in the notes table. Here, you’re suggesting that note number be set to max note number +1 in the view where any note has a blank integer value, but if all notes previous to the automation all have blank values, wouldn’t that set all of the notes to “1”? I ask because in reality, I have a couple hundred notes in my database. I can live with not being able to number these retroactively, but maybe there’s another way or I’m missing something?

And then just to close the loop here, it sounds like with this set up, I can then create a rollup in the Stories tab that displays note text for all note records whose note # value is less than or equal to 2?


You are right, this doesn’t work retroactively. But for old notes, you can manually update the note number. Once you do that, what you suggest at the end to filter for note number < 2 should work.

If you absolutely want to do this retroactively, you can add record created time for notes. You can then setup automation for only the earliest created record.

In this solution, note table will have created time.
Story table will have a rollup field with earliest created time among all notes with note number blank or not assigned.
Notes table will have a lookup field from story table which displays the earliest created time.
Note table will have a new filed, call it check, with formula of earliest time matches created time, then 1 otherwise 0.
Create a view to filter only for check field is 1.
This will only filter all notes with earliest creation time for a particular story.
Now your current automation should work to assign note number.
Once note number is assigned, the next earliest created note will be filtered in the above view for which automation applies.

But I highly recommend doing the retroactive stuff manually and staying with your current design.
This seems too complex and I always prefer simpler solutions especially in cases where manual work is one time activity.

Either way, let me know what you end up picking.
Also, let me know if you need any clarifications. :grinning_face_with_big_eyes:

Terms & ConditionsAccessibility statement