Convert Time Matters Dates, Numbers into HotDocs

From HotDocs Wiki

Jump to: navigation, search

When connecting to a Time Matters SQL database using the HotDocs Database Connection, you may need to convert a Time Matters date value (which is stored as a plain number in the database) to a HotDocs Date variable.

Because Time Matters stores its dates as plain numbers in the underlying database table, the HotDocs Database Connection will let you link only those database fields to Number variables.

In the following example, say the underlying database record contains two "dates", birth_date and contract_date. Additionally, assume the Database Connection has been used to link these two database fields to the HotDocs Number variables, birth_date_num and contract_date_num.

The first step in converting these values is to create a temporary Number variable named TempTMDate. (When creating the variable, click the Advanced tab at the Number Variable Editor and clear Ask automatically, Warn when unanswered, and Save in answer file.)

The next step is to create a Computation variable named TMDateConvert. It should contain the following script:

Convert TM Dates 1.1.png

To explain, this script takes into consideration two relevant facts:

  1. Time Matters stores dates as in integer representing the number of days since 28 DEC 1800 ; and
  2. The DATE + NUM DAYS Expression model currently supports only numbers of days less than 32,000. This means that you must include the ELSE instructions to take into consideration numbers of days greater than 32,000. (Otherwise, you could simply use the script 28 DEC 1800 + TempTMDate DAYS.) This script (in its entirety) handles dates from 19 MAY 1713 to 10 JUN 2151.

So now if you want to merge these dates into a document, you could use the following script in the text of the template:

Convert TM Dates 1.2.png

You could also set actual Date variables to the correct values using the following script:

Convert TM Dates 1.1.png