Append field using a local variable.

VITOPIEPOLI

Forums

Hi,
I have developed this script.
I want it to get the value Materiality and return a new field with YES or NO for additional filtering (i.e. PerfMateriality and Sampling).
The code is stuck on task.AppendField field as Syntax Error.
Could you please help me?
This is the code.
Sub Main
Dim Materiality As Long' Could set to 2 decimal places & do similarly for the following two variables
Dim PerfMateriality As Long
Dim SamplingThreshold As Long
Materiality = InputBox("Type in the Materiality for this client (to the nearest €).")
PerfMateriality=Materiality*0.5
SamplingThreshold=PerfMateriality*0.01
MsgBox "Materiality = €" & Materiality & vbCrLf & "Performance  Materiality = €" & PerfMateriality & vbCrLf  & "SamplingThreshold = €" & SamplingThreshold
 
Call MAT()'TEST-JLines.IMD
End Sub
 
 
' Append Field
Function MAT
Set db = Client.OpenDatabase("TEST-JLines.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "MATERIALITY"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = "@if(ABSOLUTE >= Materiality, ""YES"", ""NO"")"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
Many thanks.
 
Vito

VITOPIEPOLI Wed, 10/30/2019 - 13:26

Hi klmi,
I figured out that the issue is that I set the variable in the sub function. I have corrected it copying in the function itself.
Sub Main  Call MAT() 'TEST-JLines.IMDEnd Sub
' Append FieldFunction MAT                Dim Materiality As Long   ' Could set to 2 decimal places & do similarly for the following two variables Dim PerfMateriality As String Dim SamplingThreshold As String Materiality = InputBox("Type in the Materiality for this client (to the nearest €).") PerfMateriality=Materiality*0.5 SamplingThreshold=PerfMateriality*0.01 MsgBox "Materiality = €" & Materiality & vbCrLf & "Performance  Materiality = €" & PerfMateriality & vbCrLf  & "SamplingThreshold = €" & SamplingThreshold Set db = Client.OpenDatabase("TEST-JLines.IMD") Set task = db.TableManagement Set field = db.TableDef.NewField field.Name = "MATERIALITY" field.Description = "" field.Type = WI_NUM_FIELD field.Equation = "@if(ABSOLUTE >= Materiality, 1,0)" field.Decimals = 0 task.AppendField field task.PerformTask Set task = Nothing Set db = Nothing Set field = NothingEnd Function
However the formula does not work but if I call the variable without formula it works. Where is the error in the formula?
Thanks.
Vito

klmi Wed, 10/30/2019 - 13:58

Again there's the problem that you are mixing an IDEA equation with IDEAScript. Out of your script the variable Materiality isn't known. A field Materiality also doesn't exist because you are trying to append that.
try that: field.Equation = "@if(ABSOLUTE >=" & Materiality &", 1,0)"