We covered an introduction to the way Time Matters internally saves dates in a much earlier blog article. Even though I have a date converter (at the bottom of the page here) I wanted to go a bit deeper with this article to include examples of converting dates and times in common applications.

Most of the internal system dates and times in the Time Matters database are stored as an integer. In fact, the only dates and times stored as plain text are user defined date and time fields in the form styles.

There are several technical benefits to using integer based dates and times, primarily date/time math operations as well as an avoidance of the Y2K crisis. We'll skip most of the negatives since we cannot do anything about them (and I like to keep it positive). Let’s focus on how to make something useful and human readable from these integers.

Integer based dates and times are used throughout Time Matters and always on forms with Date and Time fields in area one. User defined areas on forms will never use integer based dates and times, they are plain text and therefore do not require any conversions.

^{Area 1 is an example of integer based dates and times.}

^{User defines areas never use integer based dates and times.}

Understanding Dates

The integer format that Time Matters dates use assumes a value of 1 being equal to 12/29/1800. Knowing this, we can perform conversions in various environments by normalizing the date math as follows:

SQL Server Inline Query Date Formula

CONVERT( VARCHAR(10), DATEADD(day, bil_ [TMIntegerDate] - 4, '1801-01-01'), 126)

This example will return a date converted and formatted as  yyyy-mm-dd. The DATEADD function performs the conversion from an integer to an SQL DateTime format. The CONVERT function allows the date to be displayed in the simple yyyy-mm-dd format. Limiting the VARCHAR to 10 characters allows only the first 10 characters of the resulting DateTime format to be returned.

Excel Date Formula

ExcelDate = [TMIntegerDate] - 3161

For Excel, "Day 1" is 01-01-1900 so the difference between the TM "Day 1" and the Excel "Day 1" is 3,161. The resulting column should be formatted as a date for appropriate viewing in Excel.

Crystal Reports Date Formula Field

DATE(1800,12,28) + {[TMIntegerDate]}

Relatively simple and straightforward, in Crystal Reports simply create a formula field for the date and use the format shown here. The resulting value can be sorted or grouped in native Crystal Reports format.

Understanding Times

Times in Time Matters are very simple to understand and represent the number of "ticks" since midnight. Each tick has a value of 100 per second. Consider the following examples: 100 = 1 second, 6,000 = 1 minute, 360,000 = 1 hour, 8,640,000 = 1 day.

SQL Server Inline Query Time Formula

LTRIM( RIGHT( CONVERT( VARCHAR(20), DATEADD(ms, [TMIntegerTime]* 10, 0),  22), 11) )

This example will return a time converted and formatted as  hh:mm:ss xm. The DATEADD function performs the conversion from an integer to an SQL DateTime format. The CONVERT function allows the time to be displayed in the simple format. In this example the LTRIM and RIGHT functions are used to truncate the date portion of the result.

LEFT( CONVERT (VARCHAR(8),  DATEADD(ms, [TMIntegerTime] * 10, 0), 8), 5)

This example will display the result as hh:mm in 24 hour format.

Excel Time Formula

ExcelTime = ([TMIntegerTime]-1) / 3161

For Excel, time is measured in a fraction of a day and therefore represents the decimal portion of a DateTime formula. The resulting column should be formatted as a time for appropriate viewing in Excel.

Crystal Reports Time Formula Field

TIME( ([TMIntegerTime]-1) / 8640000 )

Similar to Excel, Crystal uses a percentage of a day to derive the time value. In Crystal Reports simply create a formula field for the time and use the format shown here. The resulting value can be sorted or grouped in native Crystal Reports format.

I am very pleased to announce that I have accepted the position of Chief Technology Officer (CTO) for Advologix, Inc. , the best cloud-based Practice Management Solution on the market. Having spent several years studying the technology of this market, and over the last year having become acquainted with the team at Advologix, I am extremely excited to be a part of the team that I believe is the leader in helping to shape the future of this industry.

I thoroughly enjoy working with the consultants in our community, and although I have resigned my certification as a LexisNexis Certified Consultant (due to potential conflicts of interest), Data Equity remains committed to our Time Matters add-on products. We will continue to develop and support our product portfolio. I will also continue to provide technical consulting services to my existing clients.

As much as I have enjoyed the 10+ years of LexisNexis Time Matters experience, I sincerely look forward to helping shape Advologix and the future of our industry. I would like to share with you a few reasons why I am excited about the position with Advologix:

1. The Team
I was immediately impressed with the management team at Advologix. Founders Paul Deschenes and Jonathan Reed have a proven track record and are the consummate professionals.

2. The Product and Model
Acceptance of “the cloud” has moved beyond “early adopter”. Data Equity has always been focused on developing software for mobile and online platforms. Joining AdvologixPM supports and further develops my ability to embrace this core vision.

3. The Platform
Established software companies often lack the agility to move an existing desktop platform to the cloud. AdvologixPM was designed for, and built from the ground up, on the Salesforce.com platform and inherits the absolute best of breed cloud technology.

4. The Future
The AdvologixPM platform offers consultants and developers the most reliable, secure, robust, and extensive platform for developing online Practice Management solutions.

I am truly committed to this technology and sincerely excited to be moving into this new position. I especially appreciate the valuable relationships I have made as a consultant and hope you will allow me to stay in touch with you in the future. Please follow me on my Force4Law.com blog as I begin to introduce the benefits and opportunities AdvologixPM offers the legal community. Feel free to give me a call or email me with any questions you might have.

I thought this might be useful news to the community.

From the XpressDox web site....

We’re giving away up to 10 licenses PER small office or department on a first come, first served basis. This would normally cost up to $1,490 but it could be yours absolutely free!

How do you qualify? Submit your details below before 31 May 2010 to stand in line for up to 10 XpressDox licenses for your small office or department.

But please hurry! We have only a limited number of licenses to give away before 31 May 2010.

There are two types of relationships in Time Matters:  Automatic and Specified. We discovered Automatic Relationships in the first article. Automatic Relationships are fairly straightforward and generally easy to understand. The addition of Specified Relationships contributes to Time Matters’ reputation for being complicated to understand. Let’s continue.

Specified Relationships are, by definition, a linkage or connection among specific data types (Contacts, Matters, Event, ToDo, Note, Document, etc.). In database terminology, this is referred to as a MANY:MANY relationship. In specific terms, one Email may have many Events or Document records assigned to it.

One of the primary distinctions between Automatic and Specified relationships is that Automatic Relationships are parented by Contacts and Matters only. This is referred to as a ONE:MANY style relationship. Specified Relationships are not limited to being parented by Contacts and Matters.  Any record can be a parent and any record can be a child (in the relationship).

What makes Specified Relationships more complicated to understand is when you consider how these relationships are created. Automatic Relationships gets its name from the implementation and methodology that Time Matters uses to create and manage them. Specified Relationships can be explicitly created or can be the result of a linked lookup field.

Explicitly Assigned Specified Relationships

There are a few ways to explicitly create Specified Relationships, the first being from the “Related” tab of any form. An example of using Specified Relationships would be to explicitly assign a Matter to a Contact or vice-versa. The uses are numerous when you consider the ability to assign Outlines to Events or ToDos to Documents, etc.

The form’s “Related” tab allows the assignment of an existing (or new) record to the underlying record on the form. This Specifically assigned record will then be displayed on the related record tab as well as reports, powerviews and other areas of Time Matters that incorporate related records.

A quicker way to Specifically Relate records is by dragging and dropping them on top of each other from the main lists. This method is a very quick way to mass several related records without having to open forms and manually associate records.

Implied Specified Relationships

Specified Relationships can also be created in a way that is less formal and similar to the way an Automatic Relationship is created. When fields are customized with specific lookup options (as illustrated above), Specified Relationships can be created when the form is completed.

When the “Linked Field” option is selected on the Field Links general porperties, the Linked Lookup form of a Specified Relationship is created when the form is saved.

The bidrectional arrows represent an existing Specified Relationship between the underlying record and the “looked-up” record in the entry field to the left.

In contrast, when the lookup button is displayed as an ellipse, an underlying Specified Relationship for this field does not exist. This can be easily explained if the “Linked Field” checkbox has not been selected. When this box is unchecked, a Specified Relationship will not be created.

When the field is customized with the “Linked Field” option checked, and a value exists in the field, and the button is manifested as an ellipse (as opposed to bidirectional arrows), this is said to be an orphaned or invalid relationship and must be fixed by looking up the record again.

Internally Defining the Specified Relationship

Specified relationships are stored in the RelateS database table. Unlike the RelateA table, this table cannot be rebuilt and must never be modified from outside the program. The RelateS table has only three columns: RCode, FieldNo, MID and SID. These columns hold the relationship code (rcode) or field number (fieldno), and like the RelateA table, they also hold the sysId (record identifier) of the Master and Subordinate records.

RelateS table contents for a single explicitly defined Specified Relationship between an Event and a ToDo.
NOTE:  The Event Record ID starts with E and the ToDo Record ID starts with T.

The example above illustrates an explicitly defined Specified Relationship that was created by manually adding a relationship (either from related tab or drag and drop) using the relationship code “RES”. Notice the MID/SID pairing going in both directions; there are two records created for a single explicit Specified Relationship.

RelateS table contents for a Linked Lookup defined Specified Relationship between a Matter and a Contact.
NOTE:  The Matter Record ID starts with A and the Contact Record ID starts with C.

This next example (above) is for a “Linked Lookup” specified relation exemplified by the fieldno value. This example is for a customized lookup field in the first field of Area 3 on the Matter form. The value looked up is for a specific Contact. Note that unlike the previous example, a single record is used to identify this relationship.

In Conclusion

Unlike Automatic Relationships which are much more self-managed and self-healed, Specified Relationships are known to have integrity problems. Unfortunately, the Update Related Records Index function of Database Maintenance cannot repair or clean up these relationships. In fact, Time Matters does a poor job of enforcing the integrity of Specified Relationships and therefore does not always protect the database from orphaned relationships. The most common problem is with linked lookups becoming invalid.