Automation of linked tables to create a Summary table

[Coach_Sharm]

Hi!

I would like to ask is there a way that a Summary Table be created and automated. This Summary Table should be linked to other tables (four of them), so that when I add a new entry to the four tables, it would automatically be summed up and recorded in the Summary Table.

I already used roll-up type in the Summary table, linked it to other four tables, but will still manually input the link to the Summary table. Is there a way that this be done automatically?

Thanks for the help!

[Caroline_Riteno]

Thanks. I don’t have a budget. I’m actually only stuck on the part about keeping linked fields in between tabs and automating that. I’m a learner, so having someone else do the work doesn’t help me learn. I just ask for help when I"m stuck. Thanks tho!

[Justin_Barrett]

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.

[Caroline_Riteno]

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 !

[Justin_Barrett]

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.

Caroline_Ritenour:

I’m still stuck on the repetitive tasks.

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.

Caroline_Ritenour:

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.

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:

[Caroline_Riteno]

Both of your timer updates sound great! :slightly_smiling_face:

[Caroline_Riteno]

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.

[Caroline_Riteno]

I am testing out google tasks. I set up a recurring event yesterday and let it ride and it popped back up in my task list today! This might be the BEST workaround, depending on what data can be pulled in from zapier, because then i can keep a simple task list in my emails in google! If that works, it will be FABULOUS. I’m testing out some markdown text and things next. I also have multiple email addresses so i can schedule some of the details based on which google account it comes in from (for my linked data purposes). Honestly I may be overthinking the linked data tables and they really may not even be that necessary, just something i “feel” I need. shrug?

[Justin_Barrett]

Sorry for the delay. Life has shifted gears here, and I can’t spend nearly as much time in here as I used to.

Caroline_Ritenour:

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

I managed to get those working with my setup, though there are more calculations involved. Here’s the basic process based on how I set this up in my daily update automation script:

1. Find the next month
2. Find the first day of that month
3. Find the first iteration of the desired weekday as an offset from the weekday of the first of the month (this is the most complex part)
4. Add weeks to get to later iterations of that weekday

Caroline_Ritenour:

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?

Tasks that end up on the same day each month (e.g. 15th) are a lot easier to calculate. By default, if you add one month to a given date via DATEADD(), it keeps the day number the same. The only time you may hit an issue is with dates on or after the 29th. You could probably update your system to mark those differently and use a different calculation for them.