How to Add a Unique Record ID and Date to an Existing Database
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.