How to Add a Unique Record ID and Date to an Existing Database

by Jonathan Jacobsen Wednesday, May 28, 2014 6:09 PM

The principles of good database design suggest that every record in a database should have something that uniquely identifies it and distinguishes it from every other record. In a small library catalogue, it may be that every title is different from every other, but in any larger database, there's a good chance there will be more than one very, very similar record. 

When it comes time to do more with this information, such as import it into Inmagic Presto or the VuFind discovery interface, or clean-up some older records, it's imperative that every record be able to be uniquely identified.

It's also good practice to have fields that track the date a record was created and the date it was last modified, for similar reasons.

It's very easy to create these fields in your database structure, whether in Inmagic DB/TextWorks or another system, when you are first building the database. But what can you do if you already have a database with thousands of records, and no unique record identifier or dates created or modified? With DB/TextWorks, it's very easy to add this information even after the database was built and populated. 

There are four main steps to this:

  • Backup all your existing records, just in case of a problem.
  • Verify that these fields don't already exist.
  • Add the fields to the database structure.
  • Populate the fields with data.

Backup the Database

1. Open DB/TextWorks but don't open a database. Select Manage Textbases > Dump Textbase. 

2. Browse to the location of your database and select it. 

3. Next, pick a folder to store the export in and give it a meaningful name.

4. When the export is complete, you'll now have a backup you can reload, just in case anything happens.

Verify Fields

5. Open the database in DB/TextWorks and, if it has password protection, be sure to sign in with the Master password, so you have full access.

6. Select Maintain > Edit Textbase Structure.

7. In the Edit Textbase Structure window, scroll through the list of fields to check that you don't already have a unique ID and/or dates created or modified fields. You may be able to identify them by Name (e.g. RecordID, RecordCreated, RecordModified) or by Type (e.g. Automatic Number, Automatic ID, Automatic Date).

Add Fields

8. If none are found, select Edit Fields. You will now enter information to create the new fields listed below. Be sure to select Add rather than Change after entering information for each new field.

Field Name RecordID
Field Type Automatic Number
Other Settings By default, the automatic numbering will start at 1 and increment by 1; there's usually no reason to change this.
Field Name RecordModified
Field Type Automatic Date
Other Settings Update Entry: When Record is Created or Modified.
Date Components and Format: you may choose whether to include the time, or only the date, and which format. We usually select Date Only and Short Date Format, unless your data will be imported into Presto, in which case Date and Time is best.
Field Name RecordCreated
Field Type Automatic Date
Other Settings Update Entry: When Record is Created.
Date Components and Format: you may choose whether to include the time, or only the date, and which format. We usually select Date Only and Short Date Format, unless your data will be imported into Presto, in which case Date and Time is best.

9. With all of the information entered, click Finish and OK to save your changes to the database structure.

Add Data to Existing Records

10. Select Search > Find All Records to retrieve all records from the database. This may take some time, but you can click the Stop button in the bottom status bar of DB/TextWorks to stop the formatting on the screen.

11. When the records have finished loading, select Records > Batch Modify. The trick now is to make a perfectly innocuous change to every record in your database. As DB/TextWorks goes through every one, since the database now has Automatic Number and Automatic Date fields, they will automatically be populated. 

If you happen to have an unused field in your database, you could populate it with some value, and then remove that again in a second batch modification.

Or, you could add a new field and use batch modification to populate it with some value. You can then either leave it as-is, perform another batch modification to delete the data from the field, or delete the field altogether.

The batch modification feature in DB/TextWorks is quite sophisticated and an explanation of it would expand this blog post to unwieldy dimensions. However, the help built-in to DB/TextWorks explains all the options, including new features added to recent releases. 

12. When the batch modification is complete, every record will now have a unique identifier and the date the record was last modified. To verify, search for some records and view the ID and dates.

13. Now that you have a unique ID, you can configure DB/TextWorks to record it in the textbase log file every time a change is made to a record. This has proven invaluable to several clients this month when accidental changes were made to records. We were able to use the log to undo those changes. To configure your textbase log, select Maintain > Edit Textbase Structure again, then Log File. Select your RecordID field as the First Identifying Field, and something like title as the Second.

If you need any help with the above, just let us know.

Thunder Bay and Simcoe Muskoka Health Units Launch Shared Library Catalogue

by Jonathan Jacobsen Monday, May 12, 2014 6:42 PM

In 2012, Andornot worked with the Thunder Bay District Health Unit to upgrade their DB/TextWorks library system from the older Inmagic Library Module kit to the much newer Genie Integrated Library System

This year, the Simcoe Muskoka Health Unit merged their MS Access library catalogue into this shared Genie system, providing both health units with a modern, up-to-date system for managing library holdings, serial subscriptions, orders, and more.

Amongst health care agencies in Canada, it is quite common to share library systems and resources – it makes good economic sense, and helps users and patients to more easily find resources with a single search point.

The public and patients may search the combined library holdings through http://ophc.andornot.com, a discovery interface powered by the VuFind open source software. 

VuFind includes features such as:

  • Spelling suggestions and corrections. 
  • Faceted browsing to allow users to refine searches by subject, date, collection, and other key fields. 
  • A mobile interface. 
  • Google Book covers for records with ISBNs. 
  • Permalinks to records to allow easy bookmarking and social sharing. 
  • User accounts to allow users to save searches and records to personal lists.
  • RSS feeds for user-created searches.

A custom request form developed by Andornot allows users to direct a request to borrow items to the appropriate health unit.

Data is automatically imported into the VuFind system nightly using the Andornot Data Extraction Utility, so there’s no additional work needed by library staff.

The shared Genie system is hosted by Andornot as part of our managed hosting service. Working with two separate groups on a joint system can be tricky, but in this case the cooperation between the library staff in the two health units allowed us to complete the project in a very tight time frame.

"Thanks very much for all of your hard work on this project. It is very much appreciated by us and will be by our clients." Amy Faulkner, Librarian, SMDHU

"You’ve done an exceptional job and we’re all quite appreciative of your hard work and discipline to get this through in such short time frames." Shant Alajajian, Librarian, TBDHU

Contact us to discuss upgrades to your library system and public search interface.

Month List