Set a field as variant in an equation

giorgia.basso

Forums

Hi,
I am trying to automate some analysis and I don't know how to fix the following code in order to include the field as variant because I have several fields in where I need to adjust the format. Can someone help me to insert the variable field in my equation editor? This is where my code stop working, I have copied it here! Thanks
Function ModifyAllDateField (DateField, WorkingDatabase) As Variant
            Dim db As Object
            Dim task As Object
            Dim field As Object 
           
            Set db = Client.OpenDatabase(WorkingDatabase)
            Set task = db.TableManagement
            Set field = db.TableDef.NewField
           
            'Users DateField variable passed to the function to set which field is checked
            field.Name = DateField
           
            ' Checks if the field is already a date field, if it is, do nothing
            ' If the field is not a date field, then change it to a date field (saves processing time)
            If field.IsDate Then
                        Set Task = Nothing
                        Set db = Nothing
                        Set field = Nothing
            Else
                        field.Name = DateField & "_CORRECT"
                        field.Description = ""
                        field.Type = WI_VIRT_DATE
                        field.Equation = "@ctod(DateField, ""DD/MM/YYYY"")"
                        task.AppendField field
                        task.PerformTask
                        Set task = Nothing
                        Set db = Nothing
                        Set field = Nothing
                       
            End If
           
 
End Function
 

Brian Element Sat, 02/27/2016 - 07:17

Hi Giorgia and welcome to the site.

I made some modification to your function.  I added comments to the parts that I changed.  So these are the following changes I made:

  • I removed the as variant from the function call as the function is not returning anything.
  • I added a new object to hold the information for the newField.  You need two field objects, one to get the old information and the other to hold the new information.
  • Your first Set field I updated to now obtain the information from the DateField variable.
  • I removed the field.Name = DateField as it is not needed, you are not setting the field name in this case.
  • Instead of checking if the field is date I changed it to check if it is character.  That way if the field happens to be numeric or a time field you won't have an error in your code.
  • I added a line to create the NewField object
  • I changed the equation as the DateField variable has to be outside the quotes or else the equation will be looking for "DateField" and not what is contained in the variable.
  • I moved the setting of the objects to nothing outside of the if then statement so they don't need to be repeated.

Hopefully this helps you out.

Thanks

Brian

Function ModifyAllDateField (DateField, WorkingDatabase) ' As Variant not needed as function doesn't return anything
            Dim db As Object
            Dim task As Object
            Dim field As Object 
            Dim newField As Object 'added
            
            Set db = Client.OpenDatabase(WorkingDatabase)
            Set task = db.TableManagement
            Set field = db.TableDef.GetField(DateField) 'updated
            
            'Users DateField variable passed to the function to set which field is checked
            'field.Name = DateField 'not needed
            
            ' Checks if the field is already a date field, if it is, do nothing
            ' If the field is not a date field, then change it to a date field (saves processing time)
            If field.IsCharacter Then 'modified, check if fied is character as you don't want to do anything if it is numeric or time.
            	Set newField = db.TableDef.NewField 'added
                        newField.Name = DateField & "_CORRECT"
                        newField.Description = ""
                        newField.Type = WI_VIRT_DATE
                        newField.Equation = "@ctod(" & DateField & ", ""DD/MM/YYYY "")" 'Modified to move the variable outside the equation
                        task.AppendField newField
                        task.PerformTask
                        Set newField = Nothing 'added
                        
            End If
            
 	Set Task = Nothing 'moved
            Set db = Nothing 'moved
            Set field = Nothing 'moved
End Function

 

giorgia.basso Mon, 02/29/2016 - 11:46

Hi Brian,
thanks a lot for your answer and nice to meet you! :-)
The code works but only if I keep the function variants.
I like how you created the forum.
Giorgia

giorgia.basso Wed, 03/09/2016 - 16:31

here is the name of my function 
Function ModifyAllDateField (DateField, WorkingDatabase) As Variant          
 

Brian Element Wed, 03/09/2016 - 20:50

Hi Giorgia,

What I was looking for is how are you calling the function, do you have:

call ModifyAllDateField(DateField, WorkingDatabase) or do you have a variable equal to it?

Thanks

Brian

giorgia.basso Wed, 03/16/2016 - 08:31

Hi Brian,
here is my option explicit and my sub main.
Let me know if this help.
Thanks,
Giorgia
Option Explicit
 
Dim VMFileName, EmployeeFileName, BillsFileName, BillsDetailsFileName, POFileName, PODetailsFileName, dbNetSuite, dbWorkday As String
Dim Result As Long
 
Sub Main
Client.ManagedProject = "LOC-0001 Procurement"
 
Call FileBrowser() 'Call GetFileNames()
Call VMImport() ' Import the VMF Input file
Call EmployeeInfoImport() ' Import the Employee Input file
Call BillsImport() ' Import the account #20000 transactions - main line 
Call BillsDetailsImport() ' Import the account #20000 transactions - expense line file
Call POImport() ' Import the PO main line file
Call PODetailsImport() ' Import the PO details file
Call PassVMFields()
Call PassBillsFields()
Call PassBillsDetailsFields()
Call PRO_003_01()
Client.CloseAll
 
End Sub

Brian Element Wed, 04/20/2016 - 09:09

Hi Giorgia,

Sorry I missed your reponse.  What I am looking for exactly is the line that is calling the function that wants to be a variant.

Thanks

Brian

oseroke Tue, 06/28/2016 - 16:57

Hello Brian,

Let's say the variable is of type String. And I want to write an equation that checks a string field

Dim code as String

Sub main()
code = "271"
...
...
equation = "BRA_CODE = " & code
...
...
End sub

The problem is that the equation variable reads it as :
"BRA_CODE = 271"

but I want it to read it as

"BRA_CODE = "271""

Please can you help?

Brian Element Wed, 06/29/2016 - 07:32

Hi Oseroke,

You have to include the double quotes (") around the variable so your equation could be:

equation = "BRA_CODE = """ & code & """"

So when you have "" (two double quotes) IDEAScript will insert a single " instead of closing the string.  So in this case the equation editor would see the equation as: equation = "BRA_CODE = "271"

You can also write it like this:

equation = "BRA_CODE = " & chr(34) & code & chr(34)

The chr(34) inserts a double quote.

Another option is to create a variable or constant to hold the double quote:

Const dblQuote = chr(34)

equation = "BRA_CODE = " & dblQuote & code & dblQuote

All of these should work assuming I didn't make a typo.

Brian

gabriel Fri, 03/13/2020 - 12:31

Hello i want to make my equation works using variant:
 
My i get a help what is the correct syntax:
 
Function criterio
 
MsgBox ("TRANSFORMACION CARACTER")
MsgBox (sDate)
 
Set db = Client.OpenDatabase("A_DLPMT.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "MIN"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@If(FECHA_PAGAR<=sDate;@Age(FECHA_PAGAR;sDate);0)"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
 
 
MsgBox ("Fin Criterio")
End Function
 
 
Regards