HOWTO:Sync And Purge

Command Line Shortcut: NONE User Permissions Level: All Product Level: XLT PRO Sync with and purge undesirable data.

---

---

Warning: Backup your database before doing this. I'm not kidding.

---

Concept

The WireCAD synchronizer prioritizes deleted records by placing the key for the deleted record in a special table called [TABLENAME]_tombstones. Once a key exists in the [TABLENAME]_tombstones table any record that attempting to sync that has a matching key will be deleted. Using this knowledge we can purge a database of undesirable records (maybe you are wanting to remove all data from the stock WireCAD database and use only records that are important to you) thus ensuring that those records will always be deleted when syncing two WireCAD global databases even though one may contain stock data.

Before You Start

Backup your database before doing this. Your global database may be on your local machine or on a network share or a SQL server. We can't tell from here. You will need to figure this out so you can properly back up.
See these topics on file location:
Settings:Application_Global_Database_Location
Where does WireCAD put files?

Proceedure

---

Note: The following steps should be performed on your clean database

---

1. Back up your database. Did I say that?
2. Open your global Manufacturers database. Database>Manufacturers Grid.
3. We will be using the [User Added] field to store a flag that we can filter on later ([User Added] is the only user field in this table). If the [User Added] field does not show in the grid, right-click any column header and click Column Chooser, then drag the [User Added] field onto the column header row.
4. Populate the [User Added] field with some known data that we can filter on later. For purposes of this tutorial we will use "1" as the flag but you can substitute anything you like.
   1. Enter 1 in the top row.
   2. Drag from the top row to the bottom row. Be careful to only select the [User Added] column.
   3. Click Edit>Copy Down. This will copy 1 (the top row) into all the cells in the [User Added] column.
   4. Click File>Save.
5. Repeat Step 4 for your Global Equipment grid - Database>Equipment Grid. Here you can use any of the user fields [User1]-[User4].
6. Repeat Step 4 for your Global Cable Types grid - Database>Cable Types Grid. Here you can use any of the user fields [User1]-[User4].
7. Repeat Step 4 for your Global Signal Types grid - Database>Signal Types Grid. Here you can use the [Modified By] this field only takes integers.
8. Open the syncronizer tool. Database>Sync Equipment Libraries...
9. Tell the synchronizer where the dirty database is. This one's on you.
10. Select Bi-directional sync.
11. Click [Sync]. Go get coffee.

Now we should have dirty data in with our shiny-clean flagged data. Let's remove the dirty stuff.

1. Close all data grids.
2. Open your global Manufacturers database. Database>Manufacturers Grid.
3. Delete all non-flagged records
   1. Click on the [User Added] column header to sort the grid by the flagged value. This will move all empty flagged field to the top of the grid.
   2. Select all records where the flagged field is empty.
   3. Click Edit>Delete Selected Rows. This may take a minute.
   4. Click File>Save.
4. Repeat Step 3 for your Global Equipment grid - Database>Equipment Grid. Be sure to sort or filter on the field that you used to hold your flag. NOTE that most of your non-flagged records will be deleted as a result of cascading deletes from the Manufactures database.
5. Repeat Step 3 for your Global Cable Types grid - Database>Cable Types Grid. Be sure to sort or filter on the field that you used to hold your flag.
6. Repeat Step 3 for your Global Signal Types grid - Database>Signal Types Grid. Be sure to sort or filter on the field that you used to hold your flag.

Testing The Result

Your clean database should now be clean again with only the equipment we chose to keep. All other records keys are now stored in their corresponding _tombstones tables. If you sync with an unclean database that contains records with keys contained in a _tombstones table those records will be removed.

1. Open the syncronizer tool. Database>Sync Equipment Libraries...
2. Tell the synchronizer where the dirty database is. This one's on you.
3. Select Bi-directional sync.
4. Click [Sync]. Go get coffee.

Verify that only flagged records exist in the clean database.