Skip to main content

Function age

Hello,
 
I have a problem with the function @age in Ideascript. When I execute the following code, IDEA crash.
Function DatePiece
    Set db = Client.OpenDatabase(dbname)
    Set task = db.TableManagement
    Set field = db.TableDef.NewField
    field.Name = "ECARTDATE"
    field.Description = ""
    field.Type = WI_VIRT_NUM
    Dim a As String
    a = "@age(" & Chr(34) &  exercice &  Chr(34) & ";PIECEDATE)" 
    field.Equation = a
    field.Decimals = 0
    task.AppendField field
    task.PerformTask
    Set task = Nothing
    Set db = Nothing
    Set field = Nothing
End Function
 
 
The variable "exercice" is a string value : 2081231
The field PIECEDATE is a date value (format "YYYYMMDD")
 
The recording macro is :
Set db = Client.OpenDatabase("444274732FEC20160331.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "ECARTDATE"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@age(""20181231"";PIECEDATE)"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
 
Anybody has an idea ?
Thank you !
Benoit

Brian Element Tue, 03/12/2019 - 07:27

Overall it looks good.  I see in your comment you say the exercice variable is 2081231, is this a typo or this might be the problem as this is not a valid date and could cause IDEA to crash in a script.

Brian Element Tue, 03/12/2019 - 09:14

In reply to by BENOIT

That is too bad, that would have explained the problem.  I am not seeing anything obvious that is wrong.  Did you validate that you are using the proper database and the field exists in that database?

BENOIT Tue, 03/12/2019 - 09:20

yes it works with the following code :
 
Set db = Client.OpenDatabase(dbname)
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "ECARTDATE"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@age(""20181231"";PIECEDATE)"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
 
 

Brian Element Tue, 03/12/2019 - 09:41

In reply to by BENOIT

I just tried the following code on one of my files, I changed the date field to PIECEDATE and I didn't have any problems.


	Dim eqn As String
	Dim exercice As String
	Set db = Client.OpenDatabase(dbname)
	Set task = db.TableManagement
	Set field = db.TableDef.NewField
	field.Name = "ECARTDATE"
	field.Description = ""
	field.Type = WI_VIRT_NUM
	exercice = "20181231"
	eqn = "@age(" & Chr(34) & exercice & Chr(34) & "; PIECEDATE)"
	field.Equation = eqn
	field.Decimals = 0
	task.AppendField field
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Set field = Nothing

BENOIT Tue, 03/12/2019 - 09:53

It's weird.
The error's message says "internal error" and it's focus the line task.AppendField field
I will try from a new script.

BENOIT Tue, 03/12/2019 - 12:14

It works from a new script.
My code is probably not well structured.
I tried to reorganise my code and to put quotes before some instructions and functions to see if it works. => sometimes, the function 'DatePiece' works but the title of the new field is not "ECARTDATE" as wanted but an other title from my database (eg "JOURNALCODE1"). And it's not always the same title !?? But other times, IDEA crash.
Could you take a look at my script ?

Brian Element Tue, 03/12/2019 - 12:37

Sounds like the field might already exist and if it does and you try to create it you end up with a 1 added to the end.  If the field is hard coded you might get this problem or if you return it on the same script and the fields have already been created this would happen, that sounds like what is happending.  What I do is create a variable to hold the field name and use this function (http://ideascripting.com/Check-if-field-name-exists) to check if the field exists and if it does to return a field name that doesn't exist, I think use that to create the new field.  So before I create the field I would have a line something like this:

newFieldName = CheckIfFieldExists("NEW_FIELD", "My File.IMD")

Then where you have the new field name you would use the variable newFieldName and everytime there is a reference to that field you would use the variable, this should get around your problem.

That is an impressive script.