Converting Times

Steve

Forums

Hey everyone,
I'm relatively new to IdeaScript and want to transfer Times to other Timezones. I do have all the data I need
 
Example for my Table:
User                  Time              Sub/Add             Hours               Minutes
xy                       15:12:00        +                             6                          00
yz                        13:20:16        -                              2                          30
 
I would like to add 6 hours in the first row and substract 2 hours and 30 minutes in the second row and create a new Field (Local Time) with the new Time.
 
I can't think of a solution. Please help me out :-)

Brian Element Fri, 02/07/2014 - 07:14
Hi Steve, here is some code that will perform what you are looking for. The first function will change your TIME_OF_ENTRY field to a character field (if it is already character then just comment this function out), the next step will add the virtual field and the final step will change the LOCAL_TIME field to a time field. I haven't added any varaibles to the script. I also changed the list separators to ; which I believe represent your regional settings. I have attached the script so you can download and modify to your liking. 
Option Explicit

Sub Main
	Call changeTimeFieldToChar 'this will change the time field to a character field
	Call addVirtualField 'this will add a virtual field call LOCAL_TIME
	Call changeCharFieldToTime ' this will change the LOCAL_TIME field from char to time.
End Sub

Function changeTimeFieldToChar()
	Dim db As database
	Dim task As task
	Dim table As tabel
	Dim field As field
	
	Set db = Client.OpenDatabase("time test-Sheet1.IMD")
	Set task = db.TableManagement
	Set table = db.TableDef
	Set field = table.NewField
	field.Name = "TIME_OF_ENTRY"
	field.Description = ""
	field.Type = WI_CHAR_FIELD
	field.Length = 8
	task.ReplaceField "TIME_OF_ENTRY", field
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Set table = Nothing
	Set field = Nothing

End Function

Function addVirtualField()
	Dim db As database
	Dim task As task
	Dim table As tabel
	Dim field As field
	Dim eqn As String
	
	Set db = Client.OpenDatabase("time test-Sheet1.IMD")
	Set task = db.TableManagement
	Set table = db.TableDef
	Set field = table.NewField
	eqn = "#LocalTime(TIME_OF_ENTRY; SUB_ADD; HOURS; MINUTES)"
	field.Name = "LOCAL_TIME"
	field.Description = ""
	field.Type = WI_CHAR_FIELD
	field.Equation = eqn
	field.Length = 10
	task.AppendField field
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Set table = Nothing
	Set field = Nothing

End Function

Function changeCharFieldToTime()
	Dim db As database
	Dim task As task
	Dim table As tabel
	Dim field As field
	
	Set db = Client.OpenDatabase("time test-Sheet1.IMD")
	Set task = db.TableManagement
	Set table = db.TableDef
	Set field = table.NewField
	field.Name = "LOCAL_TIME"
	field.Description = ""
	field.Type = WI_TIME_FIELD
	field.Equation = "HH:MM:SS"
	
	task.ReplaceField "LOCAL_TIME", field
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Set table = Nothing
	Set field = Nothing

End Function

 

Steve Fri, 02/07/2014 - 08:19

Hi Brian,
thank  you again. Sorry for not replying yesterday. I had so much stuff going on that I haven't found the time.
I tested your Script and it worked. I only edited it a bit to increase the reusability of the code. Here is what I did:
 
<code>
Function ChangeTimeFieldToChar(IDM As String, Fieldname As String)
 On Error GoTo ErrHandler_ChangeTimeFieldToChar
 Dim db As database
 Dim task As task
 Dim table As tabel
 Dim field As field
 Set db = Client.OpenDatabase(IDM)
 Set task = db.TableManagement
 Set table = db.TableDef
 Set field = table.NewField
 field.Name = Fieldname
 field.Description = ""
 field.Type = WI_CHAR_FIELD
 field.Length = 8
 task.ReplaceField Fieldname, field
 task.PerformTask
 
 Client.CloseAll
 Set task = Nothing
 Set db = Nothing
 Set table = Nothing
 Set field = Nothing
 
 Exit Function
ErrHandler_ChangeTimeFieldToChar:
 If Err.Number = 0 Then
  Write #1, Now, " - ERROR - " , "ChangeTimeFieldToChar", Err.Number, Err.Description, "Error Not defined"
 Else
  Write #1, Now, " - ERROR - " , "ChangeTimeFieldToChar", Err.Number, Err.Description
 End If
 errorOccurred = TRUE
 'resume next
End Function
</code>
 
So thanks again for helping me so much:-)
Have a nice weekend!

Brian Element Fri, 02/07/2014 - 08:35

Not a problem Steve, making the code reusable is always a good thing.  You also have a great weekend.