So I’ve got 2 tables I am using for a tracking document for my business. Because the larger a table gets (1000+ rows of tracked items) you get significant slowdown when adding any new rows to the table, I am employing a system when I move any already Invoiced items from the Master Table to the Archived Table. Imagine a setup like this.
With the help of some excellent Coda staff I’ve already figured out a way to set an automation to automatically move any rows that have their status switched to invoiced from the Master Table to the Archived Table by essentially copying each column one at a time then deleting each column one at a time. That looks something like this:
However, when invoicing a few hundred tracked tasks at once the system slows up quite a bit and what I’d like to do is swap all my tasks from completed to invoiced, then at my own leisure, press a button and let coda sit for a few minutes while it copies all my lines of data from one table to the other. Yes I could also manually select all the invoiced rows and copy them and then paste them… I know… I know… I want to make a button do it.
So here’s what I’ve tried. First I made a button that adds the last invoiced item:
Then I’ve made a button that deletes the last invoiced item:
Then I’ve made a button which runs the first two buttons together:
BUT, this only does one line at a time. BOOOOOO.
What I want is some sort of For Loop. I’ve been looking at Formula Map but I have no idea what syntax to use to accomplish this goal. This for example adds two copies of the last line at once (since two items were marked as invoiced).
What I want is essentially this “for all lines in Master Table that have the status Invoiced, Run the Add Item button, then Run the Delete Item button. Test again. When no lines meet this condition, STOP”
Apologies that performance was not upto par and you are experiencing slowness with larger table.
Couple of options you could do
you could always have Master Table filtered to show non-archived rows. and then periodically move them into Archive folder (like by running automation once every day)
You could also write a formula like this and put it on button (this formula moves all records that are archived from one table to another and deletes the records in master table)
What this does is, it runs two commands (as there are two values in first list List(1,2)), For first command, it filters all records that are archived and joins all the fields and separate them by “:”, so in this example, it is joining Item and Status field by “:” effectively Master.Filter(Status="Archived").FormulaMap(Item + ":" + Status) - this produces and array of all records where each item in array is a concatenated TEXT of all fields. then it iterates this array and adds record into Archive Table and splits CurrentValue (which is a text of all fields from master table) and puts value into appropriate fields of Archive Table.
[P.s There is a bug here that requires this concatenation and splitting - once we fix it, it would be easier to set values on Archived table by doing CurrentValue.fieldName]
Second command,Master.Filter(Status="Archived").DeleteRows() deletes all records from master that are filtered.
@Krunal I’m not sure the syntax of that is working. I’ve tried a lot of variations against this today and can’t seem to get it to work.
what is “item” supposed to represent here?
I just don’t know what to replace “Item” with in the formula below to test this out. Why are there two items in the list? Not sure I understand both of those parts
Oh sorry I should have been more clear there - item and status both are name of the column. First pair refers to column on master table and second one refers to columns with same name on archive table.
Glad it worked - and apologies that it is this complicated. just FYI , if any of the value in the columns from MasterTable has : in it, it wouldn’t work. you would have to pick a different delimiter in that case.
I’ve already run into that and switched my delimiter to “&”.
Here’s an interesting next step though. I have dates in my charts that need copying over. If I have my Start and End columns both set to “Date” format when I copy this over, the data doesn’t get pasted correctly. All dates get overwritten as 1/1/1970. However if I set the target table (Archived Table) to have its Start and End columns by “Text” format when I copy this over, the data gets written out in a weird long form format (43473 means Jan 8th 2019 apparently) but then at least the data is preserved, albeit in a less than desirable format. I think this might be a Coda Bug??
Any idea why is it adding data from two rows in one.
I am trying to achieve similar result.
With Automation.
In Table One, on status Change
Update data from the current row to Table two. (If we can do a check for duplicates by name, will be great)
