I wanted to create an archive button that will move all of the rows in a table with the status column filled with done to another table. I have two tables in the same doc. One is Bugs and Bugs Archive. I want to press a button that will go through the Bugs Table move the rows with status done to the Bugs Archive. Can anyone help me with the formula for this? I was having trouble with coda formulas. I don’t think there is a move formula and it needs to be copied then deleted. I also don’t want this to be automated.
Hi @Paul_Camacho and Welcome to the Community !
It is completely feasible by using a formula looking like this in the
Action of your button :
(See the quick sample doc below )
Bugs.Filter([Bug Status]=Done).FormulaMap( RunActions( AddRow( [Archived Bugs], [Archived Bugs].Bugs,CurrentValue.Bugs ), DeleteRows(CurrentValue) ) )
What it does is :
Step 1: Filter the table
Bugs to find the rows where the
bug has been marked as
Done, which is returned as a list of rows
Step 2 : For each (FormulaMap()) items in the list (i.e.: the list of the previously filtered rows where the
[Bug Status]=Done. Each specific
Bug in that list being represented by
CurrentValue (more precisely: each specific
Bug is stored in
CurrentValue)) run those actions (RunActions()) :
- Add a row in the table
[Archived Bugs]and in the field
[Archived Bugs].Bugsput this very specific bug
CurrentValue.Bugsfrom the list of all
AddRow( [Archived Bugs], [Archived Bugs].Bugs,CurrentValue.Bugs )
- Delete all the rows where
To illustrate, here’s the sample
As I personally know ()
FormulaMap() isn’t the most easiest function to grasp, here’s a post that might help/interest you
Now, depending on your setup and the future of your doc, in terms of performance, this could become a problem on the long run as each time you’ll click on that button, the button will need to go through the whole table to pin-point the appropriate rows to move…
If you intend to keep this table small, clean and tidy, with very very few rows it should work just fine but if you envision a
Bugs table with hundreds of rows this button might get a bit slow .
What I could suggest you here is to use a filtered connected view of your
Bugs table instead of moving rows from one table to another.
The main/master table would be the
Bugs one, in which the
[Bug Status]=Done are filtered out (either by using an Interactive filter or a more classical one in the table’s options. In the sample below, I used an Interactive filter on that table)
The view would be your
Archived bugs in which any status that is not
Done is filtered out .
(In my sample, I chose a “classical” filter in the table’s options)
Another potential option (generally the best solution so far, still in terms of long term performances) would be to use Cross-doc
Hope this helps !
Thanks for the advice with the formula. This was a good start. I’m still having some trouble with the formula to get the row to copy to another table and delete. I am not sure if it is a bug or I if am doing something wrong. Here is a screen shot of an example table I built.
The formulas I am using for a few of the columns are listed below:
If(thisRow.Upvoters.Contains(User()), “”, “”)
Action Modify Row
If(thisRow.Upvoters.Contains(User()), thisRow.Upvoters.Filter(CurrentValue!=User()), thisRow.Upvoters.Splice(0, 0, User()))
Count Users for User Badge
Reactions column with the show people selected.
People column that allows multiple people
And this is the formula:
[To-do test table].Filter([To-do test table].[Done?].contains(true)).FormulaMap(
AddRow([To-do test archive table],[To-do test archive table].[Task name],[To-do test table].[Task name],[To-do test archive table].Assignee,[To-do test table].Assignee,[To-do test archive table].[Due date],[To-do test table].[Due date],[To-do test archive table].Type,[To-do test table].Type,[To-do test archive table].[Done?],[To-do test table].[Done?],[To-do test archive table].Vote,[To-do test table].Vote,[To-do test archive table].Upvoters,[To-do test table].Upvoters,[To-do test archive table].[Questions?],[To-do test table].[Questions?],[To-do test archive table].[Upvote Counts],[To-do test table].[Upvote Counts]
If I hit the archive button it ends up activating the vote button and not moving any values to the other table or deleting the row.
I’m not sure what the issue is or what I am doing wrong in the formula. But I did noticed in the formula when it is in the formula editor the columns of the two tables looks like they are different types. For example, one is a text column and one is a checkbox. When I look at the column type in each table they are both the same. Not sure why this is happening in the editor or if it is causing the problem?
I can share the doc I made for this if it helps. I just didn’t want to post a link to the doc on a public forum. Sorry, I had more screenshots but the forum doesn’t let new users embed more than one.
Ah… so the Archive button became a push button …
Let me run some tests, now that you’ve added some useful info ( )… I’ll get back to you a little bit later !
Hi @Paul_Camacho !
So, there are indeed mistakes in the
Action formula of your
[To-do test table].Filter([To-do test table].[Done?].contains(true))
This will not return a list of tasks where the checkboxes
[Done?] are checked.
Instead, it will just return the list of all tasks in your table (if there’s at least a checkbox checked in that column).
To filter your table, you need to understand that a table is a list of rows and the values you have in your diverse columns are all stored in a row “inside” the value of the column you chose as
This specific value in the
Display column for each row in a table is the
reference of that row in the table which can be used to access the other values, for that row, in the other columns.
Each specific value for each specific column in each specific row is stored as a
In your case your display column is the column
[Task name] so if you type somewhere on your canvas something like this :
You can see that it returns the list of all the the rows in the table
[To-do test table]
Now, from that list, all we want are the tasks where the checkboxes in the columns
[Done?] are checked. In the sample below, those tasks are
Task 1 and
Task 3 .
Adding the filter, the formula will now look like this :
As you can see, the filter check the
CurrentValue.Done? to return the appropriate list of rows from the table
[To-do test table].
In other words, it takes the whole list of rows in that table and for each specific row, it goes and see what’s the
current state of the checkbox in the column
[Done?], what is the
CurrentValue of the checkbox
[Done?] for each row in that list…
And what the filter returns here is a “new” list of rows where the checkboxes are checked on which
FormulaMap() will later act
Note that because you used checkboxes and checkboxes are
booleans (meaning they can only have 2 values :
False ) you actually don’t need to precise the
true part… It’s implied .
(I hope I’m clear enough … This is not easy to explain )
Your filter on the other hand, doesn’t check the
It checks if in all the values in the columns
[To-do test table].[Done?] there’s at least a
true value. In other words, if at least one checkbox is checked. As in my sample I have 2, it returns all the rows in that table regardless of state of the checkboxes . (More info about Contains()).
Filter() you could have written it like this too though :
or even using
Contains() … as long as the filter checks the
That’s for the filter .
(Sorry, I know it’s long )
So now that we have the appropriate list of rows where checkboxes in column
[Done?] are checked, we can apply
FormulaMap() to add and delete what’s needed where needed .
Something I should mention is that by adding your
Vote button in the
Archive button will effectively push it. (This is how you can create
A button is not a value per say, it’s a type of column. .
If you want it in your
Archive table, you should re-create it there
The other thing is that you can’t “move” the value contained in the column for a specific row if that value is obtained by formula because this value relies on other values from other columns.
(which is the case for the values in your
[Upvote Count] column )
Like for buttons, you should re-create that column, as it is, in your
Once the needed values for the formula to work will be added to the table by the button, the formula will work as it did before … but in the
Archive table .
The mistake you made with the
FormulaMap(AddRow( ... )) is the “same one” as in your filter (well, not exactly the same one, but the similar one) and once the button is clicked (after correct the few trouble I’ve just mentioned) it returns something like this :
Because, as said earlier, the filter doesn’t works (as it returns the list of all rows if at least one checkbox is checked) and also because of what you asked to the
For example this :
AddRow( [To-do test archive table], [To-do test archive table].Task,[To-do test table].Task, ... etc ... )
This, as you can see on the screenshot just above, returns :
For the 1st row added: Task 1,Task 2,Task 3 For the 2nd row added: Task 2,Task 3 For the 3rd row added: Task 3
If I had to “translate” what your
FormulaMap() does on the list of rows coming from the filter I guess I could say something like :
For each rows in the “filtered” list of rows, “write” all the "
[To-do test table].Task available in that column"
So for the
Task 1 in the “filtered” list of rows, it writes all the tasks. For
Task 2 there are only
Task 2 and
Task 3 left… and finally for
Task 3 what’s left in the column is
Task 3 .
Once again, we actually need to take the
CurrentValue.Task and add this value where needed in the
The corrected filter return a list of tasks (a list of rows) where each specific checkbox in the column
[Done?] is checked.
For each one of these rows we need to “move”, we need to add the very specific
Task for that row stored/represented by
CurrentValue.Task (in this case) .
To be complete (At least trying to ):
The filter now returns the entire list of rows from the table
[To-do test table] where
[Done?] is checked. On top of that, by using
FormulaMap() we ask that :
For each specific task (row) in that list, retrieve each specific value from the column
Task in the table
[To-do test table] and add it as the value in the column
Task in the
Archive table for this very specific task in the list.
(I hope, that makes sense )
In this case : the
CurrentValue.Task for the
Task 1 is
Task 1, for
Task 2, etc…
It then will do the same for the other columns .
DeleteRow() will work similarly except that
CurrentValue in this case, is each specific
Task in the list of filtered row (in other words, the whole row).
- In “summary” :
Your filter gives you a list of tasks (a list of rows) and each specific task in that list is represented by (stored in)
Each specific value contained in each specific column in the table for each specific task in the list can be found in
CurrentValue.[Name of the column]
So to close this way too long answer (Sorry again , the
Action formula in your button should be looking like this :
[To-do test table].Filter([Done?]).FormulaMap( RunActions( AddRow( [To-do test archive table], [To-do test archive table].Task,CurrentValue.Task, [To-do test archive table].Type,CurrentValue.Type, [To-do test archive table].Upvoters,CurrentValue.Upvoters, [To-do test archive table].Question,CurrentValue.Question, [To-do test archive table].Assignee,CurrentValue.Assignee, [To-do test archive table].[Due Date],CurrentValue.[Due Date], [To-do test archive table].[Done?],CurrentValue.[Done?] ), DeleteRows(CurrentValue) ) )
And here’s the sample
I left the “not working properly”
Archive button so you could compare both buttons. I also couldn’t fully test how it will work for the
Assignee column as I didn’t have other people to add beside myself . The
Due Date column might need some corrections as in your screenshot, your
Due Date column seems to be a select list of some sort… (I used in my sample a
Now, I still think using views will be better on the long run but well… That’s just a thought
I’m sorry for the length (again ) but I hope this helps