Skip to main content

Expression in Equation parameter of the append field function

Dear all,
I have a problem of passion gthe correct argument wihtin the string associated to my equation expression of append field function. I have created bespoke append field function (below):
' Appending field
Function AddFieldToDatabase(TargetDb As Database,NewField_Name As String,Req_Field_Type As Integer ,Criteria As String)
 
'Set TargetDb = MasterDb  this is not always MasterDb 
 
'Creating the task
Set task = TargetDb.TableManagement
'Obtaining reference to the table
Set TargetField = TargetDb.TableDef.NewField
'Specify the name of the new field
TargetField.Name = NewField_Name
 
On Error GoTo FieldDataTypeError        '(This error will occur if the resulting type of the field created by the code is different than requested one in the function Call (indicated by the number 1-4))
 
    If  Req_Field_Type = 1 Then
 
    'Get the virtual Date field    
    TargetField.Type  = WI_VIRT_DATE
                    TargetField.Length = 8
                                    
    ElseIf     Req_Field_Type = 2 Then
                     'Get the virtual Character field
                     TargetField.Type = WI_VIRT_CHAR
                     TargetField.Length = 20
 
    ElseIf     Req_Field_Type = 3 Then
      'Get the virtual Numeric field
      TargetField.Type = WI_VIRT_NUM
      TargetField.Length = 10
      TargetField.Decimals = 0
     
    ElseIf     Req_Field_Type = 4 Then
                      'Get the virtual Time field
                      TargetField.Type = WI_VIRT_TIME
                      TargetField.Length = 8     
 
     End If         
                      
                               'Set the criteria (equation) and perform the task
TargetField.Equation = Criteria
task.AppendField TargetField
task.PerformTask
       
                      GoTo CleanFunctionSection
             
FieldDataTypeError: 
 
MsgBox "Field Type for the new field (column) generated by the equation is different than requested within AddFieldToDatabase function." 
Exit Sub
     
CleanFunctionSection:
 
Set task = Nothing
Set TargetField = Nothing
Set TargetDb = Nothing
 
End Function
 
At some point in the programme I call my append field function from the below function:
 
Function Append_Field_Sales_JE_Tag(MasterDb As Object,Account As String)
                   
                   Dim DatabaseName As String
                   Dim DatabaseField As String
                   Dim FieldInput As String
                    
                    DatabaseName = Account & " journals.IMD"
                    DatabaseField = Account & "_JE_TAG"
                    FieldInput = Account  & " JE"
                    
                    MsgBox DatabaseName
                    MsgBox DatabaseField
                    MsgBox FieldInput
                    
                               
    Set TargetDb = Client.OpenDatabase(Account & " journals.IMD")      
    Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput)     
      
End Function
However i experience a huge problem  to pass correctly FieldInput element. Account string within
FieldInput is essentially string from one of the field in the database and is declared in earlier stage of my programme.The result of FieldInput is string 'Revenue JE".
 I need to highlight that the message in Msgbox associated to FieldInput variable is displaying correct string of "Revenue JE".
The only way the line:
Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput) 
works is when I replace the FieldInput with the hard coded """Revenue JE""" however thats not the point as my Account variable will change thoughout the programme and will get different value on the second run (execution) of the programme.
 

Brian Element Fri, 08/24/2018 - 14:02

So is the Account variable blank or not holding what you expect?  I don't have enough of the code to see what the problem is, you are defining Account somewhere so you need to trace it back to see where it is changed.

Pawelski_33 Tue, 08/28/2018 - 04:34

Morning Brian,
No, the Account variable is correct and the FieldInput is correct as well. 
FieldInput = Account  & " JE" displays the correct string.
The problem starts when I input that variable into my call function:
Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput)
Then it is not recognized and throws an error. My generic functiion for the append field function is given in my first post on this topic.
 
 

Brian Element Tue, 08/28/2018 - 07:09

In reply to by Pawelski_33

And a good morning to you,

Instead of the Account & "_JE_TAG" did you try putting the variable DatabaseField in instead as it looks like it holds the same information.  It might not like you concatenation you are doing within the function call.

Brian

Pawelski_33 Tue, 08/28/2018 - 08:01

DatabaseField  variable is not causing any issue.It working in my call function for either expression: Account & " _JE_TAG" or DatabaseField.
The issue is caused by the FieldInput variable which is essentially Criteria element in my function which holds the actual equation for the append field.

Brian Element Tue, 08/28/2018 - 10:11

In reply to by Pawelski_33

I just noticed something, your call is the following:

Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput) 

You are sending 3 variables to the AddFieldToDatabase_Character but your AddFieldToDataase function has four parameters and the thrid is an integer but in your call you are sending a string.  I know the function name is different but do you want to make sure that your call has the same number of parameters and same time as the function it is calling.

AddFieldToDatabase(TargetDb As Database,NewField_Name As String,Req_Field_Type As Integer ,Criteria As String)

Pawelski_33 Tue, 08/28/2018 - 11:20

Yes , i have modified my original bespoke functiion to create only 1 type of field - character one. Hence there is a call to Function AddFieldToDatabase_Character (which is given below).
Function AddFieldToDatabase_Character(TargetDb As Database,NewField_Name As String,Criteria As String)
 
'Set TargetDb = MasterDb  this is not always MasterDb 
 
'Creating the task
Set task = TargetDb.TableManagement
'Obtaining reference to the table
Set TargetField = TargetDb.TableDef.NewField
'Specify the name of the new field
TargetField.Name = NewField_Name
 
                 'Get the virtual Character field
                TargetField.Type = WI_VIRT_CHAR
                TargetField.Length = 20
                    
                'Set the criteria (equation) and perform the task
TargetField.Equation = Criteria
task.AppendField TargetField
task.PerformTask
       
       
 
Set task = Nothing
Set TargetField = Nothing
Set TargetDb = Nothing
 
End Function
 
That function has only 3 arguments as it is supposed to create only virtual character field type and therefore  doesnt need the type argument specified by integer 1 to 4. 
Therefore as you can see it doesnt include Req_Field_Type As Integer argument.
Apologies for confusion but having said that the issue is still around the last argument in the function which is Criteria associated to the field.Equation line.

Pawelski_33 Wed, 08/29/2018 - 11:10

The error message is not very helpful as it only says:"Syntax error" and provides the line number of the code where the error occurs.That line indicates the problem is around task.AppendField TargetField exection (performing task) of my generic function.
I think the issue is around passing the argument from the dialog box into the function argument and then passing that argument into my generic append field function.
 

Brian Element Wed, 08/29/2018 - 12:10

In reply to by Pawelski_33

Ok, I think I have it.  The problem is not with your IDEAScript but with your criteria.  If you get the syntax error it means that your IDEA equation is not proper and won't run.  So I would suggest just before getting to that line and using a message box to see what your criteria is and try it directly in the equation editor to figure out what is wrong with it.