Duplicate an entire table via an automation

1

Hi there,

I have a table called Today’s Schedule. It uses a nightly automation to lookup the current day’s schedule from my Time Blocker, so that when I wake up in the morning my daily plan is waiting for me. It also includes columns for completing each task (checkbox), indicating progress made (progress bar), and providing notes on my progress (text). It works great; I love it.

Thing is: it refreshes nightly, which means that it’s sort of like GroundHog Day: every day I wake up to a fresh Schedule, and everything that happened the day before is poof gone. Not optimal, obviously.

So what I’d like to do is add a step to the automation, so that just before it refreshes, it copies the contents of Today’s Schedule into another table (let’s call it Schedule Archive). Specifically, I want to take the entire contents of Today’s Schedule and add it as a subtable to Schedule Archive, so that each day is held within a single row in the Archive.

I didn’t think this would be difficult, but I’m having a much harder time than I expected getting this to work.

  • I can, of course, use a Relation to copy the whole table over - but then the tables would be linked, and when Today’s Schedule refreshes, I’ll lose my Archive entry too.
  • So what I really need to do is Duplicate the table, so that only the values copy over, unlinked. But I’m not sure that there’s an automation or formula that can do this.

I can make this happen via an Automation if I AddRow from Today’s Schedule to Archive row by row by row by row by row. But this seems pretty inconvenient. So I’m hoping someone has a better solution?

2

Hi @Matthew_Shane,

adding another table to your doc everyday is a bad idea that eventually creates a huge mess.

Instead, try making use of codas features like filtered views! In this case, I would suggest to have one database for all tasks with a due date column. Today’s Schedule just has to be a filtered view of this table, where only tasks with today as due date are shown. Schedule Archive on the other hand is a view that only shows tasks which are NOT due today or in the future.

The automation should then duplicate all rows that had yesterday as due date and then set today as new due date, uncheck the checkbox for completing, reset the progress and so on.

Does this guide you into the right direction or do you need further help with formulas?

3 Likes
3

Thanks for the suggestion.

In this case, having the tables delinked is absolutely the way I want to go, so unless I’m missing another way to do this, I’m pretty sure I can’t use filtered views.

4

Why do you need them to be delinked???

I defintiely think you actually DONT want that

5

Okay, will have to think about this…

1 Like
6

Hahaha

Just trying to save you trouble in the long run. Give us more context on the end goal and entire workflow. What are you trying to accomplish?

Why do you want a de-linked table?

7

No, no, don’t get me wrong Scott, I appreciate the help. Didn’t mean to sound flippant.

It’s very possible that my issue is an inefficiency in my very first table design. But I’ll try to explain:

Table 1 is essentially a straight up Time Blocker. It has just a few columns: Time, Day, Week, Task, but quite a few rows (20 (half hour blocks) x 7 (days) x 6 (week variations) = 720 rows. I can filter by day & week and get a 9:00-9:00 schedule of time blocked events. Nothing fancy.

Table 2 (“Today’s Schedule”) isn’t absolutely necessary, but it’s a bit fancy and it simplifies things: instead of waking up every day and filtering Table 1 to the correct day/week, Table 2 provides a filtered view of Table 1 that only shows the correct time blocking schedule for each day. So on Monday it’ll only show Monday, on Tuesday it’ll only show Tuesday, and so on. I have buttons that let me choose which week it is. So I press “Week 1” and the correct day’s schedule comes up; I press “Week 4” and the correct day’s schedule comes up, etc. I’ve added extra checkbox, progress and notes columns so that I can track how well I stay on schedule (less well when I’m fuckin around with Coda!)

As an added advantage, Table 2 only has 25ish rows, and so it’s more manageable. Still, it’s 25 rows each and every day, and so if I wanted to save my Table 2 progress/notes, I’d very quickly end up with a very large table (close to 10K rows/year). Maybe that’s okay; maybe that’s the answer; maybe I just go with that and accept the 10K rows/year. (yes? is that what I should do?)

I assumed I didn’t want 10K rows/year, and so I was coming up with other solutions.

My current thought was that Table 2 won’t be designed to save the data at all. Instead, it will refresh every morning like Groundhog Day. But before it does, it would send the data nightly to a 3rd table (call it Archive) where the data would be stored long-term.

And this (I think) explains why I don’t want Tables 2 and 3 to be linked - because Table 2 erases itself every night. So I want the data sent to Table 3 prior to Table 2 getting wiped.

(and then finally, to prevent 10K rows, I was thinking that the Archive could be set up so that every row was a day, and the whole 25ish row schedule from that day would be added as a subtable within column 2).

Does that make sense?
Am I crazy?
Should I just accept 10K rows/year?
Are there better ways of accomplishing this?
Should I just move on to a different project instead?

Thanks for listening!
-Matt

8

Hi Matthew,

I agree with Scott, stick to one table, unless you absolutely, definitely have to have another table.

10k rows a year is not too bad. There is also nothing that prevents you from archiving the data older than six month, or 6 years… :wink:

Also there is no need to manually filter to the correct day/week.

Coda provides multiple ways to filter on the day:

image
image483×555 26.7 KB

But you can also play around with Formuas like IsoWeekNumber(dateTime) to get info for a specific week.

I am sure that Scott will also have some good suggestions. Many ways to skin the Coda.

9

Try using Today() or some smart formula filter to just use a filtered view of the main table. No need to erase itself or anything. You want a table that works as an archive so you can look at the data later or build something fun of the history. But that is not your working view, its just your main table.

From that main table you filter out the views you want to have, such as todays view, or selected view of you are up for selection. But again, this is just a view of the main table and nothing is deleted.

This way you find ways to build your data set so it works for all your needs.

10

Hey @Matthew_Shane !

Actually just built this pack - should solve your problem!