Finding the latest activity description

[Liz_Fletcher]

Hi!

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.

Any tips would be very much appreciated

[Mohamed_Swella1]

Hi @Liz_Fletcher,

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.

Let me know what you think.

BR,
Mo

[Liz_Fletcher]

Thanks @Mohamed_Swellam

So here is the Activities example. The next activity is just defined as the one that is after today. Each has a relevant date.

And here is where it connects to the streams. You’ll see that I have been able to get the date from the roll up.

Happy to give the script block a go. Will just need some guidance

For context, this is all public info on regulations and government things.

[Mohamed_Swella1]

Welcome @Liz_Fletcher.

Maybe one of the others more experienced community members can chime in with the script block solution as this is not my cup of tea :slightly_smiling_face:

For me, I would use a zap to do it.

[Liz_Fletcher]

Would you be able to direct me to somewhere with more info on how you would use a zap?

[Mohamed_Swella1]

Actually, you can check this post for many script ideas that you can implement.

The project scheduler one in particular seems handy.

[ScottWorld]

Hi @Liz_Fletcher,

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:

Airtable

Streams & Activities - Airtable

Explore the "Streams & Activities" base on Airtable.

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:

IF(IS_AFTER(Date,NOW()),"Incomplete","Complete")

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:

MIN(values)

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: