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.

Peel Health Region Upgrades Library Search Interface

by Jonathan Jacobsen Wednesday, April 23, 2014 11:09 AM

The Peel Health Region library in Ontario has been using Inmagic DB/TextWorks for a number of years to manage a small, specialized collection of health care resources. 

The library contacted Andornot recently for assistance in updating the public search interface to the catalog. After reviewing the catalog and discussing their needs, Andornot recommended an upgrade to both the DB/TextWorks databases and desktop interface, as well as creating a new web search interface.  

The library’s DB/TextWorks Catalog database was upgraded to the latest one from the Andornot Starter Kit, featuring well-designed forms, query screens, and script buttons for easy navigation.

A new public search interface was built from the web portion of the Andornot Starter Kit, and is available at http://peel.andornot.com, hosted by Andornot in our data centre. This interface is based on the Twitter Bootstrap responsive web framework, allowing the site to reformat itself for the user’s browser, so that it’s as usable on full-size desktop browser as on a tablet or smartphone.

Librarian Diana Robertson was particularly interested in linking to PDFs of reports. PDFs are currently stored in a document management system only available internally. With catalog records already available for many of these, linking to the PDFs and making them available in the web interface gives much greater public access.

A list of popular topics on the home page (constructed with the help of the Andornot Search Cannery Wizard) provide quick access to areas of the collection. 

The site also features icons to indicate material type, and a selection list to allow users to email, save or print items of most interest to them. These features are standard with the Andornot Starter Kit.

Contact us for assistance with upgrading your catalog to more effective desktop and web interfaces.

Spring cleanup for your Inmagic databases. Part 4: Renaming fields

by Kathy Bryce Tuesday, March 25, 2014 9:26 AM

In the first post of this series we wrote about cleaning up the files associated with DB/TextWorks and in the second we covered rationalizing your textbase elements.The third post discussed some steps you can take to protect and maintain your textbases in good health.

In this last part of our Spring cleanup series we will discuss renaming fields. This requires the most caution and forethought, but is also advisable to ensure that new users can understand your textbases. All too often we find clients who have maintained the same textbases for years and years and see no problem with fields named AU, TI etc. It’s pretty easy to guess that these stand for Author and Title in a library catalogue, but what about some of the other abbreviations that may date from much earlier versions of Inmagic when there were limits to the field name length.  We came across a client with an LCCN field, i.e. Library of Congress Control Number. A new non library person started data entry and guessed that this field was an abbreviation for their shelf location, thus creating a horrendous mixture of entries. (We always recommend adding Automatic Date type fields for RecordCreated and RecordModified which can make cleanup of this type of mistake a bit easier.)  Field names in the current version of DB/TextWorks have a 20 character limit which is usually ample to describe the contents. We recommend not including any spaces, but visually separating words with caps or underscores as in PublicationDate or Project_Number.  If you have several databases with similar fields, you should consider giving them consistent names.

If you make changes to a field name, all DB/TextWorks query screens and form boxes simply use the new values and continue to function. Any box labels that were taken directly from the field names will however continue to show the old values.

As a precaution we always recommend making a backup or copy of the textbase before making any significant modifications.  Next, determine if there are any textbases linked to the one you wish to change. Linked fields in a Secondary textbase can be identified by viewing the Textbase Information under the Display tab, but the fields that are linked to are not shown in the primary textbase information, so you do need to understand if there are relationships between your textbases before renaming fields.

As mentioned in Part 2 on changing textbase elements, extra care must be taken if you have WebPublisher PRO, as query screens or canned searches will reference field names and will not update automatically if you edit these. Changing field names may also break forms or query screens with embedded scripts. Scripting capabilities were introduced in DB/TextWorks version 4 so pre 2001 textbases are not likely to include any. More recent textbases from Inmagic such as those in the Library Module, and those provided by Andornot will include some scripting.

If your textbases don’t have linked textbases, scripts or web access, then renaming fields can be straightforward and a great way to rationalize your textbase to make it easier for others to understand.

If you don’t feel comfortable doing this renaming cleanup yourself, contact us and we can help you on a consulting basis.

We hope you have enjoyed this four-part series on spring cleaning your databases – please let us know if there are other topics you would like us to cover!

Spring cleanup for your Inmagic databases. Part 3: Protecting and maintaining your textbases

by Kathy Bryce Monday, March 24, 2014 10:32 AM

In the first post of this series we wrote about cleaning up the files associated with DB/TextWorks and in the second we covered rationalizing your textbase elements.  In this post we’ll discuss some steps you can take to protect and maintain your textbases in good health.

Usually Inmagic DB/TextWorks textbases can function for many years without any intervention or problems. However if you do ever see a “Stop: textbase is in an inconsistent state….” message, please do NOT keep working in it! We have had clients tell us that they just ignore that message not realizing that the textbase might be corrupt. Frequently this message is just caused by a temporary loss of network connectivity while a record is being edited and can be fixed very quickly.

We recommend every so often running Check Textbase from Manage Textbases on a menu imagescreen (i.e. without a textbase open). This will detect and repair problems in the textbase and your user file. The process generally takes just a few minutes for most textbases, but can take a while for very large ones. We suggest specifying Options to Repair Structural Problems and Rebuild 10 or more Damaged Indexes (depending on textbase size). If any problems are found these will be listed in the .chk file with a recommendation for action. Running Check Textbase in this manner will clear the inconsistent state message if it was just caused by a network glitch.

As part of your regular maintenance we also recommend confirming that you have a backup routine for your textbases. We have have heard some horror stories over the years.  Two clients had fires, and two had floods in their buildings.  One of these had no offsite backup and lost several years work.  Another client had all their textbases deleted by an over zealous IT guy who didn’t know what they were and figured they weren’t important, and another client hit batch delete instead of batch modify!  For many of our smaller clients without any IT support you can always simply make a backup by copying your textbases to a USB stick and taking it home with you.

The above information applies to the non-SQL version of DB/TextWorks. Clients with DB/Text for SQL versions should ensure their IT staff are aware of the recommendations in the Administrators Guide available from the Inmagic extranet.

For more information, check out the Help file built in to DB/TextWorks, or the printable PDFfor version 13.   If you run Check Textbase and need help implementing the recommendations, please contact Inmagic Support if you have a maintenance contract, or we can help you on a consulting basis.

Month List