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 :-)
Steve
Offline
Last seen: 10 years 1 month ago
Joined: 02/04/2014 - 11:25
Hi Steve and welcome to the site.
If you have version 9 you can use the following equation in the equation editor:
@Ntot(@if(SUB_ADD = "+", @Tton(TIME) + HOURS * 60*60 + MINUTES*60, @Tton(TIME) - HOURS * 60*60 - MINUTES *60))
This won't work in V8 as the @NtoT and the @TtoN function did not exist. Let me know if this solves your problem, if not I can probably do something with a custom function to help you out.
Also what do you expect if you have a time like 02:00:00 - 4 hours? Do you expect 0, a minus or does the clock loop around and give you 22:00:00? What about 22:00:00 + 4 hours do you expect 26:00:00 or 02:00:00?
In the equation above it gives an error if it is 0 and if it goes over 24 hours it just keeps increasing.
So let me know and I will try and help you out.
Hi Steve, I read your message a bit closer and realized what you wanted. As there is a bit of logic there I decided to do it as a custom function, it could probably be done as an equation but the equation editor isn't the best when you have lots of logic to check, fortunately we have options to create a custom function. So below is the code and I have attached the custom function for you to try. Unfortunately custom functions don't handle time fields so the input and the output have to be a character field. The function takes four variables, namely the time, a + / -, the number of hours and the number of seconds. It doesn't do any checking to see if you put something in like increasing by 100 hours, in which case you will get a strange answer, the range I expect for hours is 1 to 23 and 1 to 59 for minutes. So try it out and let me know if this is what you are looking for. It also should work for both V9 and V8.
I have updated the function so that if you do not put in a + or - it will default to a + and if the hours or minutes are greater than 60 it defaults to 60 and less than 0 it defaults to 0.
Thank you Brian. I do have v8.
As I said I'm relatively new to IDEAScript. How do I use the function to append a new field to the original database?
Thanks
Hi Steve, well what I did is not technically IDEAScript, it is something you can do through the equation editor which based on what you were asking for I think works best.
So here are the steps to use the custom function:
Let me know if this makes sense or if you have any problems with it.
Thank you Brian! It works great and your description was easy to follow :-)
Thanks again and have a nice day:-)
Hi Steve, great to hear it worked. Let me know if there is anything else I can help out with.
Hi Brian,
I'm trying to automize the execution with an IDEA Script. I don't have a problem with using the custom function, but prior to that I have to convert the Time Field I have to a Character Field. If I do that manually with the Field Manipulation Dialog everything works fine, but if I do it with IDEAScript I randomly get either an empty field or fields with an Error in it, but never the correct String (e.g. "08:00:00").
Here is my Code (I commented most of it so that nothing else could lead to that behaviour):
<code>
Dim ThisTable As Table
Dim field As Field
Set db = Client.OpenDatabase(dbname)
Set task = db.TableManagement
Set ThisTable = db.TableDef
Set field = ThisTable.GetField("TIME_OF_ENTRY")
field.Type = WI_VIRT_CHAR
field.Length = 10
db.CommitDatabase
'Set field = ThisTable.NewField
'field.Name = "LocalTime"
'field.Description = ""
'field.Type = WI_CHAR_FIELD
'field.Equation = "#LocalTime(#LocalTime( TIME_OF_ENTRY ; ""-"" ;1;0); SUB_ADD ; HOURS ; MINUTES )"
'field.Length = 8
'task.AppendField field
'task.PerformTask
Client.CloseAll
Set field = Nothing
Set ThisTable = Nothing
Set task = Nothing
Set db = Nothing
Exit Function
</code>
The table is imported automatically with the use of a .rdm-File. I could change it so that the field would be imported as a Character Field, but I would rather not do that, because I may need it to be a Time field, when I implement new functions in the future
Steve
Hi Steve, the problem is, at least as far as I know and I can't say I have tested it much, is that you can't access custom functions through IDEAScript as the custom functions sit outside of IDEA.
As you want to do this by script I can put something together for you. Now are your fields hard coded or do they change based on the file? I am just wondering if you need a dialog to select the fields or are the fields always the same name so that you can skip this step? Also for the hours and the minutes and + or - is this alwasy the same for the entire file or different depending on the record?
If I have time I will put something together for you tonight, if not it will go to the weekend.
Hi Brian, the custom function is not the problem, I can call it from IdeaScript without problems.
The thing is, that I have this Field "TIME_OF_ENTRY" which has the type "Time". But for the custom function I need it to be a Character field. As you've said in one of the previous posts custom functions will only work with character fields.
So what I'm doing manually is:
Open the database,
click on data,
click on field manipulation,
select the Field TIME_OF_ENTRY
and change the type from "Time" to "Character*".
This will work fine.
However if I try to do the same thing in IdeaScript(with the code of the previous post) it results in empty values or Error values in the "TIME_OF_ENTRY" field.
I know that this is not a problem of calling the custom function from IdeaScript, because I tried it with manual converting the Type to character and then calling the custom function and it worked fine.
Many thanks in advance:-)
Steve you will have to do the script in two steps. 1 is what you are doing and the second step I have the code below. This will change the character field into a time field for you. Just change TEST for your field name which I believe would be "TIME_OF_ENTRY", also in your code don't create a virtual character field but a character field instead, so in your code change WI_VIRT_CHAR to WI_CHAR_FIELD and it should work. Unfortunately I haven't had time to test this out so let me know if it doesn't work. Thanks
Pages