Efficiency when iterating

1

First post. Let me start by saying I am so greatfull to the people who have generously posted their ideas, and to the team who makes Coda. I can’t imagine making the tools I am using everyday at work now any other way.

The short version; should you try to build iteration into a single action, or is building the thing you want to iterate through once first a better way to handle the complexity?

Slightly longer exploration:
I made a calendar scheduling doc. It stores all these calendar events and created Googlecakendar events in parallel, and even let’s me update the Gcal events from Coda with changes I make from the Doc. It’s great. But it’s also hundreds of rows and the whole set of data is changing a lot. I wanted a way to backup entries as they changed.

So I got this idea to back up rows by writing to a “Log Table”, and do a simple comparison between the log and the current data to figure out which rows need to be archived; just compare the modified date in the original with the date of its log. If they don’t match, I need to grab the row. And I check if new items need to be logged by comparing all the log dates and the current data’s modified dates. So far so good.

But then I realized to write the rows, I needed to iterate. I wanted to write one row for each item that needed be recorded. And after working with a situation where I wanted to iterate before, I heard about the trick to use sequence() and use the numbers in the sequence to iterate through a list for you. But as I tried building it, I realized there was another I could accomplish the same goal without needing sequence.

  • build one button to update the next row that needs to be logged
  • build a button that counts how many updates need to be made, and push the first button that many times
  • disable the second button if nothing needs to be logged.

The reason I am posting here is I am about to set up a bunch of these to create running logs of most of the important data in my doc. And if I am going to be running this process hundreds of times, I wanted to see if anyone else has come across this sort of things and how you dealt with these sort of workarounds when it comes to iteration.

My button to iterate looks something like
Runactions(
Formulamap(
LogOfTable.filter(Data.modifiedDate!=modifiedDateLog), PushTheButton))

And the single update
Addrow(Log, dataColumn1, Data.filter(modifiedDate!=modofiedDateLog).first().dataColumn1, ect ect)

It’s a lot of adding rows no matter how you do it, but I am curious if there is any benefit in writing it out into a single button that can iterate once per entry to log, or if this work around is a viable alternative.

1 Like
2

Hi Robert, welcome to the community.

Unfortunately I cannot easily visualize your task — a sample doc would certainly help.

But if I understand it correctly — you need to only copy the rows that have something changed? Then my train of thought would be:

  1. Create a checkbox column “Is changed” with a formula that returns true if the row should be marked as changed. If you’re comparing against the latest row for that event in the log table, it would also be a good idea to look up that log row into the events table as well, instead of writing a Filter function in that “Is changed” formula.
  2. Make a button that copies just this row into the log table.
  3. Make the button disabled when [Is changed].Not()
  4. Whenever you need to copy any rows, just call Events.[Copy to log] on all buttons in the table — the disabled ones will be skipped.

That’s the cleanest solution I can think of. You don’t need Sequence/FormulaMap there (unless I misunderstood your task)

3

Hello Paul. Thanks for your reply.

I think that link should be enough for you to see an example of what I ma trying to do.

I think I understand how you are suggesting I approach this. It makes sense, right up until I hit the part where I “copy a row”. I’m currently using addrow() and the telling the function to go through each column and write the value if that row needs to be logged. But something tells me I am doing that part the hard way too.

I tried to use the AddOrModifyRows() function, but I would actually write the contents of thew row again each time there is a change, so addrow() is probably what I want.

If I can figure out the second item in your list, I think I can get that system working. Thanks again for your help!

4

I think I got it.

I only wish I could be a little more elegant with the function for the button that checks for changes, bit it really does get the job done.

Great solution. Thanks again Paul!

5

Hey Robert,

Regarding having to enumerate all columns when copying a row with AddRow() — I totally feel your pain. I logged a request a while ago:

I have the same need at the moment (make snapshots of a table with dozens of columns), and the only way I see to make it easier is to grab the table, copy it into a text editor, and with some find/replace/regex magic compose a formula out of it.

Re your button formula, what you can do is combine the ModifyRows+AddRow in a single action:

thisRow.ModifyRows(thisRow.LogAddress, TheLog.AddRow(
  Log.Datapoint1, thisRow.[Datapoint 1],
  Log.Datapoint2, thisRow.Datapoint2,
  Log.Datapoint3, thisRow.Datapoint3
))
1 Like
6

P.S. @Robert_Clay, Just posted the trick with how to quickly compose a formula to copy all columns:

1 Like