I have a large table (~1000 rows) that I need to be archived to another table. Essentially, I want to have a sort of “closeout” feature where an admin user can click a button to kick off a workflow that automatically makes a backup copy of all the data from the Users table (I’m calling it Table A for this example) into an Archive table (Table B) then deletes the original data. This way, a record is kept of all the data for the users but they no longer will have access to the app because their account is no longer listed in the Users table. But I also have a group of users that I would NOT want to have removed from the Users table. To differentiate between the two groups, the ones I want to move have a UserID consisting of just numbers but the users I want to keep have a UserID that starts with the letter S (so some are “0000” and others are “S0000”).
It would be easiest to implement as a Workflow so that someone can use the app interface to accomplish this. A single button click that would move all the data over and delete the original data.
You don’t really have to send any data from a user-triggered workflow to do this, just use “Trigger Workflow” to allow your manual workflow to run.
Your manual workflow would loop through your Users table, find rows that match your condition, add them to the archived table, and then go back to delete the original row.
Though, I would just use a boolean column instead of the user ID method to deal with this.
I would suggest a completely different approach to this.
Create a separate table, lets call it Authorised Users. This table needs just a single column: Email address
Add a row to this table for every user that needs access to your App
Go to Settings->Access->Users, choose the “All emails in table” option, and select the Authorised Users table
The effect of the above will be that only emails that are listed in the Authorised Users table will have access to your App. Now when you need to remove a Users access, you simply need to delete their row from that table, without ever having to touch the Users table.
This is a much cleaner and simpler approach, in my view.
I agree with your thoughts here, it’s what I had thought of, too. But I don’t know what the triggered commands would be to go through this operation. If there’s 200 columns but I only want to carry over a specific 15 from Table A to Table B, how do I go through the operation?
1 - Create Row
2 - Copy Row from Table A, Columns 1 to 15 (note: they might not be concurrent in order)
3 - Paste Row into Table B, Columns 1 to 15
4 - Delete Row from Table A
In my specific case, I have ~1200 users and I want to archive the data for ~1000 of them when I trigger the workflow. Their data should be archived so that they no longer have access to the app, but I can still look up their information when I want to. And I want to be able to do this on a regular, annual basis.
For some background, I work with an educational camp (of sorts) that has around 1000 students and 100-200 staff. We want the staff to have year-round access to the app but the students only access it for the 1 week they are at the camp. After the camp finishes, I want to archive their data but NOT the staff data.
In your loop you would have an Add Row action to add original row data to another table, followed by a Delete Row action to delete the original row. That really should be it.
But still consider Darren’s idea as well. A table with only user email and type would be used for access to the app. The user table would still contain user data, but not be used for app access.
I would still recommend my approach, rather than messing around with workflows and moving data from one table to another. Consider the following:
if you are moving and then deleting 1000 rows with a workflow, then that will cost you a minimum of 2000 updates. My option will use zero updates.
if you have data in other tables that is related to data in the users table, those relations will break when you move the user record, along with any screens, etc that depend on them. My option does not have that problem.