Automatically Archiving values in one table to another

1

Hi All,

I’d like to have a workflow in Coda that allows for a periodic (i.e. weekly) archiving of information from one table to another. My team has weekly “status updates” and are required to update these every week.

The ideal workflow in Coda would be as follows. I’m sure this is a combination of buttons and automations.

  • Every Sunday at 7pm EST an “Archiving” automation starts

  • Table X with the column “Last Week’s Update” has information from last week’s update. The value in this column is then moved to a separate table called “Table Archive” with a timestamp

  • The values in “Last Week’s Update” in Table X are then removed

  • Value in “This Week’s Update” is moved to “Last Week’s Update”

Here is a skeleton of the tables. Thoughts?

https://coda.io/d/Automated-Archiving_dTBn9QSaUzP

Thank you in advance

2

This is possible with Crossdoc and I have it working. If I have time I’ll create a template to show you how to do it. Basically you need to create your archive doc, then cross doc your table into it. Set some checkbox to detect if its past the archival date, and if so have an automation that adds the row to a table in the archival doc while simultaneously deleting the row in the originating doc.

1 Like
3

Awesome. Thanks @Johg_Ananda, I look forward to your solution.

4

Thank you very much. This is exactly what I was looking for.
I’ve been implementing a doc for the past 3 months to realize about this limitation of Coda. Tables can’t grow over a few thousands rows. I think the Coda’s website should be more upfront about this. There’s nothing wrong about it. I just think it should be better stated. I asked @BenLee about the best approach for archiving but he couldn’t say. Your answer showed me the light at the end of the tunnel. Thank you, Sir. I’m already experimenting with Cross-doc.

2 Likes
5

You say that you can “archive” with Crossdoc. But isn’t that dependent upon paying for the top tier of Coda? It is my understanding Cross Doc at the affordable for one user price won’t do diddly squat with CrossDoc other than pull in an exact copy of all the records of the table. So I found it 100% useless. If I have to pull in the entire table why bother separating the table out in the first place? Am I wrong in my understanding of Cross Doc?

6

hey @Susan_M_Davis I’m not sure as I’ve always been at the top tier. Seems like if you want that feature then you have to decide if its worth the $ for you.

1 Like
7

It would be nice if someone who knows that answer would confirm it. Yes, I understand. However, I’m just a single mother of 6. I use Coda for myself personally. I cannot justify that much money for one person. Their “doc maker” pricing system is fantastic for companies with lots of people and only a few who create things to be used. But it is not economical for single use by individuals who see a great product and want to use it to improve their life. So unless that changes, I’m confined to ignoring Cross-Docs. Hopefully, that will change in the future. Once all 6 of the kids are adults I might go back to work again outside the home and have income to spend on such things. But for now, that feature is a frivolous expense.

8

Hi, @Susan_M_Davis. Why do you need to archive your data? How many rows does your largest table have?
Could you please explain your use case? This way people can better help you.

9

We live in a remote area of Montana with only a small grocery store and no other places to shop other than a local hardware store. The nearest Walmart is 3.5 hours away in North Dakota. The closest other stores are 5 + hours away.

This means we order a LOT of stuff on line that is delivered. We also have difficulty with the local UPS and Fed Ex services misdelivering packages, losing them, etc. Nothing ever arrives on the day it is supposed to and I have spent hours trying to find packages that were left somewhere they shouldn’t have been.

To prevent losing something we ordered, I am tracking every order we place and every tracking number that is generated. I use the UPS, FedEx and USPS packs in a table to give me updated information so I don’t spend all day going to these services to see status changes, etc.

What I’m finding is using the packs forces Coda to refresh and I cannot find any way to have it only refresh tracking numbers that are not yet delivered. It seems packs will refresh the entire table. This winds up taking time for processing whenever any row is updated.

I’m probably not up to 1000 rows yet, but it could easily become that as Sam’s Club seems to choose to ship each item in an order in it’s own box with it’s own tracking number!

So I was thinking perhaps instead of keeping all of the tracking information in one table I could archive delivered tracking records into an archive table but keep them linked to the original order table. I was a database/software developer for decades so I like having access to old data. Should I ever need to know when something arrived or if I just wanted to run reports to see if our shipping services ever improve up here, I just like to keep my history of data.

I tried moving this to another document but since I cannot afford the $30 a month for the top level of Coda the Cross Doc doesn’t seem to be very useful. So I was considering making a table for delivered orders, moving the records once they are marked RECEIVED to this other table and then remove the rows that give the updates from Fed Ex, UPS, and USPS. Once they are delivered those rows are fairly irrelevance since I have the shipping date and the received date, I can ignore those fields of expected delivery date, status, etc.

I honestly haven’t looked to see how many rows my tables have. In my document, however, I also have several things I do daily. So by the end of the year I will have several tables that will have 365 rows. I expect I will want to move that data out. I haven’t yet decided if it would be best to copy my entire doc, save that copy as 2021 and then make the new one for 2022 and delete all of the 2021 items out of the new doc for 2022.

So I have an immediate need to move things out of one table to another and I’m considering how my doc will perform in the future.

Thank you.

10

HI Susan,

Is there a column that indicates the status? I assume it is possible to add a Complete checklist column to the table?

If either of those works, you could do an automation to either directly move the completed row to another table, and then delete it, or to use some button(s) to do it.

Regards
Piet

11

Yes I can figure out how to move the items from one table to another. The original question was creating an archive and the solution was using Cross-Doc. So I was asking for clarification on that as it is my understanding that you cannot do anything with Cross Doc other than PULL in a FULL table from another document UNLESS you have the top $30 a month level of Coda. At the $10 a month level, Cross Doc seems completely useless. I wanted to confirm that I understand correctly.

So it isn’t that I can’t figure out how to automate removing my rows to another table. I’m trying to figure out the purpose of Cross Doc.

If my only option with Cross Doc is to pull in a table, a full table, not a filtered table or view, then my document has an exact duplicate of that original table. What am I saving here? The doc will still be the same size and I cannot update it at all. So what would be the point?

12

Hi @Susan_M_Davis,

That’s a great use-case and thank you for the details on what you’re putting together.

For the issue of Cross-doc on the Free or Pro plan, yes, it has it’s limits and the idea here was to allow for customers to try it out first before upgrading. So it’s a taste of what is available and something we wanted to offer since there are a few caveats to it, like not being a 2-way sync. This way you can try your project on a smaller scale, then if that works, you can upgrade.

For your use-case, I don’t think you need Cross-doc at all. I think you can achieve archiving within the same doc and get the results you’re looking for.

The issue seems to be that syncing so many rows, especially unnecessary rows, is starting to take some time. So the idea is to only have the rows that are still in process sync while the others can be logged as data should you ever want to revisit items or even patterns.

  • The easiest setup is to have your main table that has the packages listed that you are tracking. This will have the pack columns and will be updating regularly.
  • Then create another table, not a view but another main table, in the same doc with the columns of data you want to retain.
  • Now, create a button in the first table that uses the AddRow() function and map all the columns you want to keep to the other table.
  • You can then wrap this button formula in a RunActions() formula and second action of DeleteRows() that will delete the current row after it’s added.
  • The next step is to add a checkbox column to the first table and set it to be checked if the package status contains anything except delivered, or any other final status result that may warrant archiving. You can use this checkbox as the “Disable if” for the button.
  • Now that buttons will be disabled if the package is still actively being tracked, you can set an automation to run daily to push that column button to archive any rows that are delivered.

This should keep your active table small enough to not worry about syncing time as well as keeping your data in an archive table within the same doc. With all of our performance enhancements, I’d expect this doc to run just fine for quite a while into the future, even with thousands of deliveries.

If you are curious about doing this with Cross-doc, here’s the same pattern I mentioned but using Cross-doc. The only change is that instead of adding a row to a new table in the current doc, we’re using Cross-doc actions to add a row to a table in a separate doc.

1 Like
13

Thank you for the clarification. Yes, your steps are exactly what I was planning to do. Since I can’t afford the $30 a month to make Cross Docs useful, it’s not really an option. But even if I did upgrade, I still see limitations in Cross Docs. I could move the row into the other doc, but I still think CrossDocs requires you pull in a copy of the entire table.

If CrossDoc could pull in a VIEW instead of an entire table, that could be useful. Or if you could FILTER what you want to pull in. The Google calendar pack is a great example. When setting up the pack I choose to filter what I want to bring over. So when looking at my calendar, I just have it pulling in the next 30 days. Anything before that is irrelevant. I either did it or I didn’t, can’t change that but knowing what’s coming up helps me plan.

If CrossDocs had that same filtering capability, I could see a lot of potential use. It just feels like it really isn’t quite ready yet. Like someone made the pizza crust but we’re still waiting on the toppings and for it to bake. :grinning:

14

I’m fairly certain that one can pull a view using a cross doc.

15

There are several aspects to Cross-doc and that is just one of them.

With Cross-doc, you can:

  • Sync a table from one doc to another doc
  • Sync a view of a table from one doc to another doc
  • Add a row to a table that is all it’s own in another doc with Cross-doc Actions

With Cross-doc actions, you don’t need a sync table at all. The example I shared above adds a row from a table in one doc to a table in another doc, then deletes that row in the first doc. So you have a true and disconnected archive.

If you do want to sync a view, Cross-doc uses scoped permissions so only the data that is displayed is synced over. This makes Cross-doc a secure way to share data to another doc. If you have a table of employees with email addresses and salary information, you could use Cross-doc to sync a view that hides the salary column over to a new doc. Then there is no way, even through a formula, for someone to access the salary column from the new doc.

So there are definite capabilities and useful advantages of Cross-doc. It might only be worth it if you need those aspects though. In your case, a single doc can definitely handle your setup, so not really a need to move things to another doc.

1 Like