Reconciling Bookends DB with attachments folder

A place for users to ask each other questions, make suggestions, and discuss Bookends.
Post Reply
Fritz
Posts: 4
Joined: Thu Jul 05, 2012 5:12 pm

Reconciling Bookends DB with attachments folder

Post by Fritz »

With tech supports help I built a smart SQL group to find multiple attachments ( varCharField REGEX '\r'). But the count of the attachments in my Bookends DB adjusted for multiple entries did NOT match the items (PDFs and folders) I had in my attachments folder. The large number of PDFs made a manual reconciliation tedious. (After I finished the reconciliation I realized that a simple visual inspection would have been impossible. I never would have found the problems.)

Here's the summary solution. I made a TXT file of all the attachments in my Bookends DB and a second TXT file of all the items in my attachments folder. Both files were sorted with Textwrangler and then compared for differences. After making the clerical changes necessary a second set of TXT files were identical.

Here's the detailed solution.

(1) You will need to create a smart SQL group for multiple attachments ( varCharField REGEX '\r'). You will also need a temporary smart SQL group to find the specific reference that uses a particular attachment ( varCharField REGEX 'YourFileNameHere'). I edited this temporary group many times as I searched for references that were attached to specific files.

(2) You will need Textwrangler (or something similar) for sorting TXT files and doing file differences. Textwrangler may be freely downloaded here (http://www.barebones.com/products/TextWrangler/).

(3) Build an attachments format that only has the letter "h" in it. When you print references with this format, only the attachment file names will print. Open Bookends and select Attachments (instead of All). Under the File menu use Print References. Select the attachments format already created and print to PDF. Lets call this Bookends.pdf.

(4) Open Bookends.pdf in Adobe Reader (or Acrobat). Use View>Page Display>Single Page Continuous. Highlight the first file name and then Select All. Edit>Copy. Move to Textwrangler and open a new file. Paste, Sort and Save. You now have the first file ready to go. Lets call this TXT file Bookends.txt.

(5) Open the attachment folder in the Finder and highlight the first file. Select All and copy. Move to Textwrangler and open a new file. Paste, Sort and Save. You now have the second TXT file ready to go. Let call this TXT file Attachments Folder.txt.

(6) Textwrangler's Find Differences uses files on disk. (You do NOT need them to be open.) Under the Search menu use Find Differences and point to the two TXT files previously created. Personally I like to use the Desktop for this quick and dirty work. So both of my files were there.

(7) Working through Textwrangler's differences requires patience. My application ensures that a reference can have multiple attachments but each attachment has only one reference. If all your attachments are in a single folder, then each attachment file name must be unique. It was like balancing my checkbook and looking for missing entries and it was equally frustrating.

(8) Good Luck. I hope this outline was helpful.
Post Reply