Automation of linked tables to create a Summary table

Welcome to the community, @Coach_Sharm! :grinning_face_with_big_eyes:

Airtable does not automatically create links between tables. This normally must be done manually, but using Airtable’s new automation features, these links could be added automatically, but setting up the automation will take some careful planning. Something similar could also be done using an integration service like Zapier or Integromat.

Would you be able to share more specific details about your base? That would allow us to offer more specific guidance on how to set up the automation, whether it be internal (Airtable’s own automation features) or external (Zapier or Integromat).

I’m interested in this too!

My task base I’m trying to use an automation to move a linked field (two, actually…one for business and one for project) into an archived tab. I’d either like to

1. have the linked field just turn into a drop down
   or
2. have the linked field turn into a new linked field that would ultimately link back to my junction table-- somehow.

Honestly I kinda need both options because I want a tab for MY tasks and to also create a tab for tasks that are delegated/waiting on/follow up to a different tab. I want the linked records to keep as records move from tab to tab, really.

I do need a solution to move a linked record into a dropdown, because honestly that works for the archived base.

BUT i also would like to understand how to take a linked field and make it move from tab to tab when the record is moved from tab to tab. I can’t seem to figure it out. I have tried some lookup fields, etc and can’t quite nail it.

The third goal is to have a tab of recurring tasks that are auto-moved to my task list on plans (IE every 30, 60, 90 days. I currently have this set up but I have to update the date field for it to work. I have a built on air script bookmarked but i haven’t circled back to it, yet.

But all of this needs those linked record fields to work on the separate tabs (or at least a drop down for the time being). I have considered only having the linked records be for open tasks, potentially.
You can see in my archived tasks the dropdowns aren’t working right. I’m not sure what i’m missing.

@Justin_Barrett

I’m interested in this too!

My task base I’m trying to use an automation to move a linked field (two, actually…one for business and one for project) into an archived tab. I’d either like to

1. have the linked field just turn into a drop down
   or
2. have the linked field turn into a new linked field that would ultimately link back to my junction table-- somehow.

Honestly I kinda need both options because I want a tab for MY tasks and to also create a tab for tasks that are delegated/waiting on/follow up to a different tab. I want the linked records to keep as records move from tab to tab, really.

I do need a solution to move a linked record into a dropdown, because honestly that works for the archived base.

BUT i also would like to understand how to take a linked field and make it move from tab to tab when the record is moved from tab to tab. I can’t seem to figure it out. I have tried some lookup fields, etc and can’t quite nail it.

The third goal is to have a tab of recurring tasks that are auto-moved to my task list on plans (IE every 30, 60, 90 days. I currently have this set up but I have to update the date field for it to work. I have a built on air script bookmarked but i haven’t circled back to it, yet.

But all of this needs those linked record fields to work on the separate tabs (or at least a drop down for the time being). I have considered only having the linked records be for open tasks, potentially.
You can see in my archived tasks the dropdowns aren’t working right. I’m not sure what i’m missing.

@Justin_Barrett

Instead of moving tasks between tables, I strongly suggest looking more deeply into Airtable’s views. Using views, all of your tasks would stay in the same table, but only your personal tasks would be visible in one view, delegated tasks in another, follow-up tasks in another, etc. Here’s where you can read more about views:

I have something like this set up in my task base, which I walked through in the BuiltOnAir podcast a couple months ago. I have three different task types: Single, Recurring, and Scheduled. With Scheduled tasks, and I can specify an interval using combinations in a multiple-select field:

I have an automation that runs every day to (among other things) refresh old Scheduled tasks to their next date. It might be possible to adapt this to your task system. I’ve been thinking about sharing this portion of my system more broadly, but I won’t be able to work on it very soon because I’ve got a lot on my plate right now. Anyway, it’s definitely possible to something similar to this with your base. If you only have a few different intervals, the code wouldn’t need to be nearly as complex as mine.

I don’t want the views. I know how to use them in depth. I need the tabs. It’s too much in one view. I want the delegated stuff separate from my daily task list. I’ll review the rest of your post in the next couple days.

But that’s one of the primary uses of views: to take a large collection of records and only see a specific subset at any given time. If you have several views configured in your task table, then it shouldn’t feel like there’s too much in one view because you’re never seeing the entire task collection at once. One view would have your personal tasks, another your delegated tasks, another the tasks that are waiting for feedback, etc.

Views can give you that far more easily than moving records between tables. With views, you wouldn’t need any scripts to move things around. If you delegate a task, it would automatically disappear from your personal task view and only be visible in the Delegated view. No code needed.

But that’s one of the primary uses of views: to take a large collection of records and only see a specific subset at any given time. If you have several views configured in your task table, then it shouldn’t feel like there’s too much in one view because you’re never seeing the entire task collection at once. One view would have your personal tasks, another your delegated tasks, another the tasks that are waiting for feedback, etc.

Views can give you that far more easily than moving records between tables. With views, you wouldn’t need any scripts to move things around. If you delegate a task, it would automatically disappear from your personal task view and only be visible in the Delegated view. No code needed.

But that’s one of the primary uses of views: to take a large collection of records and only see a specific subset at any given time. If you have several views configured in your task table, then it shouldn’t feel like there’s too much in one view because you’re never seeing the entire task collection at once. One view would have your personal tasks, another your delegated tasks, another the tasks that are waiting for feedback, etc.

Views can give you that far more easily than moving records between tables. With views, you wouldn’t need any scripts to move things around. If you delegate a task, it would automatically disappear from your personal task view and only be visible in the Delegated view. No code needed.

Hi @Caroline_Ritenour, if you have a budget for your project and you’d like to hire an expert Airtable consultant to help you out, please feel free to send me a private message! :slightly_smiling_face:

I need it separated. It’s how my brain works. I fully get views. They work great for a lot of things, but not for this.

I have one tab that ALL the data comes into. I want to review an inbox tab, daily. Then decide from there where it goes. Triggered by an automation whether it goes to my to do today tab, my ‘open tasks’ tab, or ‘delegate to others’ tab.

Mostly—adhd and if there is too much information, i get overwhelmed. So i’m trying to build a base that doesn’t just have an ongoing building of tasks in one giant tab. It’s too much. then i ignore the base. I built it the way you are suggesting with multiple views and having a giant list of multiple views doesnt’ work for this gals brain. at all. so hence, the tabs. I can have a delegated tab and views for each person, but it doesn’t affect MY to do list tab, etc.

Also multiple other reasons behind it, but I want the items that aren’t mine out of my task list. I don’t mind the scripts and automations. But i need it out of my lists and onto other lists. Views get to be too much. Especially when delegating out to 10 other people. My husband, for example, needs his own tab. He messes up my tab. I’ve got it down where i Can make automations move from base to base with a few flows and a script to delete once the automation to move triggers. It works great. But those linked record fields are where I am stuck. I even tried a "ARRAYJOIN({field}) trigger to remove the string, but it doesn’t seem to be working.

Understood. Thanks for the clarification. Just to make sure that I’m completely clear on what you want to do, you have task records that you want to move between different tables, but those records contain links to other tables, and those links need to remain intact as the task records move. Is that the goal?

If so, I’m picturing a setup where you have a button field on each task table, with the button labeled “Move Task”. Clicking that would kick off a script that asks you to choose a target table. Once a target table is chosen, the script would create a new task record on the target table, copy all data from the selected task (including the link) into this new record, then delete the old task. With this setup, one script could handle moving tasks between any of your task tables.

I’m happy to build the script, or guide you in its construction, whichever you prefer. However, I’ve had to make some significant schedule changes, and I’m scaling back my forum time considerably. I’ll revisit this conversation on Monday to see how you’d like to proceed.

The workaround I created so far… from the inbox i select from a DROPDOWN list (Two separate) the business and then the project name. From there, I change the status from “inbox” to where it should go… IE “Caroline’s tasks today” “Carolines task list”, “Delegate” and "waiting on " tabs. It moves to the correct view based on the status. IE if i choose delegate, it drops into the delegated view.

For futher setup, in each table, I have the same dropdown field.

When I use automations from airtable, I have it set so the dropdown field gets ‘pasted’ into the link field each time, and then i use another automation line to ‘paste’ the same thing into hidden dropdown lists on each table for biz and project. Then i have a script that deletes it from the original table.

So i have several automations set up that are basically new record–create record–delete script flow.

So, from inbox if i change status to ‘carolines tasks today’ it just moves it to my task list for the day.
I also have an automation set up in my ‘today’ tab that once its complete, it moved to the completed view, automates to the archived task tab, and then deletes from my tab with a script. I think i’ve kinda got the bones outlined for all of that and it seems to work.

I have different columns in ‘business’ and "project’’ tabs that link, one for each tab. That way at a glance i can see the records from each tab that are ‘open’.

I did figure out the drop down automation/linked table thing after I posted earlier today, so that is a bonus!
It’s a lot of automations to have set up , but at the same time, its not that bad ONCE they’re set up, it just runs.

I also have email parser set up to forward emails to my inbox. I have a couple parser rules (i have multiple email addresses) so if it comes from my one email, it adds that business name to the inbox dropdown for business, etc. It requires me inputting the correct data

I also have email parser set up to forward emails to my inbox. I have a couple parser rules (i have multiple email addresses) so if it comes from my one email, it adds that business name to the inbox dropdown for business, etc. It requires me inputting the correct data from my inbox before moving it to other places, but man, its been a lifesaver. I also have ‘parking lot’ in my inbox so if its just something that’s an idea and i need to hold, it goes into that view.

Where i’m still stuck (but haven’t yet explored)
I’m still stuck on the repetitive tasks. I just created an account on any.do (its cheap!) and created an automation to link a task from my “capture” list there to my airtable inbox. It’s connected to my google assistant, so i can say “hey google, add call client 123” to my capture list, and then it ends up in my airtable inbox. I was hoping repetitive tasks could be built thru any.do as well but it looks like that may not work, although i sent them an email about it to see if they knew how to make that happen.

I don’t think i need a button to run the scripts for the automation part. :slightly_smiling_face: But super great idea there.

Essentially i’m just trying to build a GTD system that automates repetitive tasks and helps me take everythign from all over, work in one place, and then keep track of the time I spent on tasks. BTW your prior button with start/stop works great, only thing I hate is the side block panel opens and i’d prefer to keep it closed. Too much open is hard for me. But i’ll get there! :slightly_smiling_face: I may figure out a way to create a website view or something that runs but doesn’t show all of it. I’ve thought about that, too… kinda make my own dashboard that way.

I appreciate your time/help/advice, as always. Ive learned a lot reading througgh a lot of your stuff !

Thanks for the detailed update. I’m glad to hear that you have some automations working for you to move things around as you like.

Here’s how I handle repetitive tasks. My task setup is quite different from yours, but this might be adaptable to your setup.

I have a multiple-select field where I choose the frequency that I want the task to repeat. Mine is fairly complex, and can handle lots of different combinations, but the basic concept could be adapted to a simpler list of choices:

Every night at midnight, an automation is triggered that runs a script. One of the things that script does is look at these “Scheduled” tasks (as I call them) to update any that are past their due date to the next scheduled date based on their specified frequency. Looking at the example above, if that topmost task was last due on Saturday, and the automation is running at 12 am on Sunday (past the due date), the script would set its next due date to the following Monday.

In my case, I’m just reusing the same task record over and over again, with the script updating its due date per the specified frequency. Some people prefer to delete completed tasks and add new ones, though, which would require a slightly different setup. Either way, something like this could probably be setup with your base.

I’m considering an update that would turn the timing on/off via a checkbox field instead of a button. That could trigger the script via automation, which would mean the sidebar stays hidden. If that sounds like a preferable option, let me know and I’ll make sure you are notified when I post the update.

I’m also adding an option to review and adjust the captured time if it passes a certain threshold, in case the timer is left “running” overnight and you need to subtract X hours from the captured time. However, that option wouldn’t work if the script is triggered via automation because it requires the sidebar to be open, so there’s a tradeoff. Perhaps I’ll make a “review” script that can be run manually to display all captured time blocks and allow any one of them to be tweaked as needed. The more I think about this thing, the more ideas come to mind. :slightly_smiling_face:

Both of your timer updates sound great! :slightly_smiling_face:

Ahhh i think i’m onto a solution.

In My REPETITIVE tab:

1. Drop Down field for ‘how often’ being Weekly, Monthly, Quarterly, Every 2 Months, etc.
2. Formula field called “Frequency in Days” and used a switch function to make weekly 7, monthly 30, etc etc.
3. Formula Field “Due Again On” with a Date Add based on the drop down
4. Date Task Created Field (Just record created on date)
5. Last Updated Field used to calculate the next due date
6. Created View 'automate to task list

Created Automation

1. When record enters view “Automate to Task List” it creates a new record on my daily task list tab.
2. Then the automation creates a new record in my **REPETITIVE ** tab, essentially copying all the fields I want to stay the same.
3. Then it takes the date the record was created and inputs that in the automation as “last updated” (to calculate the next due date

Script to Delete Record
Wrote simple script to delete the record that was automated to my task tab once the automate to task list happens.

I still have the linked records not moving during automation issue (I like having a summary table with different columns based on which tasks are in which base) but my work-around has been to have a drop down that matches. Inputting into the dropdown once and using the automation to connect that same dropdown to other dropdown fields doesn’t seem to be too terribly big of a hassle.

Question:
What about tasks that I want to say, show up on the first Sunday of every month? Or the third Thursday of the month?

Weekly automations are easy, since the 7 day cycle will always repeat the same day of the week.

I was trying to think, too, how maybe (potentially a different view with another automation step if necessary) you had a list of tasks that maybe showed up on specific DATES IE: the 15th of each month. (I’m thinking this would be AWESOME for bills, even!) Is there a way to make a date/time formula for that? I could create a dropdown that had categories such as “Cycle in Days” (what I have now), “Cycle on Specific Date” (IE 15th) and a drop down that was "Cycle on day each month. (third thursday). I’d say cycle on specific date would be the one i’d most likely want to tackle. I imagine for this I’d create separate formula fields that would do the calculations and the ‘view to automate’ would reflect those columns.

Thank you again, for sharing your thought process! It helped me at least get mine started!
I keep hoping that I’ll be able to find something that would easily do repetitive tasks that would integrate (IE Google Tasks) but I couldn’t find an easy way to update the google task as completed automatically once it pulled into airtable.

I also just saw zapier has a beta integration with google assistant. It’s a little wonky still but essentially you can tell google assistant to add something to your base thru zapier. I wish Google Tasks synched with google assistant or that google keep synced with zapier, because that would be all I would need to eliminate needing something like any.do to take a voice input and put it in my inbox on airtable.

Ahhh i think i’m onto a solution.

In My REPETITIVE tab:

1. Drop Down field for ‘how often’ being Weekly, Monthly, Quarterly, Every 2 Months, etc.
2. Formula field called “Frequency in Days” and used a switch function to make weekly 7, monthly 30, etc etc.
3. Formula Field “Due Again On” with a Date Add based on the drop down
4. Date Task Created Field (Just record created on date)
5. Last Updated Field used to calculate the next due date
6. Created View 'automate to task list

Created Automation

1. When record enters view “Automate to Task List” it creates a new record on my daily task list tab.
2. Then the automation creates a new record in my **REPETITIVE ** tab, essentially copying all the fields I want to stay the same.
3. Then it takes the date the record was created and inputs that in the automation as “last updated” (to calculate the next due date

Script to Delete Record
Wrote simple script to delete the record that was automated to my task tab once the automate to task list happens.

I still have the linked records not moving during automation issue (I like having a summary table with different columns based on which tasks are in which base) but my work-around has been to have a drop down that matches. Inputting into the dropdown once and using the automation to connect that same dropdown to other dropdown fields doesn’t seem to be too terribly big of a hassle.

Question:
What about tasks that I want to say, show up on the first Sunday of every month? Or the third Thursday of the month?

Weekly automations are easy, since the 7 day cycle will always repeat the same day of the week.

I was trying to think, too, how maybe (potentially a different view with another automation step if necessary) you had a list of tasks that maybe showed up on specific DATES IE: the 15th of each month. (I’m thinking this would be AWESOME for bills, even!) Is there a way to make a date/time formula for that? I could create a dropdown that had categories such as “Cycle in Days” (what I have now), “Cycle on Specific Date” (IE 15th) and a drop down that was "Cycle on day each month. (third thursday). I’d say cycle on specific date would be the one i’d most likely want to tackle. I imagine for this I’d create separate formula fields that would do the calculations and the ‘view to automate’ would reflect those columns.

Thank you again, for sharing your thought process! It helped me at least get mine started!
I keep hoping that I’ll be able to find something that would easily do repetitive tasks that would integrate (IE Google Tasks) but I couldn’t find an easy way to update the google task as completed automatically once it pulled into airtable.

I also just saw zapier has a beta integration with google assistant. It’s a little wonky still but essentially you can tell google assistant to add something to your base thru zapier. I wish Google Tasks synched with google assistant or that google keep synced with zapier, because that would be all I would need to eliminate needing something like any.do to take a voice input and put it in my inbox on airtable.