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.