How do I create an archive button that will move all the rows in a table with status="done" to another table?

[Paul_Camacho]

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.

[Pch]

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

Bugs.Filter([Bug Status]=Done)

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()) :

1. Add a row in the table [Archived Bugs] and in the field [Archived Bugs].Bugs put this very specific bug CurrentValue.Bugs from the list of all [Bug Status]=Done

AddRow(
      [Archived Bugs],
      [Archived Bugs].Bugs,CurrentValue.Bugs
    )

Then

2. Delete all the rows where [Bug Status]=Done

DeleteRows(CurrentValue)

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

FormulaMap() : When and where?

Here is a small example. Imagine you have two tables (Table 1 and Table 2) and you want to copy all the row values from Table 1 to Table 2 (only from Name column in my example, but you can copy more columns). You can do it with a button using FormulaMap function. You “list” to use with the FormulaMap function will be all the rows in Table 1. Code for the button: [Table 1].FormulaMap(AddRow([Table 2],[Table 2].Name, ToText(CurrentValue))) Here is an example doc: Second button copies only …

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 !

[Paul_Camacho]

Hi @Pch,

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:

Vote Button
Button Label
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
thisRow.[Upvote Counts]

Upvote Count
Count(thisRow.Upvoters)

Questions?
Reactions column with the show people selected.

Assignee
People column that allows multiple people

Done?
Checkbox

And this is the formula:

[To-do test table].Filter([To-do test table].[Done?].contains(true)).FormulaMap(
RunActions(
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]
),
DeleteRows(CurrentValue)
)
)

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.

[Pch]

@Paul_Camacho

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.

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 !

[Pch]

Hi @Paul_Camacho !

So, there are indeed mistakes in the Action formula of your Archive Button…

1. The Filter()

[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 Display column.

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 CurrentValue.

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 : True or 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 CurrentValue.Done?.

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()).

For the Filter() you could have written it like this too though :

or even using Contains() … as long as the filter checks the CurrentValue.Done?

That’s for the filter .
(Sorry, I know it’s long )

---

2. FormulaMap() & AddRow()

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 AddRow() the Archive button will effectively push it. (This is how you can create Push Buttons)
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 Archive table.
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 :

Why ?

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 FormulaMap()

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 Archive table.

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, 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) CurrentValue.
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 Date field)

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