As a general rule, we recommend that SQL scripts be used to mass-select/prune records; however, sometimes that is not possible.  In these cases, we recommend having a member of the law firm complete a Google spreadsheet that clearly indicates what records should be kept and which should be discarded. 

A good spreadsheet will generate SQL for you so that you can easily include the user's selection in your playbook.

Example:  Cherry-Picking Matters

In the following example, a spreadsheet will be used to:

1. Cherry-pick matters.
2. Delete any matters not on the list.
3. Delete any contacts that are not clients of the matters.
4. Delete any documents that are not associated with matters on the list.

Step 1: Generate the Google Spreadsheet

Run a SQL command similar to the following:

SELECT
    '' as 'Keep? (Y)',
    V1.Id, 
    V2.Final_FullName,
    V1.Final_ReferenceCode, 
    V1.Final_Subject, 
    V1.Final_Description,
    V1.Final_Status,
    Script =
      --IF cell A### = "Y"
      '=IF(INDIRECT("A"&ROW()) = "Y"'
      --THEN: Generate the script using the ID in cell B###.
      + ', "Update __M_Matters SET Final_Status = Original_Status WHERE Id = ''" & INDIRECT("B"&ROW()) & "''"'
      --ELSE: Blank script
      + ', ""'
      + ')'
FROM
    __M_Matters V1
LEFT JOIN
    __M_Contacts V2 ON V2.Id = V1.Final_ClientContact_Id
ORDER BY
    V1.Final_ReferenceCode

Notice how the Script column automatically fills in for any columns marked with Y.

Step 2: Get the Generated Scripts from the Spreadsheet

Once the client has completed the spreadsheet, click on the KEEP column header, then select Data > Create Filter.

You will then be able to easily select and copy the script for the rows the client wants to keep.

Step 3: Incorporate the Generated Script

At this point, you are ready to incorporate the generated scripts into your playbook.  For the scenario described in the example, the entire playbook might look something like this:

--We are going to default all matters to deleted.
UPDATE

    __M_Matters
SET
    Final_Status = 'Deleted'

--Restore the status of the matters we actually want.

--PASTE OUR GENERATED SCRIPT HERE

UPDATE __M_Matters SET Final_Status = Original_Status WHERE Id = 'WALLP!52125'
UPDATE __M_Matters SET Final_Status = Original_Status WHERE Id = 'WALLP!52118'
UPDATE __M_Matters SET Final_Status = Original_Status WHERE Id = 'WALLP!52111'
UPDATE __M_Matters SET Final_Status = Original_Status WHERE Id = 'WALLP!52104'

--END OF GENERATED SCRIPT

--Delete all contacts where it is not
--the client of a not-deleted matter
UPDATE
    __M_Contacts
SET
    Final_Status = 'Deleted'
WHERE 1=1
    AND Id NOT IN (
        SELECT
            Final_ClientContact_Id
        FROM
            __M_Matters
        WHERE 1=1
            AND Final_Status != 'Deleted'
        )

--Set all documents to be deleted
UPDATE
    __M_Documents_Digital_Files
SET
    Final_Status = 'Deleted'

--Then restore the status of any documents that are associated to not-deleted matters
UPDATE
    __M_Documents_Digital_Files 
SET
    Final_Status = Original_Status
WHERE 1=1
    AND Final_Parent_Id IN (
        SELECT
            Id 
        FROM
            __M_Matters 
        WHERE 1=1
            AND Final_Status != 'Deleted'
        )