In my previous role as a Senior Systems Analyst (mostly helping the eDiscovery team process and load data), I encountered an issue where the date and time exported from LAW did not align with the date and time format utilized in our department-wide Relativity database. First, the date and time fields were their own original fields in the LAW DB. Second, there is a heigharchy of dates that determine the DOCDATE field we need to update in Relativity.
DOCDATE is determined based on whether the document is a parent or attachment, and a heigharchy of the following dates:
DateTimeCreated
DateTimeLastMod
DateTimeRcvd
DateTimeSent
Typically, we relied on MS Access to create a DSN entry, enabling us to connect to the LAW database and execute custom formulas and finally export an overlay. This process was often time-consuming, particularly for larger cases due to the size of the case/mdb file. To address this, I developed a HTML/VBSCRIPT application that efficiently filtered out unnecessary data from the LAW database server based on Volume number. It then reformatted the dates and times into a valid Relativity format, facilitating the creation of a quick overlay file. Here is a picture of the Date/Time Overlay Creator tool I designed:
Date Time Overlay Creator: For each new case, the creation of a DSN entry in MS Access was required, which could be quite labor-intensive. My solution eliminated this requirement and accelerated the process by 90%. The application proved effective for both small and large cases and featured a user-friendly graphical interface, making it accessible for new users who were unfamiliar with the process of establishing DSN connections in MS Access. When the application loaded, it populated the combobox with all the cases in the LAW DB. It also asks user to enter the _BOX#, which is where we populated the Volume (For example ABC001, VOL001, etc.). Finally, the user can specify a Output Directory for where they want the overlay created and saved. Creation of this method made training a whole lot easier as well.
Here is the code for the entire application. It requires some coding knowledge, however I commented the code for easier understanding:
<!--
Application: Date Time Overlay Creator
Description: Creates a date/time overlay based on _BOX field in LAW
Author: MSingh
Date: 12/4/2018
Notes: added delimiters
added default delimiters
-->
'below we create the HTA application
<head>
<title>Create Date/Time Overlay</title>
<HTA: APPLICATION
APPLICATIONNAME="Create Date/Time Overlay"
SCROLL="no"
SINGLEINSTANCE="yes"
WINDOWSTATE="normal"
MAXIMIZEBUTTON="no"
BORDER="DIALOG"
>
'below we reference a CSS stylesheet to quickly stylize our application
<link rel="stylesheet" type="text/css" href="http://fastly.ink.sapo.pt/3.1.10/css/ink.css">
'Select VBSCRIPT as the language in the script html tag
<script language="VBSCRIPT">
dim myConn 'declare global variables
dim myCommand
dim dbServer
dim database
Sub Window_onLoad 'here is the procedure to define what the application does on start-up
Window.resizeTo 600 ,500
Dim OptBox
Set OptBox = document.getelementbyid("dbChooser") 'Get the text in the Combobox
'Connection string to SQL DB. It connects to the DataSource using UserID and Password. The initial catalog is MASTER because it contains a list of all the cases in LAW
Const DB_CON_STR = "Provider=SQLOLEDB.1;Data Source=data_source;Initial Catalog='Master';user id ='law_user';password='user_password'"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CON_STR
Set myCommand.ActiveConnection = myConn
'Pass query to SQL DB
Set objRecSet = myConn.Execute("Select Name from SysDatabases")
'Populate combobox with names of all the cases
Do until objRecSet.EOF
set objOpt = OptBox.document.createElement("option")
objOpt.Text = objRecSet.Fields("Name")
objOPT.Value = Objopt.text
dbchooser.options.add objOpt
objRecSet.movenext
Loop
End Sub
'The subroutine below actually creates the overlay
Sub CreateOverlay
'Create text file
result.innerHTML = result.InnerHTML & "Connecting to DB...<br>Creating date overlay text file..."
dim docdate
database = dbchooser.Value
boxnum = txtBoxNum.Value
'Open new DB connection to the case selected in the combobox
DB_CON_STR = "Provider=SQLOLEDB.1;Data Source=data_source;Initial Catalog='" + database + "';user id ='law_user';password='user_pw'"
Set myNewConn = CreateObject("ADODB.Connection")
Set myNewCommand = CreateObject("ADODB.Command")
myNewConn.open DB_CON_STR
Set myNewCommand.ActiveConnection = myNewConn
'Select date fields using SQL SELECT query
Set objDateRecSet = MynewConn.Execute("SELECT _DOCID, Email_Subject, Title, DateSent, TimeSent, DateCreated, TimeCreated, DateLastMod, TimeLastMod, DateRcvd, TimeRcvd, DateAccessed, TimeAccessed, AttachPID, AttachLvl From Tbldoc Where _BOX='" + cstr(boxNum) + "'" )
Dim DateTimeParent 'Variable used to find parent
dim objfile
'Get date/time for filename output -- the filename of the overlay is created based on current date and time -- helps not overwrite if multiple overlays are created
DateTime = Now()
dd = Right("00" & Day(dateTime), 2)
mm = Right("00" & Month(dateTime), 2)
yy = Year(dateTime)
hh = Right("00" & Hour(dateTime), 2)
mn = Right("00" & Minute(dateTime),2)
ss = Right("00" & Second(dateTime), 2)
fileName = yy & mm & dd & "_" & hh & mn & ss & "-dateOverlay.txt"
'Create object for writing - open the filesystem object to enable writing
Set objFSO = CreateObject("Scripting.FileSystemObject")
dir = txtDir.Value
'if txtDir is empty - output to current directory:
if (isNull(dir) or isEmpty(dir) or dir="") Then
Directory = objFSO.BuildPath(CurrentDirectory, filename)
Else
Directory = txtDir.Value & "\" & filename
End if
Set objFile = objFSO.CreateTextFile(Directory, true, true) 'overwrite, unicode - helps with unknown characters (err 5 - invalid procedure)
'WriteToFile
'Write Header to file based on delimiter:
Select case True 'determines which delimiter to use based on user selection on GUI
Case delim(0).checked ' Default
headerLine = "þ" & "DOCID" & "þ" & "" & "þ" & "SUBJECT" & "þ" & "" & "þ" & "DOCDATE" & "þ" & "" & "þ" & "DateTimeSent"& _
"þ" & "" & "þ" & "DateTimeCreated" & "þ" & "" & "þ" & "DateTimeLastMod" & "þ" & "" & "þ" & "DateTimeRCVD" & "þ" & "" & "þ" & "DateTimeAccessed" & "þ" & VBCRLF
Case delim(2).checked 'Tab Delim
headerLine = "DOCID" & vbTab & "SUBJECT" & vbTab & "DOCDATE" & vbTab & "DateTimeSent" &_
vbTab & "DateTimeCreated" & vbTab & "DateTimeLastMod" & vbTab & "DateTimeRCVD" & vbTab & "DateTimeAccessed" & VBCRLF
Case delim(1).checked 'Comma Delim
headerLine = chr(34) & "DOCID" & chr(34) & "," & chr(34) & "SUBJECT" & chr(34) & "," & chr(34) & "DOCDATE" & chr(34) & "," & chr(34) & "DateTimeSent" &_
chr(34) & "," & chr(34) & "DateTimeCreated" & chr(34) & "," & chr(34) & "DateTimeLastMod" & chr(34) & "," & chr(34) & "DateTimeRCVD" & chr(34) & "," & chr(34) & "DateTimeAccessed" & chr(34) & VBCRLF
Case delim(3).checked 'Other
headerLine = txtDelim.Value & "DOCID" & txtDelim.Value & "SUBJECT" & txtDelim.Value & "DOCDATE" & txtDelim.Value & "DateTimeSent" &_
txtDelim.Value & "DateTimeCreated" & txtDelim.Value & "DateTimeLastMod" & txtDelim.Value & "DateTimeRCVD" & txtDelim.Value & "DateTimeAccessed" & txtDelim.Value & VBCRLF
end Select
objfile.write headerline
'update status on GUI for user
result.innerHTML = result.InnerHTML & "<br><br>Formatting Date/Time Fields.."
'Go through record set and assign variables and combine date and time fields based on array:
Do until objDateRecSet.EOF
DocID = objDateRecSet(0)
EmailSubject = objdateRecSet(1)
DocTitle = objDateRecSet(2)
DateTimeSent = trim(objDateRecSet(3) & " " & objDateRecSet(4))
DateTimeCreated = trim(objDateRecSet(5)& " " & objDateRecSet(6))
DateTimeLastMod = trim(objDateRecSet(7)& " " & objDateRecSet(8))
DateTimeRcvd = trim(objDateRecSet(9)& " " & objDateRecSet(10))
DateTimeAccessed = trim(objDateRecSet(11)& " " & objDateRecSet(12))
AttachPID = trim(objDateRecSet(13))
AttachLvl = trim(objDateRecSet(14))
'Compare EmailSubject and Title and select the one that is not null:
If (objDateRecSet(1) = "" or isnull(objDateRecSet(1))) Then
Subject = objDateRecSet(2)
Else
Subject = objDateRecSet(1)
END IF
'Find Docdates. Docdates are set based on DateTimeCreated, DateTimeLastMod, DateTimeRcvd, and DateTimeSent. The below if..then statements determine what to set the docdate field to based on heigharchy. The heigharchy slightly varies based on if the document is a parent alone, or if it has childern -- this is determined based on the AttachPID and AttachLvl fields--
If AttachPID = 0 and AttachLvl = 0 Then 'Parent alone
Docdate = objDateRecSet(9)
if (Docdate = "" or isnull(Docdate))Then
Docdate = ObjDateRecSet(3)
if (Docdate = "" or isnull(Docdate))Then
Docdate = objDateRecSet(7)
if (Docdate = "" or isnull(Docdate))Then
Docdate = objDateRecSet(5)
end if
End if
End if
ElseIf AttachPID > 0 And AttachLvl = 0 Then 'Parent w/children
Docdate = trim(ObjDateRecSet(3)) 'SENT
DateTimeParent = trim(ObjDateRecSet(3))
if (Docdate = "" or isnull(Docdate))Then
Docdate = objDateRecSet(9) 'RCVD
DateTimeParent = trim(ObjDateRecSet(9))
if (Docdate = "" or isnull(Docdate))Then
Docdate = objDateRecSet(7) 'MOD
DateTimeParent = trim(ObjDateRecSet(7))
if (Docdate = "" or isnull(Docdate))Then
Docdate = objDateRecSet(5) 'CREATED
DateTimeParent = trim(ObjDateRecSet(5))
End if
End if
End if
ElseIf AttachLvl = 1 then 'Attachment/children only
Docdate = DateTimeParent
End if
objDateRecSet.movenext
'format output based on user selected delimiter
Select case True
Case delim(0).checked ' Default
outputLine = "þ" & Docid & "þ" & "" & "þ" & Subject & "þ" & "" & "þ" & Docdate & "þ" & "" & "þ" & DateTimeSent &_
"þ" & "" & "þ" & DateTimeCreated & "þ" & "" & "þ" & DateTimeLastMod & "þ" & "" & "þ" & DateTimeRCVD & "þ" & "" & "þ" & DateTimeAccessed & "þ"
Case delim(2).checked 'Tab Delim
outputLine = Docid & vbTab & Subject & vbTab & Docdate & vbTab & DateTimeSent &_
vbTab & DateTimeCreated & vbTab & DateTimeLastMod & vbTab & DateTimeRCVD & vbTab & DateTimeAccessed
Case delim(1).checked 'Comma Delim
outputLine = chr(34) & Docid & chr(34) & "," & chr(34) & Subject & chr(34) & "," & chr(34) & Docdate & chr(34) & "," & chr(34) & DateTimeSent &_
chr(34) & "," & chr(34) & DateTimeCreated & chr(34) & "," & chr(34) & DateTimeLastMod & chr(34) & "," & chr(34) & DateTimeRCVD & chr(34) & "," & chr(34) & DateTimeAccessed & chr(34)
Case delim(3).checked 'Other
outputLine = txtDelim.Value & Docid & txtDelim.Value & Subject & txtDelim.Value & Docdate & txtDelim.Value & DateTimeSent &_
txtDelim.Value & DateTimeCreated & txtDelim.Value & DateTimeLastMod & txtDelim.Value & DateTimeRCVD & txtDelim.Value & DateTimeAccessed & txtDelim.Value
end Select
objFile.writeline outputLine
'loop through the recordset and keep appending to overlay file
Loop
objfile.close
'update the user to status complete
result.innerHTML = result.InnerHTML & "<br>Overlay Complete..<br>"
result.innerhtml = result.innerhtml & "OUTPUT: " & Directory
End Sub
'create the user interface based on HTML:
</script>
<body>
<nav class="ink-navigation">
<ul class="menu horizontal black">
<h1><p align="center" style="color:white"><b>Date/Time Overlay Creator</b></p></h1>
</ul>
</nav>
<table>
<tbody>
<tr>
<th><b>Please select database:</b></th>
<td><select id=SQLdb size="1" name="dbChooser" value="DB List">
</select>
</td>
</tr>
<tr>
<th align=right><b>Please enter _BOX#:</b></th>
<td><input type="text" name="txtBoxNum"></td>
</tr>
<tr>
<th align=right>Output Directory:</th>
<td><input type="text" name="txtDir" style="width=100%"></td>
</tr>
</tbody>
</table>
<table width="100%">
<tbody style="font-size=11px;">
<th width="75%"><fieldset><legend>Delimiter</legend>
<input type="radio" name="delim" id="delim" value="default" Checked="true"><Label for="default">Default</label>
<input type="radio" name="delim" id="delim" value="comma"><Label for="comma">Comma</label>
<input type="radio" name="delim" id="delim" value="tab" ><Label for="tob">Tab</label>
<input type="radio" name="delim" id="delim" value="customr"><Label for="customr">Custom</label>
<input type="text" name="txtDelim" size="1" maxlength="1">
</fieldset></th>
<td width="25%" align=right><input type="button" class="ink-button Green" value="Create Overlay" onClick="CreateOverlay"></td>
</tbody>
</table>
<div class="resultdiv" align="left" style="border:1px solid black;height:30%;">
<span id="result" style="font-family:Courier;font-size:12;line-height:.9;">INSTRUCTIONS:<br>Please select a database, enter the "_BOX#" above, select delimiter style then hit "Create". This will output a comma-delimited text file. </span><br>
</div>
</body>The code above has been commented, however it can be improved using functions. Creating a function for the delimiter would reduce the lines of code, by defining it once and using it twice. I am always looking for way I can improve the process by creating scripts or applications that automate some of the tasks a user has to complete. In the end, it saves time therefore more can be accomplished.
0 Comments