Auto-Creation of Timeline Files

The following article applies to TMG v5.x, v6.x and v7.x. 
A version for TMG v4.x and earlier is located here.

Creating a Timeline File

A timeline can be instrumental in helping your genealogy research. TMG allows you to create new timeline files as you wish using your own data. However, you must create the file and enter each timeline date and event one at a time. This may be alright for information such as county and state formations; but for data that you have already entered in your dataset, you hate to enter it through your keyboard all over again.

Take heart, a custom timeline can be created from your own dataset and used to see who else in your ancestry (both direct and indirect) was doing what at the same time. The custom timeline can be as simple or as complex and can include or exclude as much or as little as you wish. Naturally, the more you include in the timeline, the more work it will involve. On the other hand, it may provide that extra research tool that may help you find that elusive piece of information you need.

There are three major parts to the procedure. The first part requires the creation of a List of Events (LOE) file based on the criteria you select from TMG.  Part two allows you to re-arrange the report file in preparation for creating the database file. Part three creates the actual timeline file. 

1. Generating the List of Events (LOE) report is the easiest part. You may already have done many this times; but with output going to your printer and/or in a different sequence. I wanted a timeline of all Hoffman births, so I created one this way:

• Select Reports=>List of...=>Events from the Main Menu.
• Click the [Add] button to create a new Report Definition, enter the File name (say 'LOE - Timeline'), and click on the [Save] button choosing the [No] option..
• Select the Filtered group option and click on the [Add] button.
• Enter Timeline in the Save Filter As field at the top of the window.
• Enter the following as the filter:
  • Principal1...    Surname    =Equals    [?]    END
    • Note that you can adjust the above filter to be more or less restrictive as to who should be selected.  The above just selects anyone having a specific surname.
• When the filter is complete, click on the [OK] button to save it.
• In the Print to section, select the File option.
• Choose the Microsoft Excel v5 (XLS) File Type option.
  • Note that this will vary depending on how you will process the result into the Timeline file. 
• Enter the File name (and file path) as appropriate (I used 'u:\TMG Reports\Hoffman Timeline.xls').
• Click on the [Options] button to display the Options window.
• On the Output Columns tab, select the following in order for numbers 1 through 8:
  • Date - Year
  • Date - Month
  • Date - Day
  • Tag Type Label
  • Prin1 Given Last
  • Prin1 ID
  • Place
  • Memo
    • The first three lines are required in the order named.  The remainder can be in any order and you may add or delete any desired fields.  Depending on the Tags included, you may want to include a Prin2 Give Last name.  Keep in mind that the Memo field is where occupations, employers, etc. are located.  If you want to include more data you can use Date(thin) in place of the separate Year, Month, and Day items, but you will have to remember to adjust the file later for the date and split the Date(thin) field into its sub-fields.
    • Be sure to set the column width field so that it does not truncate data before you want it.  This applies primarily for the Place and Memo fields, but you may wish to check the other fields just in case.
• Not strictly necessary, but you may wish to enter the Sort Order.  So enter a 1 for the Year, 2, for the Month, and 3 for the Day.  (If you use the Date(thin) option then just have a 1 Sort Order for it.
• Click on the [OK] button to save the changes.
• Now, back on the Report Definition Screen, click on the [Print & Save] button to save the definition and generate the report file.

2. After the file has been generated, it must be converted to the proper format for use as a Timeline. So open the file produced above in Microsoft Excel.

The input to your database manager will need to be a file with four "columns" of data:
        Data                    Data           Data      Decimal
     Column    Data     Type           Length    Places
        ----     --------   -------       -------   ---------
        1.          Year     Numeric           4              0
        2.          Month  Numeric           2              0
        3.          Day      Numeric           2              0
        4.          Event   Character      160              -
The data length in column four should be adjusted to the length of the longest amount of data in the event field that you will have.

Below are some sample data lines as they might appear in the LOE file (with quote marks shown to simulate spreadsheet cells) as generated by TMG using the criteria shown above in step 1:
"1821","06","25","Birth","(Judge) John Stringer HOFFMAN", "11306", "Weston, Virginia"," "
"1790","00","00","Birth","John HOFFMAN", "11316","Culpeper County, Virginia"," "
"1758","00","00","Birth","James HUFFMAN","11304","Culpeper County, Virginia"," "

Note that this data appears to have six fields or columns and must be edited for input to your database management program so that it would appear something like:
1821,06,25,"Birth of (Judge) John Stringer HOFFMAN (11306) in Weston, Virginia"
1758,00,00,"Birth of James HOFFMAN (11316) in Culpeper County, Virginia"
1712,09,22,"Birth of Hans Henrich HUFFMAN (11304) in Culpeper County, Virginia"

Thus the data now appears as four fields or columns for each record as indicated above.  Remember at this point that you must have three fields for your date information, and in the order specified. So if you generated the date with Date(thin), you must edit the data accordingly.

Because the information in the Event field of a timeline is often in a sentence format or partial sentence format, you need to edit the text information into a single "field" or "column" into a clause or sentence of sorts to better explain the event. Note that I concatenated all the "text" fields into a single Event field. Insofar as the Event field is concerned, you may or may not wish to take the pains to create sentence fragments that are grammatically correct. This all depends of your intended use of the timeline. If the timeline is one that you might wish to share with others, you might want to take more time and create grammatically correct phrases as it would be more "professional". On the other hand, if you just want something quick and dirty just for your own use, you might just concatenate the field into the single Event field.

Some point on how to make the modifications:

• With the file open in Excel, note that all the date fields are marked to show they are text data.  You can convert this data into numeric data as follows:
  • Highlight all the date fields. 
  • Scroll the list back up to the top and hover the mouse over the "i" inside the diamond beside the highlighted fields.
  • Right-click on the down arrow that appears and choose the "Convert to Number" option.
• Click in the empty cell immediately to the right of the last used cell.
• Enter the following formula or something like it depending on what fields are included to the left of this cell and to the right of the date cells:

  =D2&" of "&E2&" ("&F2&") in "&G2&", "&H2

  • Note that this formula not only combines the text fields into the one Event field, but makes the result read a little easier.
  • Change "in" to "at" depending on your preference for the preposition used with the location.
• Copy that formula to all the cells below that do to the bottom entered row.
• Now, highlight the Event column (all the above cells with formulas), copy it, and select to Edit=>Paste Special.
• In the sub-menu, choose the Values option.  The result should not be noticeable except that the formulas will be gone but the data should remain.
• If the formulas are gone, then delete the tag type, P1 Given Last, P1 ID, Place and Memo columns that had held the individual text data.  This should leave you with four columns (Year, Month, Day, and Event).
• You may want to look at the entries with empty dates and decide if you want to keep them.  I usually elect to delete those records.  If there are just a few, you may want to keep them and manually edit the Timeline after you create it to add some kind of date for these records.
• Finally, make sure that the heading labels in the first line are Year, Month, Day, and Event.  If the headings are abbreviated or blank, then TMG will not be able to read the database when it is created.
• Finally, save the file and exit from Excel.

3. After editing the file, start Microsoft Access, and open the above Excel file and process it as follows:

• Select File=>New... from the Main Menu and choose Blank Database... from the New File menu.
• In the File New Database window, enter a file name (the name of the Timeline) for the new database.  This will be an interim file and may be placed on your drive anywhere you wish. (Note you may only have an eight character file name for the Timeline).
• Click on the [Create] button to create the database and display the database window.
• Double-click on the Create Table in Design View option.
• In the Field Name column, enter the names of the four fields (in this order): YEAR, MONTH, DAY, and EVENT.
• In the Data Type field select Number for the date fields and Memo for the Event field.
• Highlight each of each of the date fields in turn and in the General tab at the bottom of the screen, change Long Integer to Integer.
• Click on the [X] button in the upper right of the Table window to save and close the window.
• In the Save As window, enter the name of the Timeline (only eight characters, no spaces) and click the [OK] button.
• Click on the [No] answer to the Primary Key question.
• Highlight the Table that you just created.
• Now select File=>Get External Data=>Import... from the Main Menu.
• In the displayed Import window, choose Microsoft Excel (*.XLS) in the Files of Type field.
• Browse to the folder containing the Excel file that you created and modified above, select that file.
• Click the [Import] button to display (may take a moment) the Import Spreadsheet Wizard.
• Be sure that the option First Row Contains Column Headings, and click the [Next] button.
• Select the In an Existing Table option and select the table name.
• Click the [Finish] button to import the data into the table.
• Click the [OK] button.
• Select File=>Export from the Main Menu.
• In the Export Table ... window, enter a File name (such as 'Hoffman Timeline').
• In the Save as type field, select 'dBase III (*.dbf).
• Check that the Save In folder at the top is where the rest of your TMG Timelines are located, and click on the [Export] button.
• Exit from Microsoft Access.

At this point you have created a Timeline file - a .DBF file and a .DBT file.

Now enter TMG as usual and select your new timeline file. If you are on the Person view, you will see entries from the new timeline appear based on the rules of TMG as you have selected them. You now have a new timeline file that you can use.  Note that is any dates are incomplete (i.e., no day, no month, etc.) then the Date field in the Person View may display a zero in that place.

Many Timelines have a DOCumentation file which is text of your choice describing the Timeline, discussing the origin of the data, giving the author's name and address, etc.  You may create this file within TMG by editing the Timeline.  Select Tools=>Timeline Manager, scroll down the list and highlight your new Timeline. Click on the [Edit] button to display the Edit Timeline window. Click on the [Memo] button. Then in the Update Timeline memo window, enter a description of the Timeline. When finished, click on the [OK] button.  Click on the [Close] button and you will see the description in the Timeline listing.

Congratulations and have fun!

Return to the TMG Tips Tutorials Page

Last revised: