So I have a main table which has a series of streams of work. There is a second table with ALL the activities related to those streams. The tables are linked such as on the main table it brings in ALL the activities in a string.
I want to bring forward just the next activity for each stream. The roll up function works for the date but I can’t seem to get the text description in.
I basically need to say. Of all the activities related to this record, give me the next one. But because they are in different tables, I’m stuck.
Welcome to Airtable Community! :slightly_smiling_face:
This seems like a job for the Script Block.
If you do not want to use the script block, please share screenshots from both tables so that we can help you better.
For example, how is the next activity defined? Is there a numbering system for example so that we know which activity is next? You can use Zapier to do this, which should be easy to use.
So basically when a new activity is finished, add a checkbox saying it is completed, which takes it to a view called Task Completed (this will be used to trigger the zap). The zap then should search the Activities table for the next Activity, then create a new record with this activity linked.
I think it would be easier to do a variation of what @Mohamed_Swellam recommended earlier.
Since your next activity is always the very next activity that comes after today, I would simply use that easy logic to help you solve this dilemma.
Because Airtable is not a true relational database system, it takes quite a few formulas back & forth between the streams table and the tasks table to make this happen automatically for you. I’ve created a sample base for you that you can copy to your own workspace here:
Since we know that your next activity is always the very next activity that comes after today, I created a formula in the Activities table that tells you if the activity is after today or not. The formula looks like this:
Then, back in the Streams table, I created a Rollup field that shows you the very next “Incomplete” date. To do this, the rollup field uses the new “Conditional” feature which lets me tell Airtable to ONLY rollup the “Incomplete” dates. Then, I told it to show me the EARLIEST (or MINIMUM) of incomplete dates, with this formula:
Then, back in the Activities table, I looked up this minimum value from the Streams table using a lookup field.
While still in the Activities table, I created a formula field which compared this lookup field to the date of the activity. If they match, then it results in the word “Yes” to indicate that it is the very next activity for that stream.
Finally, back in the Streams table, I added a final Lookup field which only returns the “task name” if the value of its formula from above equals “Yes”.
You can check out the sample base to see how I’ve set this up.
I would be curious to know from anyone if there is a slightly more efficient way to solve this dilemma. But this is how I solved it for you, and I think this is one of the most efficient ways to solve this in Airtable.
Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face: